Compute percent of active users with 50+ calls
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
## Problem
You work on a Messenger-like app. You want to measure how many **active users in Great Britain (GB) today** have been heavy callers recently.
### Tables
Assume two tables (names can vary).
#### 1) `calls`
One row per call record.
- `call_id` (STRING, primary key)
- `caller_id` (STRING)
- `receiver_id` (STRING)
- `date` (STRING) — call date in string format (e.g., `'2025-11-06'`)
- `duration` (INT) — call duration (seconds)
#### 2) `user_daily_status`
One row per user per day describing user attributes and activity.
- `user_id` (STRING)
- `country` (STRING)
- `is_active` (INT) — `1` if the user is active on that date, else `0`
- `date` (STRING) — snapshot date in string format (e.g., `'2025-11-06'`)
### Definitions / assumptions
- “GB active users today” = users with `country = 'GB'`, `is_active = 1` on **today’s** snapshot date.
- “Previous seven days” = the 7-day lookback window **excluding today**, i.e. `[today - 7 days, today - 1 day]`.
- A user’s “call records” count includes both:
- outgoing calls where the user is the `caller_id`, and
- incoming calls where the user is the `receiver_id`.
- Count calls as number of call records (e.g., count of `call_id`).
## Task
Write a SQL query to compute:
- the **percentage** of today’s GB active users who had **more than 50** call records in the previous 7 days.
### Output
Return at least:
- `as_of_date`
- `active_users_today` (denominator)
- `active_users_gt_50_calls_last_7d` (numerator)
- `pct_active_users_gt_50_calls_last_7d`
(You may assume your SQL dialect provides a way to parse the date string into a date type.)
Overview: This question evaluates a data scientist's competence in Data Manipulation (SQL/Python), focusing on SQL aggregation, joining call and user snapshot tables, temporal filtering, and computing user-level engagement metrics across incoming and outgoing call records.
Read the full Meta Data Scientist interview experience this question came from
You are given call logs and a daily user status table for a Messenger-like app.
Define "today" as 2025-06-01.
Compute the percentage of users who are:
1) Active in GB (country = 'gb' and is_active = 1) on 2025-06-01, and
2) Have more than 50 call records in the previous 7 days window FROM 2025-05-25 TO 2025-05-31 (inclusive).
A call record counts for a user if the user appears as either the caller OR the receiver. Both outgoing and incoming calls should be counted.
Notes:
- Both tables store the date as a STRING in format 'YYYY-MM-DD'.
Return a single row with a single column: percent_active_users_with_50_plus_calls_last_7_days (as a percentage from 0 to 100, rounded to 2 decimals).
Tables
call_logs(call_id BIGINT, caller_id VARCHAR(20), receiver_id VARCHAR(20), call_date VARCHAR(10), duration_seconds INT)
user_daily_status(user_id VARCHAR(20), country VARCHAR(2), is_active INT, status_date VARCHAR(10))
Hints
- Filter the denominator to users who are active in GB on 2025-06-01.
- Count calls where the user is either caller or receiver in the date range 2025-05-25 through 2025-05-31.
Community answers
Answer by SS
WITH active_today AS (
SELECT DISTINCT user_id
FROM user_daily_status
WHERE country = 'GB'
AND date = CURRENT_DATE
AND is_active = 1
),
call_list AS (
SELECT call_id, caller_id AS user_id
FROM calls
WHERE date BETWEEN CURRENT_DATE - INTERVAL '7 days'
AND CURRENT_DATE - INTERVAL '1 day'
UNION ALL
SELECT call_id, receiver_id AS user_id
FROM calls
WHERE date BETWEEN CURRENT_DATE - INTERVAL '7 days'
AND CURRENT_DATE - INTERVAL '1 day'
),
user_call_counts AS (
SELECT user_id, COUNT(DISTINCT call_id) AS total_calls
FROM call_list
GROUP BY user_id
HAVING COUNT(DISTINCT call_id) > 50
)
SELECT
CURRENT_DATE AS as_of_date,
COUNT(DISTINCT a.user_id) AS active_users_today,
COUNT(DISTINCT c.user_id) AS active_users_gt_50_calls_last_7d,
ROUND(
COUNT(DISTINCT c.user_id) * 100.0 /
NULLIF(COUNT(DISTINCT a.user_id), 0),
2
) AS pct_active_users_gt_50_calls_last_7d
FROM active_today a
LEFT JOIN user_call_counts c ON a.user_id = c.user_id