Katkat
Back

Calculate Week-over-Week Revenue Change Using SQL

ShopifyData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question evaluates proficiency in SQL time-series aggregation, weekly grouping, and prior-period comparison for calculating percentage changes in revenue.

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, ali​as columns with non-reserved words, handle NULL or zero prior-week revenue.