EasyFinance10 min

Bills with vendor names

Challenge

Attach each bill to a cleaned vendor name.

Business context

Accounts payable is sending a bill register to an auditor. The register needs the vendor's name, not only the vendor id, and the name should not carry stray spaces.

Instructions

Return bill_id, vendor_name, amount_cents, and status. vendor_name is TRIM of the vendor name. Include every bill. Sort by bill_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: Claims with patient names