Compute Heavy-Caller Percentages
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: hard
Interview Round: Technical Screen
You are given two tables that track voice calls and daily active users for a messaging app.
**Table: `call_events`**
- `call_id` BIGINT — unique call identifier
- `event_time` TIMESTAMP — time when the call started, stored in UTC
- `caller_user_id` BIGINT — user who initiated the call
- `recipient_user_id` BIGINT — user who received the call
- `country_code` STRING — country of the call session, using ISO-style country codes such as `GB`
**Table: `daily_active_users`**
- `activity_date` DATE — date of user activity in UTC
- `user_id` BIGINT — active user identifier
- `country_code` STRING — user country on that date
Assume:
- The analysis date is the most recent date available in `daily_active_users`.
- The phrase **"last 7 days"** means the 7-day window ending on the analysis date, inclusive.
- For call-volume calculations, each row in `call_events` represents one completed call attempt.
- For question 2, a user's total call count includes both roles: calls made as caller and calls received as recipient.
- `GB` refers to users in Great Britain.
Write SQL for the following two tasks:
1. Compute the **percentage of distinct callers** who made **at least 20 calls** in the last 7 days.
- A "caller" is defined using `caller_user_id` only.
- Output columns:
- `analysis_date` DATE
- `pct_callers_ge_20` DOUBLE
2. Compute the **percentage of GB daily active users** on the analysis date who had **at least 50 total calls** in the last 7 days.
- Total calls = calls as `caller_user_id` + calls as `recipient_user_id`.
- The denominator should be GB users who are active on the analysis date in `daily_active_users`.
- Output columns:
- `analysis_date` DATE
- `pct_gb_dau_ge_50_calls` DOUBLE
Overview: This question evaluates proficiency in time-windowed data aggregation, user-level counting and percentage calculations using SQL or Python for analytics tasks.
Community answers
Answer by melodyyun.z
Task 1
with a_date as (
select max(activity_date) as analysis_date
from daily_active_users
)
, callers as (
select c.caller_user_id
, count(c.call_id) as call_count
from call_events c
join a_date a
on date(c.event_time) between a.analysis_date - INTERVAL '6 DAYS' and a.analysis_date
group by 1
)
, metrics1 as (
select a.analysis_date
, count(c.caller_user_id) as total_caller_count
, sum(case when c.call_count >= 20 then 1 else 0 end) as heavy_caller_count
from callers c
join a_date a
on 1=1
group by 1
)
select analysis_date
, heavy_caller_count/nullif(total_caller_count, 0)::double as pct_callers_ge_20
from metrics1
Task 2
with a_date as (
select max(activity_date) as analysis_date
from daily_active_users
)
, participants as (
select c.call_id
, c.caller_user_id as participant_id
from call_events c
join a_date a
on date(c.event_time) between a.analysis_date - INTERVAL '6 DAYS' and a.analysis_date
union all
select c.call_id
, c.recipient_user_id as participant_id
from call_events c
join a_date a
on date(c.event_time) between a.analysis_date - INTERVAL '6 DAYS' and a.analysis_date
)
, active_users as (
select distinct u.user_id
, a.analysis_date
from daily_active_users u
join a_date a
on 1 = 1
where u.activity_date = a.analysis_date
and u.country_code = 'GB'
)
, calls as (
select au.analysis_date
, au.user_id
, count(p.call_id) as call_count
from participants p
right join active_users au
on p.participant_id = au.user_id
group by 1, 2
)
, metrics2 as (
select analysis_date
, cou