Compute 7-day views and reactions by relationship

Use the schemas and sample data below to answer two tasks. Assume dates are strings in 'YYYY-MM-DD'. Treat "today" as 2025-09-01; "last/past 7 days" means ds between 2025-08-26 and 2025-09-01 inclusive. If a viewer has multiple views of the same post on the same day, deduplicate by keeping the row with the maximum duration.

Schemas:

• info_stream_views(post_id BIGINT, viewer_id BIGINT, relationship STRING CHECK (relationship IN ('Friend','Followee','Unconnected')), duration DOUBLE, ds STRING)
• post_reactions(post_id INTEGER, viewer_id INTEGER, post_action STRING CHECK (post_action IN ('like','comment','reshare')), ds STRING)

ASCII sample tables:

info_stream_views
post_id | viewer_id | relationship | duration | ds
101 | 10 | Friend | 45 | 2025-08-26
101 | 10 | Friend | 75 | 2025-08-27
101 | 11 | Unconnected | 61 | 2025-08-28
102 | 12 | Unconnected | 30 | 2025-08-28
103 | 13 | Followee | 90 | 2025-08-29
104 | 14 | Unconnected | 120 | 2025-08-30
104 | 14 | Unconnected | 20 | 2025-08-30
105 | 10 | Unconnected | 59 | 2025-09-01

post_reactions
post_id | viewer_id | post_action | ds
101 | 10 | like | 2025-08-27
101 | 11 | comment | 2025-08-28
104 | 14 | reshare | 2025-08-30
104 | 14 | like | 2025-08-30
103 | 13 | like | 2025-08-29
105 | 10 | like | 2025-09-01

Tasks: Q1) Write a single SQL query that returns the count of DISTINCT post_id that had at least one Unconnected view with duration > 60 seconds within 2025-08-26..2025-09-01. Ensure a post is counted once even if multiple qualifying viewers/days exist. Q2) For the same 7-day window, compute two numbers: avg_reactions_per_post for Friend and for Unconnected. Define reactions-to-relationship by joining reactions to views on (post_id, viewer_id, ds) and, if multiple same-day views exist, attribute the reaction to the view with the longest duration. For each relationship group G in {'Friend','Unconnected'}: numerator = total count of reactions made by viewers labeled G; denominator = count of DISTINCT post_id that had at least one view by viewers labeled G in the window (include posts with zero reactions in the denominator). Return a two-row result set: relationship, avg_reactions_per_post (as a decimal). Use window functions or CTEs to handle deduping and joins, and be robust to posts that appear in both groups.

Count posts with long Unconnected views in a 7-day window

Using the tables below, write a single SQL query that returns the count of DISTINCT post_id that had at least one Unconnected view with duration > 60 seconds between '2025-08-26' and '2025-09-01' (inclusive).

Important details:

• If a viewer has multiple views of the same post on the same day, deduplicate by keeping the row with the maximum duration for that (post_id, viewer_id, ds) combination.
• Only consider views where relationship = 'Unconnected'.
• A post should be counted at most once, even if it has multiple qualifying viewers or days.

Return a single-row, single-column result with the count (e.g., num_posts).

Table info_stream_views

column	type	constraints
post_id	BIGINT	NOT NULL
viewer_id	BIGINT	NOT NULL
relationship	VARCHAR(20)	NOT NULL, CHECK (relationship IN ('Friend','Followee','Unconnected'))
duration	DOUBLE	NOT NULL
ds	VARCHAR(10)	NOT NULL

Sample data:

ds	duration	post_id	relationship	viewer_id
2025-08-26	45	101	Friend	10
2025-08-27	75	101	Friend	10
2025-08-28	61	101	Unconnected	11
2025-08-28	30	102	Unconnected	12
2025-08-29	90	103	Followee	13
2025-08-30	120	104	Unconnected	14
2025-08-30	20	104	Unconnected	14
2025-09-01	59	105	Unconnected	10

Table post_reactions

column	type	constraints
post_id	INT	NOT NULL
viewer_id	INT	NOT NULL
post_action	VARCHAR(20)	NOT NULL, CHECK (post_action IN ('like','comment','reshare'))
ds	VARCHAR(10)	NOT NULL

