Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Google
Calculate total revenue by month to track sales trends and identify seasonal patterns.
Google's finance team is assembling the quarterly board deck and needs a clean month-by-month revenue trend that can survive executive questions: for each calendar month, total revenue, transaction count, and average transaction value, plus min/max tickets for context.
The goals are twofold: spot seasonal patterns (does revenue climb into March? is there a Q4-style spike forming?) and flag anomalies before the numbers go in front of leadership — a month whose revenue moved opposite to its transaction count deserves a footnote, not a surprise question from the board.
With only a handful of transactions per month, discipline matters more than technique here: report observed facts, separate volume effects (fewer deals) from value effects (smaller deals), and refuse to narrate trends the sample can't support.
Transactions with transaction_id, transaction_amount, transaction_date.
CREATE TABLE transactions (
transaction_id INTEGER PRIMARY KEY,
transaction_amount DECIMAL(10,2),
transaction_date DATE
);Your target
Calculate total revenue by month to track sales trends and identify seasonal patterns.
Loading the expected output…
SELECT
STRFTIME('%Y-%m', transaction_date) as month,
COUNT(transaction_id) as transaction_count,
ROUND(SUM(transaction_amount), 2) as total_revenue,
ROUND(AVG(transaction_amount), 2) as average_transaction_value,
MIN(transaction_amount) as min_transaction,
MAX(transaction_amount) as max_transaction
FROM transactions
GROUP BY STRFTIME('%Y-%m', transaction_date)
ORDER BY month ASC;Step-by-step Solution:
Use STRFTIME('%Y-%m', transaction_date) to extract year-month from the transaction_date. This groups dates into months (e.g., 2024-01, 2024-02).
COUNT(transaction_id) counts the number of transactions per month.
SUM(transaction_amount) calculates total revenue for each month.
AVG(transaction_amount) shows average transaction value per month.
MIN(transaction_amount) and MAX(transaction_amount) show the smallest and largest transaction each month.
GROUP BY STRFTIME('%Y-%m', transaction_date) aggregates all transactions by month.
ORDER BY month ASC to show months in chronological order. This reveals revenue trends, seasonality, and helps forecast future revenue.
Computing the reference output…
Revenue grew 50% from Jan (Rs.1.5L) to Feb–Mar (Rs.2.25L) and April's dip to Rs.1.75L is a volume shortfall (only 2 transactions), not a pricing problem — average ticket size rose every single month, from Rs.50K to Rs.87.5K. Investigate April pipeline coverage, not discounting.
What the interviewer asks next
“April dips to Rs.1.75L on just 2 transactions — anomaly worth escalating, or noise?”
Strong answer covers: Volume-vs-value decomposition: average ticket rose every month (Rs.50K to Rs.87.5K), so the dip is pipeline coverage, not pricing — escalate as a pipeline question with that evidence attached.
“You have 12 transactions total and the board deck goes out tomorrow — what do you refuse to claim?”
Strong answer covers: Small-sample discipline: no seasonality calls, no trendlines from four points; present the months as observed facts and ask for YoY and pipeline data before narrating a story.
“If finance asks whether growth is sustainable, which single cut of this data answers best?”
Strong answer covers: Decomposing growth into ticket-size vs transaction-count shows the engine (bigger deals, fewer of them) — sustainability then hinges on whether ticket growth repeats.
“The min–max spread tightens every month (Jan Rs.10K range → Apr Rs.5K) while averages climb — what structural shift does that imply?”
Strong answer covers: Reading dispersion, not just means: converging tickets at rising values suggests a shift toward enterprise-grade deals — a strategic claim worth verifying with sales before the board hears it.
“February has the most transactions (4) yet March matches its revenue on 3 — which month was more efficient, and why does that matter?”
Strong answer covers: Efficiency lens: revenue per transaction separates sales productivity from headcount luck — March's crew did more with less, and that story belongs in the deck.
“There is no cost column anywhere — revenue is not profit. What do you demand from finance before presenting growth as success?”
Strong answer covers: Refusing vanity metrics: rising revenue on exploding acquisition cost is failure in a costume — ask for margins before letting the board celebrate.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.