Analyze Recent Post Performance Using SQL Queries
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates SQL-based data manipulation skills including joins, aggregation, temporal filtering, and audience segmentation to calculate viewing-time and reaction metrics.
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.
Tables
INFO_STREAM_VIEWS(post_id INTEGER, viewer_id INTEGER, relationship VARCHAR(20), duration DECIMAL(10,1), ds DATE)
POST_REACTIONS(post_id INTEGER, viewer_id INTEGER, post_action VARCHAR(20), ds DATE)
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.
Tables
INFO_STREAM_VIEWS(post_id INTEGER, viewer_id INTEGER, relationship VARCHAR(20), duration DECIMAL(10,1), ds DATE)
POST_REACTIONS(post_id INTEGER, viewer_id INTEGER, post_action VARCHAR(20), ds DATE)
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.
Community answers
Answer by SS
Do we really need that many CTEs?