Coding questions · Meta
Analyze Recent Post Performance Using SQL Queries
This question evaluates SQL-based data manipulation skills including joins, aggregation, temporal filtering, and audience segmentation to calculate viewing-time and reaction metrics.
INFO_STREAM_VIEWS
+---------+-----------+--------------+----------+------------+
| post_id | viewer_id | relationship | duration | ds |
+---------+-----------+--------------+----------+------------+
| 1 | 101 | Friend | 75.2 | 2023-08-10 |
| 1 | 102 | Unconnected | 45.0 | 2023-08-10 |
| 2 | 103 | Followee | 120.5 | 2023-08-11 |
| 3 | 104 | Unconnected | 65.0 | 2023-08-11 |
| 4 | 105 | Friend | 30.0 | 2023-08-12 |
+---------+-----------+--------------+----------+------------+
POST_REACTIONS
+---------+-----------+-------------+------------+
| post_id | viewer_id | post_action | ds |
+---------+-----------+-------------+------------+
| 1 | 101 | like | 2023-08-10 |
| 1 | 102 | comment | 2023-08-10 |
| 2 | 103 | reshare | 2023-08-11 |
| 3 | 104 | like | 2023-08-11 |
| 3 | 104 | comment | 2023-08-11 |
+---------+-----------+-------------+------------+
Scenario
Social-content feed needs SQL to report recent performance of posts.
Question
Q1. Write a query to count posts that received >60 seconds of viewing time from unconnected audiences in the last 7 days. Q2. Write a query to compute the average number of reactions per post coming from friends and from unconnected viewers in the last 7 days.
Hints
Leverage info_stream_views for watch-time filtering and join to post_reactions for reaction counts; window of CURRENT_DATE-6 to CURRENT_DATE.
Count posts with >60s unconnected watch
Count distinct posts that received more than 60 seconds of total viewing time from unconnected audiences within the fixed 7-day window from 2023-08-06 to 2023-08-12. Return a single row with metric, relationship (use 'All'), and value.
Table INFO_STREAM_VIEWS
| column | type | constraints |
|---|---|---|
| post_id | INTEGER | NOT NULL |
| viewer_id | INTEGER | NOT NULL |
| relationship | VARCHAR(20) | NOT NULL |
| duration | DECIMAL(10,1) | NOT NULL |
| ds | DATE | NOT NULL |
Sample data:
| ds | duration | post_id | relationship | viewer_id |
|---|---|---|---|---|
| 2023-08-10 | 75.2 | 1 | Friend | 101 |
| 2023-08-10 | 45 | 1 | Unconnected | 102 |
| 2023-08-11 | 120.5 | 2 | Followee | 103 |
| 2023-08-11 | 65 | 3 | Unconnected | 104 |
| 2023-08-12 | 30 | 4 | Friend | 105 |
Table POST_REACTIONS
| column | type | constraints |
|---|---|---|
| post_id | INTEGER | NOT NULL |
| viewer_id | INTEGER | NOT NULL |
| post_action | VARCHAR(20) | NOT NULL |
| ds | DATE | NOT NULL |
Sample data:
| ds | post_action | post_id | viewer_id |
|---|---|---|---|
| 2023-08-10 | like | 1 | 101 |
| 2023-08-10 | comment | 1 | 102 |
| 2023-08-11 | reshare | 2 | 103 |
| 2023-08-11 | like | 3 | 104 |
| 2023-08-11 | comment | 3 | 104 |
Expected output
Single row counting posts whose total unconnected watch time exceeded 60 seconds between 2023-08-06 and 2023-08-12.
| metric | relationship | value |
|---|---|---|
| posts_with_unconnected_watch_time_gt_60s | All | 1 |
Hints
- Filter to unconnected viewers and aggregate duration per post.
- Use HAVING SUM(duration) > 60 over the fixed range 2023-08-06 to 2023-08-12.
Avg reactions per post by relationship
Compute the average number of reactions per post coming from friends and from unconnected viewers over the fixed window 2023-08-06 to 2023-08-12. Consider all posts that had any views in this window, count zero reactions where applicable, and classify reactions by the viewer's relationship on the same post and day.
Table INFO_STREAM_VIEWS
| column | type | constraints |
|---|---|---|
| post_id | INTEGER | NOT NULL |
| viewer_id | INTEGER | NOT NULL |
| relationship | VARCHAR(20) | NOT NULL |
| duration | DECIMAL(10,1) | NOT NULL |
| ds | DATE | NOT NULL |
Sample data:
| ds | duration | post_id | relationship | viewer_id |
|---|---|---|---|---|
| 2023-08-10 | 75.2 | 1 | Friend | 101 |
| 2023-08-10 | 45 | 1 | Unconnected | 102 |
| 2023-08-11 | 120.5 | 2 | Followee | 103 |
| 2023-08-11 | 65 | 3 | Unconnected | 104 |
| 2023-08-12 | 30 | 4 | Friend | 105 |
Table POST_REACTIONS
| column | type | constraints |
|---|---|---|
| post_id | INTEGER | NOT NULL |
| viewer_id | INTEGER | NOT NULL |
| post_action | VARCHAR(20) | NOT NULL |
| ds | DATE | NOT NULL |
Sample data:
| ds | post_action | post_id | viewer_id |
|---|---|---|---|
| 2023-08-10 | like | 1 | 101 |
| 2023-08-10 | comment | 1 | 102 |
| 2023-08-11 | reshare | 2 | 103 |
| 2023-08-11 | like | 3 | 104 |
| 2023-08-11 | comment | 3 | 104 |
Expected output
Two rows with the average number of reactions per post by relationship (Friend and Unconnected) between 2023-08-06 and 2023-08-12. Posts with no reactions in a relationship count as zero in the average.
| avg_reactions_per_post | relationship |
|---|---|
| 0.25 | Friend |
| 0.75 | Unconnected |
Hints
- Join reactions to views on post_id, viewer_id, and ds to infer each viewer's relationship.
- Build a post-by-relationship grid so posts with zero reactions still contribute 0 to the average before aggregating.