MediumFinance12 min

Open payables by vendor

Challenge

Sum the open bills for each vendor.

Business context

Treasury wants the cash still owed. Paid and void bills are not open payables.

Instructions

Return vendor_name and open_cents. vendor_name is the trimmed vendor name. Sum amount_cents for bills whose status is open. Sort by open_cents descending, then vendor name.

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: Collections by department