Date Operations & Time-Based Analytics SQL Topic exerciseHardVerified answerSQLite live

Compare Consecutive Active Weeks

Aggregate 2024 revenue by populated week and compare each active week with the preceding active week.

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

Exercise brief

Understand the request

Growth analytics manager A sparse sales series needs period-over-period comparison across weeks that contain orders.

A sparse sales series needs period-over-period comparison across weeks that contain orders. Aggregate 2024 revenue by populated week and compare each active week with the preceding active week.

Return

  • Return year, week_num, weekly_revenue, prev_week_revenue, wow_growth_percentage in this exact left-to-right order.

Constraints

  • Do not imply that missing calendar weeks were zero-filled.
  • Use LAG over chronological year and week order.
  • Return NULL growth for the first active week.

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

Bucket orders into week numbers, then use LAG to fetch the previous week’s revenue.

Hint 2

Growth% = (this − prev) / prev × 100; multiply by 1.0 to force float division.

Hint 3

The first week has no previous week, so its growth is NULL.

Verified SQL answer

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

Reveal solution and explanation
WITH weekly_sales AS (SELECT CAST(strftime('%Y', order_date) AS INTEGER) AS year, CAST(strftime('%W', order_date) AS INTEGER) AS week_num, SUM(order_total) AS weekly_revenue FROM orders WHERE strftime('%Y', order_date) = '2024' GROUP BY strftime('%Y', order_date), strftime('%W', order_date)) SELECT year, week_num, weekly_revenue, LAG(weekly_revenue) OVER (ORDER BY year, week_num) AS prev_week_revenue, ROUND(((weekly_revenue - LAG(weekly_revenue) OVER (ORDER BY year, week_num)) * 1.0 / LAG(weekly_revenue) OVER (ORDER BY year, week_num)) * 100, 2) AS wow_growth_percentage FROM weekly_sales ORDER BY year, week_num;

Why this works

WoW growth pairs week bucketing with LAG. The portability landmine is WEEK NUMBERING: SQLite '%W' uses Monday-based weeks; Postgres EXTRACT(WEEK) is ISO-8601; MySQL WEEK() has 8 modes; SQL Server has DATEPART(WEEK) (locale-dependent) and DATEPART(ISO_WEEK). Always pin the exact week definition when reporting across engines.

Success check

Every populated 2024 week appears once with the correct preceding active-week revenue and guarded growth.

Expected result

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

yearweek_numweekly_revenueprev_week_revenuewow_growth_percentage
20243430NULLNULL
20246320430-25.58
20248150320-53.13
202410420150180
202411280420-33.33
202414190280-32.14
20241535019084.21
202418220350-37.14
20242138022072.73
202422290380-23.68

Previewing 10 of 17 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.