The zero-session days thing tripped me up more than I expected.
Start by clarifying the requirements: define the reporting period, the grain (shop-day), and the rolling window (previous 7 days excluding current). Then outline a SQL-based solution using a date spine to ensure all shop-day combinations appear, left join session counts, and compute the rolling average with a window function that excludes the current row.
Pro tip: Mention that you would validate the rolling average by manually checking a few shop-day pairs, especially around period boundaries, and discuss how to handle partial windows (e.g., first days of the period) to avoid misleading averages.
Confirm the reporting period, definition of a session, and whether the rolling average should be computed over the previous 7 days including or excluding the current day. Also clarify how to handle days with zero sessions and shops with no sessions at all.
Create a cross join of all shops and all dates in the reporting period to ensure every shop-day combination appears, even if there were no sessions. This can be done with a calendar table or a recursive CTE.
Compute the total number of sessions per shop per day from the sessions table, then left join this to the date-shop spine and coalesce nulls to zero.
Use a window function with a frame that includes the previous 7 days (e.g., ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) partitioned by shop and ordered by date. Ensure that days with zero sessions are included in the average.
Check results for correctness, especially at the start of the period where fewer than 7 days are available. Decide whether to show null or compute average over available days, and document the choice.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.