MediumFinance10 min

Clean ledger memos

Challenge

Show every ledger memo with spaces removed and blanks labeled.

Business context

The journal export has a padded memo and one voided line whose memo was never typed. Reviewers need a visible placeholder instead of an empty cell.

Instructions

Return id, memo, and amount_cents for every ledger row. Trim the memo. When the trimmed memo is null or empty, return (blank). 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: Campaigns with at least two leads