The two-step aggregation part is where I fumbled initially.
First, filter the table to the year 2020 and extract the ISO week using a function like EXTRACT(WEEK FROM timestamp). Then, group by that week and count the views (or sum if there's a view count column) to get weekly totals, and finally compute the average of those totals using a subquery or CTE.
Pro tip: Clarify whether 'views' means counting rows or summing a separate views column, and mention that ISO weeks can span year boundaries, so filtering by year might need adjustment for weeks that start in 2019 or end in 2021.
Ask whether each row represents a single view or if there is a separate views count column. This determines whether to use COUNT(*) or SUM(views).
Use a WHERE clause to restrict the data to timestamps within 2020. Be mindful of time zones and whether to use calendar year or ISO year.
Use EXTRACT(WEEK FROM timestamp) or equivalent to get the ISO week number, then GROUP BY that week and compute the total views per week.
Wrap the weekly aggregation in a subquery or CTE and compute the average of the weekly sums using AVG().
Check for weeks with zero views (if they should be included) and ensure the query handles ISO weeks that cross year boundaries correctly.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.