Top 3 Revenue Months

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

OnDeck

Problem

Finance is drawing up a short leaderboard of the strongest sales months. Find the three highest-grossing months by total transaction amount, biggest earner first, with each month shown as YYYY-MM.

The three best months on record.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

What this really is

This is a calendar rollup dressed up as a finance leaderboard. transaction_date is stored per day, so the month you are asked to rank is not a column: you derive it, sum total_amount across it, then cut to three. Anyone can write the descending sort and the cap. What separates candidates is building the month key first. Group on the raw date and you rank single days: a month's gross stays split across its day-rows, one big day can beat a strong month, and the output shows full dates instead of YYYY-MM.

The second decision is the year. transactions spans 2022 through 2026, so a key built from the month number alone pours every January into one bucket and inflates it with four years of sales.

Build it

Step 1: Derive a year-month key

strftime('%Y-%m', transaction_date) keeps February 2025 and February 2026 apart, and it is already in the YYYY-MM shape the leaderboard shows, so one expression is both the bucket and the label.

Step 2: Sum the revenue per month

SUM(total_amount) per bucket. The table holds a refund stored as a negative amount and a voided sale at zero; both stay in, so each month's total is what was actually booked. Do not reach for quantity: total_amount is already the line revenue.

Step 3: Sort and cut

ORDER BY total_sales DESC LIMIT 3. The cut is clean on this data because the third and fourth months do not tie. If the interviewer wants ties at third kept, swap the cap for DENSE_RANK() and keep rnk <= 3.

Step 4: Name the output

Alias the key year_month and the sum total_sales. The alias also lets GROUP BY year_month reuse the expression instead of repeating it.

The solution

SELECT
  strftime('%Y-%m', transaction_date) AS year_month,
  SUM(total_amount) AS total_sales
FROM transactions
GROUP BY year_month
ORDER BY total_sales DESC
LIMIT 3;

Group on the raw date

GROUP BY transaction_date makes one row per day. Its top three are the three biggest single days, labeled with full dates, and no month's revenue is ever added up.

Group on the month key

GROUP BY year_month collapses every day of a month into one row, so SUM(total_amount) is the month's gross and the cap picks the three strongest months.

Common follow-up questions

Common follow-up questions

• How would you return ties so a four-way tie at third surfaces all four months? (Probes whether you reach for `DENSE_RANK()` over `LIMIT` and know the difference.)
• Now return the top three months per year. (Tests a ranking scoped per year: `DENSE_RANK() OVER (PARTITION BY year ORDER BY total_sales DESC)`.)
• Should refunds count in the month of the refund or the month of the original sale? (Checks whether you notice the negative `total_amount` and can argue the accounting choice.)
• How would you make this incremental so it updates daily without rescanning 120M rows? (Opens the door to a `monthly_revenue` rollup refreshed by a daily job keyed on `transaction_date`.)