Advanced SQL Functions and Windowing | Dataford Interview Questions

Solve an SQL query, then rewrite the same query using advanced SQL functions (window functions were expected). Asked in the Technical HR stage. Candidate's simple-function solution was accepted as a first step; interviewer wanted window functions.

Output

Return one row per category for transactions dated in January 2025.
Include category, total_amount, and amount_rank.
Rank categories by total amount descending, with tied categories sharing a rank.
Sort by amount_rank, then category ascending, with NULL categories last.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
category	VARCHAR(50)	Transaction category
transaction_date	DATE	Date of the transaction
amount	DECIMAL(12,2)	Transaction amount

Your question is Advanced SQL Functions and Windowing. Start with the requirements and the one table 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

Output

Return one row per category for transactions dated in January 2025.
Include category, total_amount, and amount_rank.
Rank categories by total amount descending, with tied categories sharing a rank.
Sort by amount_rank, then category ascending, with NULL categories last.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
category	VARCHAR(50)	Transaction category
transaction_date	DATE	Date of the transaction
amount	DECIMAL(12,2)	Transaction amount

Your question is Advanced SQL Functions and Windowing. Start with the requirements and the one table 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.