The window function part I got fine, ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING is pretty textbook once you know it exists.
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.
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.
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.
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.
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.
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.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.