Write SQL for video-call recipients and FR activity
Overview
This question evaluates a candidate's ability to author ANSI-SQL for time-windowed aggregations, distinct recipient counts, tie-breaking sorts, joins with user tables, deduplication of users, and percentage calculations on activity data.
Given the schema and samples below, write ANSI‑SQL to answer both questions. Assume dates are stored in UTC. Today is 2025-09-01, so “yesterday” is 2025-08-31 and the “last 7 days” window is 2025-08-25 through 2025-08-31 inclusive (exclude 2025-09-01).
Tables
- video_calls(date DATE, caller_id STRING, recipient_id STRING, call_id BIGINT, duration_sec INT)
- daily_users(date DATE, user_id STRING, country STRING, dau_flag TINYINT)
Small ASCII samples (not exhaustive) video_calls
| date | caller_id | recipient_id | call_id | duration_sec |
|---|---|---|---|---|
| 2025-08-25 | U1 | U2 | 1 | 300 |
| 2025-08-25 | U1 | U3 | 2 | 120 |
| 2025-08-25 | U2 | U1 | 3 | 60 |
| 2025-08-26 | U1 | U2 | 4 | 200 |
| 2025-08-27 | U3 | U4 | 5 | 400 |
| 2025-08-27 | U1 | U4 | 6 | 180 |
| 2025-08-30 | U5 | U1 | 7 | 240 |
| 2025-08-30 | U1 | U5 | 8 | 60 |
| 2025-08-31 | U2 | U5 | 9 | 300 |
| 2025-08-31 | U4 | U1 | 10 | 100 |
| 2025-08-31 | U6 | U6 | 11 | 30 |
| 2025-09-01 | U1 | U2 | 12 | 90 |
daily_users
| date | user_id | country | dau_flag |
|---|---|---|---|
| 2025-08-31 | U1 | FR | 1 |
| 2025-08-31 | U2 | FR | 1 |
| 2025-08-31 | U3 | FR | 0 |
| 2025-08-31 | U4 | US | 1 |
| 2025-08-31 | U5 | FR | 1 |
| 2025-08-31 | U6 | FR | 1 |
| 2025-09-01 | U1 | FR | 1 |
| 2025-09-01 | U2 | FR | 1 |
Q1) Find the top 10 callers by the number of distinct recipients they called in the last 7 days (2025-08-25..2025-08-31). Exclude self‑calls (caller_id = recipient_id). Break ties by total calls in the window, then by caller_id ascending. Return: caller_id, distinct_recipient_count, total_calls.
Q2) What percentage of active daily users in France were on at least one call yesterday (2025-08-31), counting users who either called or received? Numerator: distinct users in FR with dau_flag = 1 on 2025-08-31 who appear as caller or recipient in video_calls on 2025-08-31. Denominator: distinct users with dau_flag = 1 and country = 'FR' in daily_users on 2025-08-31. Return: numerator, denominator, pct_active_on_call. Ensure no double‑counting of users who both called and received.