Monthly KPI Trends with YoY | Dataford Interview Questions

Write a SQL query to calculate monthly KPI trends from a sales or operations table, including year-over-year comparison.

Assume the KPI output is based on monthly revenue, transaction count, and average transaction value from sales_operations. Include every month present in the table and compare each month with the same month in the prior year.

Output

One row per calendar month, ordered chronologically.
Columns: month_start, total_revenue, transaction_count, average_transaction_value, prior_year_revenue, and revenue_yoy_percent.
Use NULL where no prior-year comparison exists.

Schema

sales_operations

Column	Type	Description
sale_idPK	INT	Unique identifier for the sale
sale_date	DATE	Date on which the sale occurred
revenue	DECIMAL(12,2)	Revenue generated by the sale
sales_channel	VARCHAR(30)	Channel through which the sale was made

Your question is Monthly KPI Trends with YoY. 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

Write a SQL query to calculate monthly KPI trends from a sales or operations table, including year-over-year comparison.

Output

One row per calendar month, ordered chronologically.
Columns: month_start, total_revenue, transaction_count, average_transaction_value, prior_year_revenue, and revenue_yoy_percent.
Use NULL where no prior-year comparison exists.

Schema

sales_operations

Column	Type	Description
sale_idPK	INT	Unique identifier for the sale
sale_date	DATE	Date on which the sale occurred
revenue	DECIMAL(12,2)	Revenue generated by the sale
sales_channel	VARCHAR(30)	Channel through which the sale was made

Your question is Monthly KPI Trends with YoY. 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.