Calculate Week-over-Week Revenue Change Using SQL

transactions

+----+---------+---------+------------+
| id | user_id | revenue | order_date |
+----+---------+---------+------------+
| 1  | 101     | 100     | 2023-01-02 |
| 2  | 102     | 200     | 2023-01-05 |
| 3  | 103     | 150     | 2023-01-09 |
| 4  | 101     | 120     | 2023-01-10 |
| 5  | 104     | 180     | 2023-01-16 |
+----+---------+---------+------------+

Scenario

Technical phone screen (CodePad) using sample data; candidate must compute week-over-week revenue change in SQL.

Question

Write a SQL query that returns each calendar week and the percentage change in total revenue versus the previous week.

Hints

Apply DATE_TRUNC to define weeks, alias columns with non-reserved words, handle NULL or zero prior-week revenue.

Week-over-week revenue change

Given a transactions table of individual orders, write a SQL query that returns each calendar week (weeks starting on Monday) and the percentage change in total revenue versus the previous week. If there is no prior week or the prior week's revenue is zero, return NULL for the percentage change.

Table transactions

column	type	constraints
id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
revenue	DECIMAL(10,2)	NOT NULL
order_date	DATE	NOT NULL

Sample data:

id	order_date	revenue	user_id
1	2023-01-02	100	101
2	2023-01-05	200	102
3	2023-01-09	150	103
4	2023-01-10	120	101
5	2023-01-16	180	104

Expected output

One row per calendar week (weeks start on Monday via DATE_TRUNC('week')), with the percentage change in total weekly revenue compared to the previous week. If there is no prior week or the prior week's revenue is zero, return NULL for the percentage change.

pct_change	week_start
NULL	2023-01-02
-10	2023-01-09
-33.33	2023-01-16

Hints

1. Use DATE_TRUNC('week', order_date) to bucket rows into calendar weeks.
2. Aggregate weekly revenue with SUM and then use LAG to compare to the prior week.
3. Alias output columns with simple, non-reserved names like week_start and pct_change.
4. Guard against NULL or zero prior-week revenue using NULLIF or CASE to avoid divide-by-zero.