Coding questions · Meta
Calculate Recent Post Views and Reactions for Social Media
This question evaluates proficiency in SQL-based data manipulation and analytics, focusing on time-window filtering, grouping and aggregation (distinct counts and averages), and combining view and reaction event tables.
info_stream_views
+---------+-----------+--------------+----------+------------+
| post_id | viewer_id | relationship | duration | ds |
+---------+-----------+--------------+----------+------------+
| 101 | 2001 | Friend | 45.7 | 2023-10-01 |
| 102 | 2002 | Unconnected | 67.2 | 2023-10-01 |
| 103 | 2003 | Followee | 12.0 | 2023-10-02 |
| 104 | 2004 | Unconnected | 80.5 | 2023-10-02 |
| 105 | 2005 | Friend | 30.0 | 2023-10-03 |
+---------+-----------+--------------+----------+------------+
post_reactions
+---------+-----------+-------------+------------+
| post_id | viewer_id | post_action | ds |
+---------+-----------+-------------+------------+
| 101 | 2001 | like | 2023-10-01 |
| 101 | 2002 | comment | 2023-10-01 |
| 102 | 2003 | reshare | 2023-10-02 |
| 103 | 2001 | like | 2023-10-02 |
| 104 | 2004 | like | 2023-10-03 |
+---------+-----------+-------------+------------+
Scenario
Info stream engagement analytics for a social media platform; need recent metrics about post views and reactions.
Question
Write a SQL query to find the number of distinct posts that accumulated at least one view longer than 60 seconds from viewers whose relationship to the author is 'Unconnected' during the last 7 days (relative to today). Write a SQL query to compute the average number of reactions (like, comment, reshare) per post separately for 'Friend' viewers and 'Unconnected' viewers over the last 7 days.
Hints
Filter last 7 days, use relationship filter, group/aggregate by post and relationship.
Distinct Posts with Long Unconnected Views
Count the number of distinct posts that had at least one Unconnected viewer with a view longer than 60 seconds during the 7-day period from 2025-05-26 to 2025-06-01 (inclusive).
Table info_stream_views
| column | type | constraints |
|---|---|---|
| post_id | INTEGER | |
| viewer_id | INTEGER | |
| relationship | VARCHAR(20) | |
| duration | DECIMAL(5,1) | |
| ds | DATE |
Sample data:
| ds | duration | post_id | relationship | viewer_id |
|---|---|---|---|---|
| 2025-05-30 | 45.7 | 101 | Friend | 2001 |
| 2025-05-30 | 67.2 | 102 | Unconnected | 2002 |
| 2025-05-31 | 12 | 103 | Followee | 2003 |
| 2025-05-31 | 80.5 | 104 | Unconnected | 2004 |
| 2025-06-01 | 30 | 105 | Friend | 2005 |
Table post_reactions
| column | type | constraints |
|---|---|---|
| post_id | INTEGER | |
| viewer_id | INTEGER | |
| post_action | VARCHAR(20) | |
| ds | DATE |
Sample data:
| ds | post_action | post_id | viewer_id |
|---|---|---|---|
| 2025-05-30 | like | 101 | 2001 |
| 2025-05-30 | comment | 101 | 2002 |
| 2025-05-31 | reshare | 102 | 2003 |
| 2025-05-31 | like | 103 | 2001 |
| 2025-06-01 | like | 104 | 2004 |
Expected output
Distinct post count with an Unconnected view longer than 60 seconds between 2025-05-26 and 2025-06-01 (inclusive). Using the sample data, posts 102 and 104 qualify, so the result is 2.
| post_count |
|---|
| 2 |
Hints
- Filter by relationship = 'Unconnected' and duration > 60 before counting distinct posts.
- Use the fixed date window: ds BETWEEN '2025-05-26' AND '2025-06-01'.
Average Reactions Per Post by Relationship
Compute the average number of reactions per post during the 7-day period from 2025-05-26 to 2025-06-01 (inclusive), reported separately for Friend and Unconnected viewers. Determine each reaction's viewer relationship using the latest available view record for that (post_id, viewer_id) pair in info_stream_views.
Table info_stream_views
| column | type | constraints |
|---|---|---|
| post_id | INTEGER | |
| viewer_id | INTEGER | |
| relationship | VARCHAR(20) | |
| duration | DECIMAL(5,1) | |
| ds | DATE |
Sample data:
| ds | duration | post_id | relationship | viewer_id |
|---|---|---|---|---|
| 2025-05-30 | 45.7 | 101 | Friend | 2001 |
| 2025-05-30 | 67.2 | 102 | Unconnected | 2002 |
| 2025-05-31 | 12 | 103 | Followee | 2003 |
| 2025-05-31 | 80.5 | 104 | Unconnected | 2004 |
| 2025-06-01 | 30 | 105 | Friend | 2005 |
Table post_reactions
| column | type | constraints |
|---|---|---|
| post_id | INTEGER | |
| viewer_id | INTEGER | |
| post_action | VARCHAR(20) | |
| ds | DATE |
Sample data:
| ds | post_action | post_id | viewer_id |
|---|---|---|---|
| 2025-05-30 | like | 101 | 2001 |
| 2025-05-30 | comment | 101 | 2002 |
| 2025-05-31 | reshare | 102 | 2003 |
| 2025-05-31 | like | 103 | 2001 |
| 2025-06-01 | like | 104 | 2004 |
Expected output
Average number of reactions per post between 2025-05-26 and 2025-06-01, grouped by viewer relationship (Friend, Unconnected). Using the sample data, the average is 1.0 for both Friend and Unconnected viewers.
| avg_reactions_per_post | relationship |
|---|---|
| 1 | Friend |
| 1 | Unconnected |
Hints
- First, restrict to reactions where ds is between '2025-05-26' and '2025-06-01'.
- Use a window function on info_stream_views to get the latest relationship per (post_id, viewer_id).
- Join reactions to these relationships, count reactions per post per relationship, then average by relationship.