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.
Quick Answer: 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.