Count heavy callers in 7 days
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given two tables.
`users`
- `user_id` BIGINT PRIMARY KEY
- `country_code` STRING
`calls`
- `call_id` BIGINT PRIMARY KEY
- `caller_id` BIGINT
- `recipient_id` BIGINT
- `call_type` STRING — values include `audio` and `video`
- `initiated_at` TIMESTAMP
- `duration_seconds` INT
Assume each row in `calls` represents one completed call session. Both `caller_id` and `recipient_id` reference `users.user_id`. Use UTC for all timestamps. Interpret `last 7 days` as `initiated_at >= CURRENT_TIMESTAMP - INTERVAL '7' DAY` and `initiated_at < CURRENT_TIMESTAMP`.
Write SQL for both tasks:
1. Count the number of distinct users who initiated more than 10 calls in the last 7 days.
- Required output column: `initiators_over_10_calls`
2. Among users in Great Britain (`country_code = 'gb'`), compute the percentage of active users who had more than 50 video calls in the last 7 days, counting both calls they placed and calls they received.
- Define an active GB user as a GB user with at least one call of any type in the last 7 days, either as caller or recipient.
- Required output columns: `active_gb_users`, `gb_users_over_50_video_calls`, `pct_gb_active_users_over_50_video_calls`
Quick Answer: This question evaluates proficiency in SQL-based data manipulation and analytics, covering joins between user and call records, aggregation and distinct counting, temporal filtering, and conditional metrics within the Data Manipulation (SQL/Python) domain.