Count Recent High-Volume Call Users
Quick Overview
This question evaluates SQL data manipulation and analytical querying skills, including time-window filtering, joins between user and call tables, role-aware event counting (caller vs recipient), distinct user aggregation, and cohort percentage calculations.
Meta
Mar 14, 2026, 12:00 AM
Data Scientist
Technical Screen
Data Manipulation (SQL/Python)
4
0
Loading...
Given the following tables:
users
- user_id BIGINT
- country STRING
- is_active BOOLEAN
calls
- call_id BIGINT
- initiated_at TIMESTAMP
- caller_id BIGINT
- recipient_id BIGINT
- call_type STRING -- values: 'audio' or 'video'
- status STRING -- values such as 'completed', 'missed', 'failed'
Assumptions:
- users.user_id joins to calls.caller_id and calls.recipient_id.
- All timestamps are stored in UTC.
- 'Last 7 days' means [analysis_ts - INTERVAL '7' DAY, analysis_ts).
- Only completed calls should be counted.
- For the second task, a user's video call count should include calls where the user was either the caller or the recipient.
Write SQL for the following two tasks:
- Return the number of distinct users who initiated more than 10 calls in the last 7 days.
- Return the percentage of active users from Great Britain (country = 'gb') who participated in more than 50 video calls in the last 7 days.
Required outputs:
- Query 1: initiated_call_users_over_10
- Query 2: pct_active_gb_users_over_50_video_calls