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.
Quick Answer: This question evaluates SQL-based data manipulation skills including joins, aggregation, temporal filtering, and audience segmentation to calculate viewing-time and reaction metrics.