Coding questions · Meta
Analyze Social Media Engagement with SQL Queries
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.
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.
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.
Table info_stream_views
| column | type | constraints |
|---|---|---|
| view_id | INTEGER | PRIMARY KEY, NOT NULL |
| post_id | INTEGER | NOT NULL |
| viewer_id | INTEGER | NOT NULL |
| creator_connection_type | VARCHAR(20) | NOT NULL |
| view_duration_seconds | INTEGER | NOT NULL |
| view_date | DATE | NOT NULL |
Sample data:
| creator_connection_type | post_id | view_date | view_duration_seconds | view_id | viewer_id |
|---|---|---|---|---|---|
| friend | 9001 | 2025-05-27 | 15 | 101 | 501 |
| unconnected | 9001 | 2025-05-27 | 25 | 102 | 777 |
| unconnected | 9002 | 2025-05-28 | 40 | 103 | 502 |
| friend | 9003 | 2025-05-29 | 10 | 104 | 501 |
| unconnected | 9002 | 2025-05-29 | 30 | 105 | 888 |
Expected output
Number of distinct post_id with more than 60 seconds of total view_duration_seconds from unconnected viewers between 2025-05-26 and 2025-06-01 (inclusive), using the sample data.
| distinct_post_count |
|---|
| 1 |
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.
Table info_stream_views
| column | type | constraints |
|---|---|---|
| view_id | INTEGER | PRIMARY KEY, NOT NULL |
| post_id | INTEGER | NOT NULL |
| viewer_id | INTEGER | NOT NULL |
| creator_connection_type | VARCHAR(20) | NOT NULL |
| view_duration_seconds | INTEGER | NOT NULL |
| view_date | DATE | NOT NULL |
Sample data:
| creator_connection_type | post_id | view_date | view_duration_seconds | view_id | viewer_id |
|---|---|---|---|---|---|
| friend | 9001 | 2025-05-27 | 15 | 101 | 501 |
| unconnected | 9001 | 2025-05-27 | 25 | 102 | 777 |
| unconnected | 9002 | 2025-05-28 | 40 | 103 | 502 |
| friend | 9003 | 2025-05-29 | 10 | 104 | 501 |
| unconnected | 9002 | 2025-05-29 | 30 | 105 | 888 |
Table post_action
| column | type | constraints |
|---|---|---|
| action_id | INTEGER | PRIMARY KEY, NOT NULL |
| post_id | INTEGER | NOT NULL |
| action_type | VARCHAR(20) | NOT NULL |
| actor_id | INTEGER | NOT NULL |
| action_date | DATE | NOT NULL |
Sample data:
| action_date | action_id | action_type | actor_id | post_id |
|---|---|---|---|---|
| 2025-05-27 | 5001 | react | 501 | 9001 |
| 2025-05-28 | 5002 | react | 777 | 9002 |
| 2025-05-28 | 5003 | comment | 888 | 9002 |
| 2025-05-29 | 5004 | react | 502 | 9003 |
| 2025-05-29 | 5005 | share | 501 | 9001 |
Expected output
Average number of 'react' actions per creator_connection_type across posts that had views between 2025-05-26 and 2025-06-01 (inclusive), using distinct (post_id, creator_connection_type) pairs from the views table.
| avg_total_reacts | creator_connection_type |
|---|---|
| 1 | friend |
| 1 | unconnected |
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.