Sample data:

ds	post_action	post_id	viewer_id
2025-08-27	like	101	10
2025-08-28	comment	101	11
2025-08-30	reshare	104	14
2025-08-30	like	104	14
2025-08-29	like	103	13
2025-09-01	like	105	10

Expected output

Single-row result with the count of distinct posts that had at least one Unconnected view with duration > 60 seconds between '2025-08-26' and '2025-09-01', after deduplicating multiple same-day views per (post_id, viewer_id, ds) by keeping the maximum duration.

num_posts
2

Hints

1. First deduplicate same-day views using GROUP BY on (post_id, viewer_id, ds) and MAX(duration).
2. After deduplication, filter to Unconnected views with duration > 60 and use COUNT(DISTINCT post_id).

Average reactions per post by relationship over 7 days

Using the same tables, compute average reactions per post for each relationship group 'Friend' and 'Unconnected' between '2025-08-26' and '2025-09-01' (inclusive).

Definitions and requirements:

• First, deduplicate views: if a viewer has multiple views of the same post on the same day, keep only the row with the maximum duration for that (post_id, viewer_id, ds).
• Define the relationship for a reaction by joining post_reactions to the deduplicated info_stream_views on (post_id, viewer_id, ds). If multiple same-day views existed originally, the reaction should be attributed to the view with the longest duration via the deduped table.
• For each relationship group G in {'Friend','Unconnected'}:
  • Numerator(G) = total count of reactions made by viewers labeled with relationship G in the deduplicated views.
  • Denominator(G) = count of DISTINCT post_id that had at least one view by viewers labeled with relationship G in the window (include posts with zero reactions in the denominator).
• Posts that are viewed under both relationships should contribute independently to each group's denominator as appropriate.

Return a two-row result set with columns:

• relationship (one of 'Friend', 'Unconnected')
• avg_reactions_per_post (a decimal value, Numerator(G) / Denominator(G))

Table info_stream_views

column	type	constraints
post_id	BIGINT	NOT NULL
viewer_id	BIGINT	NOT NULL
relationship	VARCHAR(20)	NOT NULL, CHECK (relationship IN ('Friend','Followee','Unconnected'))
duration	DOUBLE	NOT NULL
ds	VARCHAR(10)	NOT NULL

Sample data:

ds	duration	post_id	relationship	viewer_id
2025-08-26	45	101	Friend	10
2025-08-27	75	101	Friend	10
2025-08-28	61	101	Unconnected	11
2025-08-28	30	102	Unconnected	12
2025-08-29	90	103	Followee	13
2025-08-30	120	104	Unconnected	14
2025-08-30	20	104	Unconnected	14
2025-09-01	59	105	Unconnected	10

Table post_reactions

column	type	constraints
post_id	INT	NOT NULL
viewer_id	INT	NOT NULL
post_action	VARCHAR(20)	NOT NULL, CHECK (post_action IN ('like','comment','reshare'))
ds	VARCHAR(10)	NOT NULL

Sample data:

ds	post_action	post_id	viewer_id
2025-08-27	like	101	10
2025-08-28	comment	101	11
2025-08-30	reshare	104	14
2025-08-30	like	104	14
2025-08-29	like	103	13
2025-09-01	like	105	10

Expected output

Two-row result with average reactions per post for Friend and Unconnected viewers over the specified 7-day window, using deduplicated views and joining reactions on (post_id, viewer_id, ds). With the sample data, each group has numerator = 1 (Friend: 1 reaction on post 101; Unconnected: 4 reactions across posts 101, 104, 104, 105) and denominator = 1 for Friend (post 101) and 4 for Unconnected (posts 101, 102, 104, 105), so both averages are 1.00.

avg_reactions_per_post	relationship
1	Friend
1	Unconnected

Hints

1. Start by deduplicating views in a CTE using GROUP BY (post_id, viewer_id, ds) and MAX(duration), then filter to Friend and Unconnected.
2. Join reactions to the deduplicated views on (post_id, viewer_id, ds) to assign a relationship, then separately aggregate posts-with-views and reactions-by-relationship and combine them to compute the averages.