← Shopify Interview Insights

Shopify·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2026Remote

Summary

Shopify data scientist phone screen, done in a CodePad environment with a sample transactions table. One SQL question the whole time, but it had enough edge cases to trip you up if you weren't careful.

Questions Asked (1)

Q1

Given a transactions table with user IDs, revenue, and order dates, write a SQL query that returns each calendar week along with the percentage change in total revenue compared to the prior week.

Product Analytics & MetricsData Modeling
Author's notes

The DATE_TRUNC part was fine, that's muscle memory at this point.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by aggregating revenue per calendar week using a date truncation function, then use a window function like LAG to access the prior week's revenue. Compute the percentage change with a safe division to handle nulls or zero values, and order the final result by week.

Pro tip: Explicitly discuss how you would handle edge cases like the first week (no prior week) and weeks with zero revenue, and mention that you'd validate the query against a small sample to ensure correctness.

1. Aggregate revenue by week

Use DATE_TRUNC or equivalent to group transactions into calendar weeks and sum revenue for each week.

2. Access prior week's revenue

Apply the LAG window function over the weekly revenue, ordered by week, to get the previous week's total.

3. Compute percentage change

Calculate (current_revenue - prior_revenue) / prior_revenue * 100, using NULLIF or CASE to avoid division by zero.

4. Format and order output

Select the week and percentage change, order by week ascending, and optionally round the percentage for readability.

Key Points to Mention

  • Use of DATE_TRUNC('week', order_date) to define calendar weeks (note: weeks start on Monday in most SQL dialects).
  • Window function LAG(revenue) OVER (ORDER BY week) to get prior week's revenue.
  • Handling division by zero with NULLIF or CASE to avoid errors.
  • The first week will have NULL percentage change; decide whether to include it or filter it out.
  • Consider time zone and week start day conventions if data spans multiple regions.
  • Performance considerations: indexing on order_date and pre-aggregating if the table is large.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.