Find posts with >60s unconnected viewing time

Q: Find posts with >60s unconnected viewing time

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.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

Context

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).

Tables

Assume the following schema (UTC timestamps):

`post_views`

view_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

Task

Write a SQL query to find posts whose total unconnected view time in the last 7 days is greater than 60 seconds.

Requirements

Time window: [current_timestamp - 7 days, current_timestamp) in UTC.
Only include unconnected views: is_connected = FALSE .
Aggregate per post_id .

Output

Return:

post_id
unconnected_view_seconds_7d (sum of view_duration_seconds over the window)

Order by unconnected_view_seconds_7d descending.