Straightforward once you see it, but I spent a weirdly long time second-guessing the timestamp filter.
Start by clarifying the schema and assumptions (e.g., column names, time window definition, whether view_time is in seconds). Then write a single aggregation query that filters for unconnected viewers and events in the last 7 days, groups by post ID, sums view time, and applies a HAVING clause to keep only sums > 60 seconds, finally ordering by the sum descending.
Pro tip: Mention that you would validate the 7-day window definition (e.g., rolling 7 days from current timestamp vs. calendar days) and consider edge cases like null view_time or duplicate events, showing attention to data quality and business context.
Confirm column names, data types, and the exact definition of 'last 7 days' (e.g., rolling window from now or from a fixed date). Ask about any filters like excluding bot traffic.
Use a WHERE clause to select only events where the viewer is unconnected (is_connected = false) and the event timestamp falls within the last 7 days.
Group by post_id and compute SUM(view_time) as total_unconnected_view_seconds.
Use HAVING to keep only groups where the sum exceeds 60 seconds, and ORDER BY the sum descending.
Return post_id and the computed sum as the output columns, ensuring aliases are clear.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.