SQL Month-Over-Month Revenue and Gross Profit | Dataford Interview Questions

Your question is SQL Month-Over-Month Revenue and Gross Profit. Start with the requirements and the two tables on the right.

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

Problem

Advance Business Capital's Operations team needs a monthly store performance view. Write a PostgreSQL query that calculates revenue, gross profit, and month-over-month changes for each store using qualifying transactions from January through March 2024.

Requirements

Aggregate transactions by store and calendar month.
Calculate gross profit as revenue minus cost. Treat a NULL cost as zero.
Use a window function to compare each store's month with its previous available month. Return NULL changes for the first month.
Exclude transactions outside the reporting period and transactions that do not match a known store. Sort by store ID and month.

Representative Data

store_transactions includes multiple transactions per store-month, a zero-revenue month, a NULL cost, an unmatched store, and a transaction outside the reporting period.

Schema

stores

Column	Type	Description
store_idPK	INTEGER	Unique store identifier
store_name	VARCHAR(100)	Store display name

store_transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
store_id	INTEGER	Referenced store identifier
transaction_date	DATE	Date the transaction occurred
revenue	NUMERIC(12,2)	Revenue generated by the transaction
cost	NUMERIC(12,2)	Transaction cost