Compute 7-day views and reactions by relationship
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates skills in SQL-based data manipulation—specifically deduplication by longest duration, join-based attribution of reactions to views, window functions, distinct counts, aggregations, and date-range filtering within the Data Manipulation (SQL/Python) domain.
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).
Tables
info_stream_views(post_id BIGINT, viewer_id BIGINT, relationship VARCHAR(20), duration DOUBLE, ds VARCHAR(10))
post_reactions(post_id INT, viewer_id INT, post_action VARCHAR(20), ds VARCHAR(10))
Hints
- First deduplicate same-day views using GROUP BY on (post_id, viewer_id, ds) and MAX(duration).
- 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))
Tables
info_stream_views(post_id BIGINT, viewer_id BIGINT, relationship VARCHAR(20), duration DOUBLE, ds VARCHAR(10))
post_reactions(post_id INT, viewer_id INT, post_action VARCHAR(20), ds VARCHAR(10))
Hints
- Start by deduplicating views in a CTE using GROUP BY (post_id, viewer_id, ds) and MAX(duration), then filter to Friend and Unconnected.
- 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.