EasyFinance12 min

Bill aging labels

Challenge

Label every bill as settled, void, past due, or current as of 15 March 2025.

Business context

The payables aging report uses a fixed as-of date so the close package does not change when someone reruns it next week.

Instructions

Return id, amount_cents, and aging_label. As of 2025-03-15, paid bills are Settled, void bills are Void, open bills with due_on before that date are Past due, and any remaining bill is Current. Sort by id.

Schema

accounts

id INTEGER · primary key
name TEXT
account_type TEXT · asset, liability, revenue, expense

vendors

id INTEGER · primary key
name TEXT · may include extra spaces
category TEXT · nullable

bills

id INTEGER · primary key
vendor_id INTEGER
amount_cents INTEGER
issued_on TEXT
due_on TEXT
paid_on TEXT · nullable
status TEXT · paid, open, void

ledger

id INTEGER · primary key
account_id INTEGER
txn_date TEXT
amount_cents INTEGER
direction TEXT · debit or credit
memo TEXT · nullable, may include extra spaces
status TEXT · mixed case: posted, Posted, POSTED, void

Sample rows

accounts
id	name	account_type
1	Cash	asset
2	Accounts Payable	liability
3	Sales Revenue	revenue

vendors
id	name	category
1	Harbor Office	Facilities
2	Northwind Cloud	Software
3	City Clinic Supplies	Medical

bills
id	vendor_id	amount_cents	issued_on	due_on	paid_on	status
1	1	180000	2025-01-02	2025-01-31	2025-01-20	paid
2	2	42000	2025-01-05	2025-02-04	2025-02-10	paid
3	2	42000	2025-02-05	2025-03-07	NULL	open

ledger
id	account_id	txn_date	amount_cents	direction	memo	status
1	4	2025-01-20	180000	debit	January rent	posted
2	5	2025-02-10	42000	debit	Cloud january	Posted
3	6	2025-01-28	250000	debit	Payroll	POSTED

Draft saves automatically

Ctrl or Cmd + Enter runs the query. The database resets on every run.

Run the query to see rows from the sample database.

Next exercise: Encounters still open