← Shopify Interview Insights

Shopify·Software Engineer·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

Shopify data/analytics interview with a SQL-heavy question that required more than just knowing window functions. The zero-session day handling tripped me up more than I expected.

Questions Asked (1)

Q1

For each shop and each day in a reporting period, return the daily session count and a 7-day trailing average that excludes the current day. Days with zero sessions must still appear in the output and count toward the average.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs
Author's notes

The window function part I got fine, ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING is pretty textbook once you know it exists.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the requirements: the output should include every shop-day combination in the reporting period, even if there are zero sessions. Then explain how you would generate a complete date spine per shop, left join session data, and compute the 7-day trailing average excluding the current day using a window function with a ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING clause. Finally, discuss how to handle edge cases like the first days of the period where fewer than 7 prior days exist.

Pro tip: Mention that the trailing average should be computed over the full 7-day window even if some days have zero sessions, and that you would use COALESCE to treat missing session counts as zero. Also, note that you should confirm whether the average should be based on calendar days or business days, as this affects the date spine generation.

1. Clarify requirements and edge cases

Ask about the reporting period boundaries, whether all shops must appear even if they have no sessions, and how to handle days before the period starts (e.g., should the trailing average include days outside the period?). Confirm that zero-session days count toward the average.

2. Generate a complete date-shop spine

Create a cross join of all shops and all dates in the reporting period to ensure every shop-day combination is represented. This can be done with a calendar table or a recursive CTE.

3. Aggregate daily sessions and left join

Aggregate session counts per shop per day from the raw events table, then left join this to the date-shop spine. Use COALESCE to replace null session counts with 0.

4. Compute 7-day trailing average excluding current day

Use a window function: AVG(session_count) OVER (PARTITION BY shop_id ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING). This ensures the current day is excluded and the window covers the previous 7 days.

5. Handle initial days and validate output

For the first days of the period, the window will have fewer than 7 rows; decide whether to return the average of available days or null. Validate that zero-session days are included and that the average is correct by manually checking a few shop-day combinations.

Key Points to Mention

  • Use of a date spine (calendar table or recursive CTE) to ensure all shop-day combinations appear, including zero-session days.
  • Left join session aggregates to the spine and use COALESCE to treat missing sessions as zero.
  • Window function with ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING to exclude the current day and include exactly the previous 7 days.
  • Consideration of edge cases: first days of the period, shops with no sessions at all, and whether the trailing average should include days outside the reporting period.
  • Performance implications: partitioning by shop and ordering by date, and ensuring the date spine is efficient (e.g., using a pre-built calendar table).
  • Clarify whether the average should be over calendar days or business days, and whether to round or keep decimals.

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