Calculate Engagement Metrics for Info-Stream Content Analysis
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 |
+----------+-----------+--------------+----------+------------+
| 101 | 9001 | Friend | 85.4 | 2023-09-18 |
| 102 | 9002 | Unconnected | 63.0 | 2023-09-19 |
| 103 | 9003 | Followee | 12.0 | 2023-09-20 |
| 104 | 9004 | Unconnected | 70.1 | 2023-09-20 |
+----------+-----------+--------------+----------+------------+
post_reactions
+----------+-----------+-------------+------------+
| post_id | viewer_id | post_action | ds |
+----------+-----------+-------------+------------+
| 101 | 9001 | like | 2023-09-18 |
| 102 | 9002 | comment | 2023-09-19 |
| 102 | 9003 | like | 2023-09-19 |
| 104 | 9004 | reshare | 2023-09-20 |
+----------+-----------+-------------+------------+
##### Scenario
Analyst must write SQL/Python to understand engagement with info-stream content over the last week.
##### Question
Using the tables below, write a query to compute the number of DISTINCT posts that received more than 60 seconds of viewing time from unconnected audiences in the past 7 days. Write a second query that returns the average number of reactions per post coming from
(a) friends and
(b) unconnected viewers during the past 7 days.
##### Hints
Filter ds for last 7 days, group by relationship, count distinct post_id for first query, aggregate reactions and divide by distinct posts for second.
Overview: This question evaluates proficiency in data manipulation and analytics using SQL and Python, including aggregations, grouping, joins, and handling time-based event data to compute engagement metrics.
Given a table of user activity statuses by date, return for each user the earliest date they were recorded as active and the most recent date they were recorded as inactive.
Tables
activity(id INTEGER, date DATE, active_status VARCHAR(20))
Hints
- Use conditional aggregation with CASE expressions inside MIN and MAX.
- Group by id to compute dates per user.
Community answers
Answer by SS
Select id , Min(case when active_Status = 'active' then date else NULL end) as first_active_date , Max(case when active_status = 'inactive' then date else NULL end) as last_inactive_date from activitygroup by 1