Calculate Video Call Usage Metrics by Country and Date

video_calls

+---------+-----------+------------+---------+----------+
| caller  | recipient | ds         | call_id | duration |
+---------+-----------+------------+---------+----------+
| 123     | 456       | 2019-01-01 | 4325    | 864.4    |
| 032     | 789       | 2019-01-01 | 9395    | 263.7    |
| 456     | 032       | 2019-01-01 | 0879    |  22.0    |
+---------+-----------+------------+---------+----------+

users

+---------+------------+---------+-------------+----------+------------+
| user_id | age_bucket | country | primary_os  | dau_flag | ds         |
+---------+------------+---------+-------------+----------+------------+
| 123     | 25-34      | US      | iOS         | 1        | 2019-01-01 |
| 456     | 35-44      | FR      | Android     | 1        | 2019-01-01 |
| 789     | 18-24      | US      | Android     | 0        | 2019-01-01 |
+---------+------------+---------+-------------+----------+------------+

Scenario

Video-calling platform wants usage KPIs by country and date.

Question

What percentage of users located in France made at least one video call yesterday? What is the average time spent on calls per daily active user (DAU) in the United States today?

Hints

Join call and user tables, filter by ds for yesterday/today, count distinct users, sum durations, divide for ratios or averages.

France Callers and US DAU Call Time Metrics

A video-calling platform wants usage KPIs by country and date.

Using the ds column as the date partition, compute both of the following metrics:

1. For 2025-05-31, the percentage of users located in France who initiated at least one video call on that date.
2. For 2025-06-01, the average total time spent on video calls per daily active user (DAU) in the United States on that date. DAUs are users with dau_flag = 1, and call time should include calls they placed or received.

Return one row per metric with the columns: metric, ds, country, value.

• For the France metric, use metric = 'pct_fr_users_made_call_yesterday' and country = 'FR'.
• For the US metric, use metric = 'avg_call_time_per_dau_us_today' and country = 'US'.
• Round value to 1 decimal place.

Table video_calls

column	type	constraints
caller	VARCHAR	NOT NULL
recipient	VARCHAR	NOT NULL
ds	DATE	NOT NULL
call_id	VARCHAR	PRIMARY KEY, NOT NULL
duration	DECIMAL(10,1)	NOT NULL

Sample data:

call_id	caller	ds	duration	recipient
4325_20250531	123	2025-05-31	864.4	456
9395_20250531	032	2025-05-31	263.7	789
0879_20250531	456	2025-05-31	22	032
4325_20250601	123	2025-06-01	864.4	456
9395_20250601	032	2025-06-01	263.7	789
0879_20250601	456	2025-06-01	22	032

Table users

column	type	constraints
user_id	VARCHAR	NOT NULL
age_bucket	VARCHAR	NOT NULL
country	VARCHAR	NOT NULL
primary_os	VARCHAR	NOT NULL
dau_flag	INTEGER	NOT NULL
ds	DATE	NOT NULL

Sample data:

age_bucket	country	dau_flag	ds	primary_os	user_id
25-34	US	1	2025-05-31	iOS	123
35-44	FR	1	2025-05-31	Android	456
18-24	US	0	2025-05-31	Android	789
25-34	US	1	2025-06-01	iOS	123
35-44	FR	1	2025-06-01	Android	456
18-24	US	0	2025-06-01	Android	789

Expected output

Two rows: one with the percentage of France users who initiated at least one video call on 2025-05-31, and one with the average total call duration per US DAU on 2025-06-01. Each row has columns metric, ds, country, value.

country	ds	metric	value
FR	2025-05-31	pct_fr_users_made_call_yesterday	100
US	2025-06-01	avg_call_time_per_dau_us_today	864.4

Hints

1. Use users.ds snapshots for 2025-05-31 (France) and 2025-06-01 (US) when filtering.
2. For the France metric, the denominator is distinct FR users on 2025-05-31; the numerator is distinct FR callers on that date.
3. For the US metric, identify US DAUs (dau_flag = 1) and sum durations for calls where they are caller or recipient, then average total seconds per DAU.