Calculate Week-over-Week Revenue Change Using SQL
Company: Shopify
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency in SQL time-series aggregation, weekly grouping, and prior-period comparison for calculating percentage changes in revenue.
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.
Tables
transactions(id INTEGER, user_id INTEGER, revenue DECIMAL(10,2), order_date DATE)
Hints
- Use DATE_TRUNC('week', order_date) to bucket rows into calendar weeks.
- Aggregate weekly revenue with SUM and then use LAG to compare to the prior week.