Analyze Social Media Engagement with SQL Queries
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
info_stream_views
+----------+---------+-----------+-------------------------+-----------------------+------------+
| view_id | post_id | viewer_id | creator_connection_type | view_duration_seconds | view_date |
+----------+---------+-----------+-------------------------+-----------------------+------------+
| 101 | 9001 | 501 | friend | 15 | 2023-09-18 |
| 102 | 9001 | 777 | unconnected | 25 | 2023-09-18 |
| 103 | 9002 | 502 | unconnected | 40 | 2023-09-19 |
| 104 | 9003 | 501 | friend | 10 | 2023-09-20 |
| 105 | 9002 | 888 | unconnected | 30 | 2023-09-20 |
+----------+---------+-----------+-------------------------+-----------------------+------------+
post_action
+-----------+---------+-------------+----------+-------------+
| action_id | post_id | action_type | actor_id | action_date |
+-----------+---------+-------------+----------+-------------+
| 5001 | 9001 | react | 501 | 2023-09-18 |
| 5002 | 9002 | react | 777 | 2023-09-19 |
| 5003 | 9002 | comment | 888 | 2023-09-19 |
| 5004 | 9003 | react | 502 | 2023-09-20 |
| 5005 | 9001 | share | 501 | 2023-09-18 |
+-----------+---------+-------------+----------+-------------+
##### Scenario
A social-media analytics task using info_stream_views and post_action tables.
##### Question
Write a query to find the number of DISTINCT post_id that accumulated more than 60 seconds of total view_duration_seconds from unconnected viewers during the past 7 days. 2. Write a query to compute the average number of total reactions (action_type = 'react') received by
(a) friend-created posts and
(b) unconnected‐creator posts in the last 7 days.
##### Hints
Use date filtering on the last 7 days, group by post_id, and JOIN the two tables for reactions.
Overview: This question evaluates SQL data-manipulation and analytics competencies, including aggregation, DISTINCT counting, joins between view and action logs, date-range filtering, and conditional metric computation from event data.
Posts over 60s unconnected views
Count the number of distinct post_id where, between '2025-05-26' and '2025-06-01' (inclusive), the sum of view_duration_seconds from unconnected viewers (creator_connection_type = 'unconnected') exceeds 60 seconds.
Tables
info_stream_views(view_id INTEGER, post_id INTEGER, viewer_id INTEGER, creator_connection_type VARCHAR(20), view_duration_seconds INTEGER, view_date DATE)
Hints
- Filter to creator_connection_type = 'unconnected' and the specified date range.
- Aggregate total view_duration_seconds per post_id, then count how many totals exceed 60.
Average reacts by connection type
Compute the average total number of 'react' actions per creator_connection_type (e.g., 'friend', 'unconnected') for posts that had views between '2025-05-26' and '2025-06-01' (inclusive). Use distinct (post_id, creator_connection_type) pairs from info_stream_views to associate posts with connection types, and only count actions with action_type = 'react' that occurred in the same date range.
Tables
info_stream_views(view_id INTEGER, post_id INTEGER, viewer_id INTEGER, creator_connection_type VARCHAR(20), view_duration_seconds INTEGER, view_date DATE)
post_action(action_id INTEGER, post_id INTEGER, action_type VARCHAR(20), actor_id INTEGER, action_date DATE)
Hints
- First build the distinct (post_id, creator_connection_type) pairs from info_stream_views within the date range.
- Aggregate 'react' counts per post_id in post_action over the same date range, then join and average by creator_connection_type.