This question evaluates a candidate's proficiency in data manipulation and aggregation using SQL (or Python) to compute time-windowed metrics from event data, including filtering by boolean flags and summing durations per post.

You work on a social app where users can view posts. A view can be from a connected user (viewer is friends/connected with the post author) or unconnected (not connected).
Assume the following schema (UTC timestamps):
post_viewsview_id
STRING (PK)
post_id
STRING
viewer_id
STRING
view_ts
TIMESTAMP (UTC)
view_duration_seconds
INT
is_connected
BOOLEAN
TRUE
if the viewer is connected to the post’s author at the time of view
Write a SQL query to find posts whose total unconnected view time in the last 7 days is greater than 60 seconds.
[current_timestamp - 7 days, current_timestamp)
in UTC.
is_connected = FALSE
.
post_id
.
Return:
post_id
unconnected_view_seconds_7d
(sum of
view_duration_seconds
over the window)
Order by unconnected_view_seconds_7d descending.