Identify Users Interested in Group Video Calls
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
video_calls
caller | recipient | ds | call_id | duration
u1 | u2 | 2023-09-01| c100 | 320
u3 | u4 | 2023-09-01| c101 | 0
u2 | u1 | 2023-09-02| c102 | 180
dim_all_users
user_id | age_bucket | country | primary_os | dau_flag | ds
u1 | 25-34 | US | iOS | 1 | 2023-09-01
u2 | 35-44 | IN | Android | 1 | 2023-09-01
u3 | 18-24 | US | Android | 0 | 2023-09-01
##### Scenario
Using call-level logs and user attributes, estimate user interest in group video calls.
##### Question
Write SQL to identify users who appear interested in group video calls using only the tables below.
##### Hints
Derive number of unique recipients per caller per day and filter callers exceeding a threshold.
Overview: This question evaluates a data scientist's ability to perform SQL-based event-level data manipulation, aggregation, joins, and user-level feature derivation from call logs and user attribute tables.
Using the call-level logs and user attributes, write SQL to identify users who appear interested in group video calls. Define an interested user as a caller who, on a given day (ds), contacted at least 2 unique recipients. Return one row per (user, ds) with the user's attributes and the number of unique recipients.
Tables
video_calls(caller VARCHAR(50), recipient VARCHAR(50), ds DATE, call_id VARCHAR(50), duration INTEGER)
dim_all_users(user_id VARCHAR(50), age_bucket VARCHAR(10), country VARCHAR(2), primary_os VARCHAR(20), dau_flag INTEGER, ds DATE)
Hints
- First aggregate the call logs by caller and ds to count distinct recipients.
- Use COUNT(DISTINCT recipient) grouped by caller and ds; filter where this count is at least 2.