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.
Quick Answer: This question evaluates proficiency in SQL time-series aggregation, weekly grouping, and prior-period comparison for calculating percentage changes in revenue.