Date Operations & Time-Based Analytics SQL practice problemHardVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Quarter-over-Quarter (QoQ) Sales Growth

Aggregate 2024 revenue by calendar quarter and calculate QoQ growth from the preceding quarter.

  • Window functions
  • Subqueries
  • Aggregation
  • Date analysis
  • Numeric functions

Interview brief

Understand the request

Finance strategy analyst Quarterly planning needs sequential 2024 revenue growth by calendar quarter.

Quarterly planning needs sequential 2024 revenue growth by calendar quarter. Aggregate 2024 revenue by calendar quarter and calculate QoQ growth from the preceding quarter.

Constraints

  • Aggregate before applying LAG.
  • Guard division by a zero prior-quarter value.
  • Order by year and quarter.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

orders

  • order_idINTEGER
  • order_dateDATE
  • order_totalDECIMAL

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Calendar quarter from month: (month + 2) / 3 → Jan-Mar=1, Apr-Jun=2, etc.

Hint 2

Aggregate revenue per quarter, then LAG to fetch the prior quarter.

Hint 3

qoq_growth_pct = (revenue − prev) / prev × 100; first quarter is NULL.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH quarterly AS (SELECT CAST(strftime('%Y', order_date) AS INTEGER) AS year, (CAST(strftime('%m', order_date) AS INTEGER) + 2) / 3 AS quarter, SUM(order_total) AS revenue FROM orders WHERE strftime('%Y', order_date) = '2024' GROUP BY year, quarter) SELECT year, quarter, revenue, LAG(revenue) OVER (ORDER BY year, quarter) AS prev_quarter_revenue, ROUND((revenue - LAG(revenue) OVER (ORDER BY year, quarter)) * 100.0 / NULLIF(LAG(revenue) OVER (ORDER BY year, quarter), 0), 2) AS qoq_growth_pct FROM quarterly ORDER BY year, quarter;

Why this works

QoQ growth is YoY's quarterly cousin. Deriving the quarter via integer math ((month+2)/3) is portable, but most engines also offer a built-in: Postgres EXTRACT(QUARTER), MySQL QUARTER(), SQL Server DATEPART(QUARTER). Pair it with LAG over (year, quarter) for the period-over-period delta.

Success check

All four quarters appear once, with NULL growth only for the first quarter.

Expected result

Use this output to verify values, aliases, ordering, and row count.

yearquarterrevenueprev_quarter_revenueqoq_growth_pct
202411600NULLNULL
202421840160015
2024310501840-42.93
202449601050-8.57

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: