Coding questions · Meta

Compute percent of active users with 50+ calls

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.

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.)

Percent of today's active GB users with >50 calls in the prior 7 days

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:

Active in GB (country = 'gb' and is_active = 1) on 2025-06-01, and
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).

Table `call_logs`

column	type	constraints
call_id	BIGINT	PRIMARY KEY, NOT NULL
caller_id	VARCHAR(20)	NOT NULL
receiver_id	VARCHAR(20)	NOT NULL
call_date	VARCHAR(10)	NOT NULL
duration_seconds	INT	NOT NULL

Sample data:

call_date	call_id	caller_id	duration_seconds	receiver_id
2025-05-25	1	u1	60	u5
2025-05-25	2	u6	120	u1
2025-05-25	3	u1	45	u7
2025-05-25	4	u8	30	u1
2025-05-25	5	u1	200	u9
2025-05-25	6	u5	90	u1
2025-05-25	7	u1	75	u6
2025-05-25	8	u7	110	u1
2025-05-26	9	u1	55	u5
2025-05-26	10	u6	80	u1
2025-05-26	11	u1	40	u7
2025-05-26	12	u8	35	u1
2025-05-26	13	u1	140	u9
2025-05-26	14	u5	65	u1
2025-05-26	15	u1	95	u6
2025-05-26	16	u7	105	u1
2025-05-27	17	u1	70	u5
2025-05-27	18	u6	90	u1
2025-05-27	19	u1	50	u7
2025-05-27	20	u8	60	u1
2025-05-27	21	u1	210	u9
2025-05-27	22	u5	85	u1
2025-05-27	23	u1	100	u6
2025-05-27	24	u7	115	u1
2025-05-28	25	u1	65	u5
2025-05-28	26	u6	75	u1
2025-05-28	27	u1	55	u7
2025-05-28	28	u8	25	u1
2025-05-28	29	u1	160	u9
2025-05-28	30	u5	95	u1
2025-05-28	31	u1	85	u6
2025-05-28	32	u7	125	u1
2025-05-29	33	u1	62	u5
2025-05-29	34	u6	82	u1
2025-05-29	35	u1	42	u7
2025-05-29	36	u8	38	u1
2025-05-29	37	u1	180	u9
2025-05-29	38	u5	88	u1
2025-05-29	39	u1	92	u6
2025-05-29	40	u7	112	u1
2025-05-30	41	u1	58	u5
2025-05-30	42	u6	78	u1
2025-05-30	43	u1	48	u7
2025-05-30	44	u8	28	u1
2025-05-30	45	u1	155	u9
2025-05-30	46	u5	98	u1
2025-05-30	47	u1	83	u6
2025-05-30	48	u7	118	u1
2025-05-31	49	u1	66	u5
2025-05-31	50	u6	86	u1
2025-05-31	51	u1	52	u7
2025-06-01	52	u1	60	u5
2025-05-24	53	u5	60	u1

Table `user_daily_status`

column	type	constraints
user_id	VARCHAR(20)	NOT NULL
country	VARCHAR(2)	NOT NULL
is_active	INT	NOT NULL
status_date	VARCHAR(10)	NOT NULL

Sample data:

country	is_active	status_date	user_id
gb	1	2025-06-01	u1
gb	1	2025-06-01	u2
gb	0	2025-06-01	u3
us	1	2025-06-01	u4
gb	1	2025-05-31	u1

Expected output

A single-row result showing the percent of today's (2025-06-01) active GB users who made/received >50 calls between 2025-05-25 and 2025-05-31 inclusive.

percent_active_users_with_50_plus_calls_last_7_days
50

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.
Use a LEFT JOIN so active users with zero calls are still included in the denominator.