Top 5 Categories by Total Count | Dataford Interview Questions

University of Kansas Medical Center reporting needs a summary of transaction volume by category. Write a PostgreSQL query that identifies the five categories with the most completed transactions during calendar year 2024.

Requirements

Include every category in the aggregation, including categories with no qualifying transactions.
Count only transactions with status = 'Completed' and a transaction_date in 2024.
Return the category name and transaction count, ordered from highest to lowest count, with category name ascending as the tie-breaker.
Return only the top five categories.

Schema

transaction_categories

Column	Type	Description
category_idPK	INT	Primary key for the category
category_name	VARCHAR(100)	KUMC transaction category name
is_active	BOOLEAN	Whether the category is currently active

transactions

Column	Type	Description
transaction_idPK	INT	Primary key for the transaction
category_id	INT	Assigned transaction category
status	VARCHAR(20)	Transaction processing status
transaction_date	DATE	Date the transaction was recorded
amount	DECIMAL(12,2)	Illustrative transaction amount

Your question is Top 5 Categories by Total Count. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem