Ledger Balance as of Date

Build a view named result containing every account's balance as of 2025-04-30, using:

• accounts(id INT PRIMARY KEY, name TEXT NOT NULL)
• ledger_entries(entry_id INT PRIMARY KEY, account_id INT NOT NULL, entry_ts DATE NOT NULL, amount_cents INT NOT NULL, status TEXT NOT NULL)

Each entry belongs to an existing account. Amounts are signed integer cents.

Contract

Return account_id, balance_cents, ordered by account_id.

• Count only status = 'posted' entries with entry_ts <= DATE '2025-04-30'.
• Sum signed amounts directly. Separate entries with equal amounts both count.
• Include every account, including accounts with no entries, only ineligible entries, or a negative balance. With no eligible amounts, return zero.
• entry_ts is a date, not a timestamp. This task does not model status history.

Example

Account 1 has posted +500 and -200 on April 30, pending +900 on April 29, and posted +700 on May 1. Account 2 has only a pending entry; account 3 has no entries. Expected balances: (1,300), (2,0), (3,0).

Hints, in increasing detail

1. Which table contains the complete population that must appear?
2. Preserve that population with a left join.
3. Eligibility may be expressed inside ON or a conditional aggregate. A later WHERE on the joined entry can remove accounts you must preserve.
4. Convert a null aggregate to zero; that conversion cannot restore a missing group.

Verify all three example accounts before submitting. Do not use distinct amounts as a substitute for counting each ledger entry exactly once.