Find posts with >60s unconnected viewing time
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
## 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.
Overview: 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.
Read the full Meta Data Scientist interview experience this question came from
You are given a table of post view sessions. Each row represents one viewing session for a post by a viewer, with the number of seconds watched and whether the viewer is "connected" (e.g., a friend/follower) to the post creator.
For the date range FROM 2025-05-26 TO 2025-06-01 (inclusive), return all posts whose total **unconnected** viewing time is **strictly greater than 60 seconds**.
Return:
- post_id
- unconnected_view_seconds_7d (sum of view_seconds where is_connected = 0 within the date range)
Sort by unconnected_view_seconds_7d descending, then post_id ascending.
Tables
post_view_sessions(session_id INT, post_id INT, viewer_id INT, view_date DATE, view_seconds INT, is_connected SMALLINT)
Hints
- Use conditional aggregation for unconnected viewing seconds.
- Apply the threshold in `HAVING` after grouping by `post_id`.
Community answers
Answer by SS
Select post_id , sum(case when is_connected is False then view_duration_Seconds else 0 end) as unconnected_view
from post_views
where date(view_ts) >= current_date - interval '7 days' and date(view_ts) < current_Date
group by 1
having sum(case when is_connected is False then view_duration_Seconds else 0 end) > 60
order by 2 desc