Find posts with >60s unconnected viewing time

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.

Find posts with >60 seconds of unconnected viewing time in the last 7 days

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.

Table post_view_sessions

column	type	constraints
session_id	INT	PRIMARY KEY, NOT NULL
post_id	INT	NOT NULL
viewer_id	INT	NOT NULL
view_date	DATE	NOT NULL
view_seconds	INT	NOT NULL
is_connected	SMALLINT	NOT NULL

Sample data:

is_connected	post_id	session_id	view_date	view_seconds	viewer_id
0	101	1	2025-05-26	30	501
0	101	2	2025-05-27	40	502
1	101	3	2025-05-28	10	503
0	102	4	2025-05-29	60	504
0	102	5	2025-06-01	1	505
0	103	6	2025-05-26	61	506
0	103	7	2025-05-25	100	507
0	104	8	2025-05-30	50	508
1	104	9	2025-05-31	11	509
1	105	10	2025-06-01	61	510

Expected output

One row per post where the sum of unconnected viewing seconds between 2025-05-26 and 2025-06-01 (inclusive) is > 60. Columns: post_id, unconnected_view_seconds_7d.

post_id	unconnected_view_seconds_7d
101	70
102	61
103	61

Hints

1. Use conditional aggregation for unconnected viewing seconds.
2. Apply the threshold in HAVING after grouping by post_id.