Coding questions · Meta
Compute unconnected 60s posts and reactions averages
This question evaluates data manipulation skills in SQL and Python, focusing on deduplication, join logic between event and reaction tables, date handling for string-typed dates, and aggregation to compute per-post metrics.
Given these tables and sample data, write SQL that answers both tasks below. Use today = 2025-09-01 and interpret "last/past 7 days" as the inclusive window 2025-08-26 through 2025-09-01. Schemas:
info_stream_views
- post_id BIGINT -- ID of post
- viewer_id BIGINT -- ID of user who viewed the post
- relationship STRING -- {Friend, Followee, Unconnected}
- duration DOUBLE -- seconds watched for that view event
- ds STRING -- 'YYYY-MM-DD' event date
post_reactions
- post_id INTEGER
- viewer_id INTEGER
- post_action STRING -- {like, comment, reshare}
- ds STRING -- 'YYYY-MM-DD'
Sample rows (small, illustrative):
info_stream_views
post_id | viewer_id | relationship | duration | ds
1 | 11 | Unconnected | 70.0 | 2025-08-31
1 | 10 | Friend | 45.0 | 2025-08-31
2 | 12 | Unconnected | 30.0 | 2025-08-30
2 | 13 | Friend | 120.5 | 2025-08-30
3 | 14 | Followee | 61.0 | 2025-08-26
3 | 11 | Unconnected | 62.0 | 2025-08-27
post_reactions
post_id | viewer_id | post_action | ds
1 | 11 | like | 2025-08-31
1 | 11 | comment | 2025-08-31
2 | 12 | like | 2025-08-30
2 | 13 | reshare | 2025-08-30
3 | 11 | like | 2025-08-27
Task A (distinct-post count): Return the number of distinct post_id that have at least one Unconnected view with duration > 60 seconds within 2025-08-26..2025-09-01 (inclusive). If multiple view rows exist for the same (post_id, viewer_id, ds), deduplicate by taking MAX(duration) for that key before applying the > 60s filter.
Task B (friend vs unconnected averages): Compute the average number of reactions per post attributable to Friend vs Unconnected viewers within the same 7-day window. Classify each reaction by joining post_reactions to info_stream_views on (post_id, viewer_id, ds); if multiple matches exist for a given (post_id, viewer_id, ds), prefer the row with the greatest duration. Count all reaction types equally (each = 1). For each relationship group in {Friend, Unconnected}, define the denominator as the number of distinct post_id that appeared in info_stream_views in the window; posts with zero reactions in a group should contribute 0 to that group's average. Return two rows: relationship, avg_reactions_per_post.
Edge cases to handle explicitly: mixed relationships across days, multiple views per user per post per day, reactions without a matching view (exclude these), and ds stored as STRING.
Count posts with >60s Unconnected views in a 7-day window
You are given two tables, info_stream_views and post_reactions, that track post views and reactions over time. Using the schema and sample data below, write a SQL query to answer the following:
Within the date range 2025-08-26 through 2025-09-01 (inclusive), return the number of distinct post_id values that have at least one Unconnected view with duration > 60 seconds.
Important details:
- The ds column is stored as a string in 'YYYY-MM-DD' format.
- Before applying the duration > 60 seconds filter, you must deduplicate info_stream_views by (post_id, viewer_id, ds). If multiple view rows exist for the same (post_id, viewer_id, ds), keep only the row with the greatest duration (and its associated relationship).
- Only Unconnected views should be considered for this count.
Return a single row with a single column named unconnected_60s_post_count.
Table info_stream_views
| column | type | constraints |
|---|---|---|
| post_id | BIGINT | NOT NULL |
| viewer_id | BIGINT | NOT NULL |
| relationship | VARCHAR(20) | NOT NULL |
| duration | DOUBLE | NOT NULL |
| ds | VARCHAR(10) | NOT NULL |
Sample data:
| ds | duration | post_id | relationship | viewer_id |
|---|---|---|---|---|
| 2025-08-31 | 70 | 1 | Unconnected | 11 |
| 2025-08-31 | 50 | 1 | Unconnected | 11 |
| 2025-08-31 | 45 | 1 | Friend | 10 |
| 2025-08-30 | 30 | 2 | Unconnected | 12 |
| 2025-08-30 | 120.5 | 2 | Friend | 13 |
| 2025-08-26 | 61 | 3 | Followee | 14 |
| 2025-08-27 | 62 | 3 | Unconnected | 11 |
| 2025-08-28 | 55 | 4 | Friend | 15 |
| 2025-08-30 | 65 | 4 | Unconnected | 15 |
| 2025-08-30 | 10 | 4 | Friend | 16 |
| 2025-08-29 | 59 | 6 | Unconnected | 17 |
| 2025-08-29 | 120 | 6 | Unconnected | 18 |
| 2025-08-25 | 80 | 7 | Unconnected | 19 |
Table post_reactions
| column | type | constraints |
|---|---|---|
| post_id | BIGINT | NOT NULL |
| viewer_id | BIGINT | NOT NULL |
| post_action | VARCHAR(20) | NOT NULL |
| ds | VARCHAR(10) | NOT NULL |
Sample data:
| ds | post_action | post_id | viewer_id |
|---|---|---|---|
| 2025-08-31 | like | 1 | 11 |
| 2025-08-31 | comment | 1 | 11 |
| 2025-08-30 | like | 2 | 12 |
| 2025-08-30 | reshare | 2 | 13 |
| 2025-08-27 | like | 3 | 11 |
| 2025-08-28 | like | 4 | 15 |
| 2025-08-30 | like | 4 | 15 |
| 2025-08-30 | comment | 4 | 16 |
| 2025-08-29 | like | 6 | 17 |
| 2025-08-29 | reshare | 6 | 18 |
| 2025-08-31 | like | 5 | 20 |
Expected output
A single-row, single-column result giving the count of distinct posts that, within 2025-08-26 to 2025-09-01 inclusive, have at least one Unconnected view whose deduplicated duration exceeds 60 seconds.
| unconnected_60s_post_count |
|---|
| 4 |
Hints
- First deduplicate info_stream_views by (post_id, viewer_id, ds) using a window function that keeps the row with the maximum duration.
- After deduplication, filter to Unconnected rows with duration > 60 seconds within the date range and count distinct post_id.
Average reactions per post for Friend vs Unconnected viewers
Write a PostgreSQL query. Using the same info_stream_views and post_reactions tables and sample data, compute the average number of reactions per post attributable to Friend vs Unconnected viewers within the date range 2025-08-26 through 2025-09-01 (inclusive).
Requirements:
- Deduplicate views:
- In info_stream_views, multiple rows can exist for the same (post_id, viewer_id, ds).
- For each (post_id, viewer_id, ds), keep only the row with the greatest duration and use its relationship value.
- Classify reactions:
- Join post_reactions to the deduplicated info_stream_views on (post_id, viewer_id, ds).
- If a reaction has no matching view in info_stream_views for that (post_id, viewer_id, ds), exclude that reaction entirely.
- Use the relationship from the matched view to classify each reaction as Friend or Unconnected; ignore other relationship types.
- Count all reaction types equally (each reaction counts as 1).
- Date filtering:
- Only consider rows where ds is between '2025-08-26' and '2025-09-01' inclusive in both tables.
- Denominator definition:
- For each relationship group in {Friend, Unconnected}, the denominator is the number of distinct post_id that appeared in info_stream_views (any relationship) within the date range.
- Posts with zero reactions in a group should still be included in that group's denominator, effectively contributing 0 to the average.
Output:
- Return exactly two rows, one for each relationship in {Friend, Unconnected}.
- Columns: relationship, avg_reactions_per_post.
Table info_stream_views
| column | type | constraints |
|---|---|---|
| post_id | BIGINT | NOT NULL |
| viewer_id | BIGINT | NOT NULL |
| relationship | VARCHAR(20) | NOT NULL |
| duration | DOUBLE PRECISION | NOT NULL |
| ds | VARCHAR(10) | NOT NULL |
Sample data:
| ds | duration | post_id | relationship | viewer_id |
|---|---|---|---|---|
| 2025-08-31 | 70 | 1 | Unconnected | 11 |
| 2025-08-31 | 50 | 1 | Unconnected | 11 |
| 2025-08-31 | 45 | 1 | Friend | 10 |
| 2025-08-30 | 30 | 2 | Unconnected | 12 |
| 2025-08-30 | 120.5 | 2 | Friend | 13 |
| 2025-08-26 | 61 | 3 | Followee | 14 |
| 2025-08-27 | 62 | 3 | Unconnected | 11 |
| 2025-08-28 | 55 | 4 | Friend | 15 |
| 2025-08-30 | 65 | 4 | Unconnected | 15 |
| 2025-08-30 | 10 | 4 | Friend | 16 |
| 2025-08-29 | 59 | 6 | Unconnected | 17 |
| 2025-08-29 | 120 | 6 | Unconnected | 18 |
| 2025-08-25 | 80 | 7 | Unconnected | 19 |
Table post_reactions
| column | type | constraints |
|---|---|---|
| post_id | BIGINT | NOT NULL |
| viewer_id | BIGINT | NOT NULL |
| post_action | VARCHAR(20) | NOT NULL |
| ds | VARCHAR(10) | NOT NULL |
Sample data:
| ds | post_action | post_id | viewer_id |
|---|---|---|---|
| 2025-08-31 | like | 1 | 11 |
| 2025-08-31 | comment | 1 | 11 |
| 2025-08-30 | like | 2 | 12 |
| 2025-08-30 | reshare | 2 | 13 |
| 2025-08-27 | like | 3 | 11 |
| 2025-08-28 | like | 4 | 15 |
| 2025-08-30 | like | 4 | 15 |
| 2025-08-30 | comment | 4 | 16 |
| 2025-08-29 | like | 6 | 17 |
| 2025-08-29 | reshare | 6 | 18 |
| 2025-08-31 | like | 5 | 20 |
Expected output
Two rows, one for Friend and one for Unconnected, giving the average number of reactions per post over all posts that appeared in info_stream_views between 2025-08-26 and 2025-09-01 (inclusive). Reactions are counted only when they can be joined to a deduplicated view on (post_id, viewer_id, ds), and they are attributed to the relationship of that view.
| avg_reactions_per_post | relationship |
|---|---|
| 0.6 | Friend |
| 1.4 | Unconnected |
Hints
- First deduplicate info_stream_views by (post_id, viewer_id, ds) using a window function, then join reactions on that deduplicated set.
- Compute total reactions per relationship, compute the distinct post count from the views as a separate denominator, and divide; use a small relationships helper table or VALUES list to ensure both Friend and Unconnected appear even if one has zero reactions.