Analyze User Engagement Metrics for Video-Calling App

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     | France  | Android    | 1        | 2019-01-01 |
| 789     | 25-34     | US      | iOS        | 0        | 2019-01-01 |
+---------+-----------+---------+------------+----------+------------+

Scenario

You work for a video-calling app and need to report user engagement metrics for different geographies.

Question

What percentage of users whose country = 'France' were on at least one video call yesterday?

What is the total video-call duration divided by the number of daily active users (DAU) in the United States today?

Hints

Join the calls table with the user profile table; use DISTINCT user counts, date filters like DATE(ds)=CURRENT_DATE-1 or CURRENT_DATE, and aggregate durations in seconds/minutes before dividing.

France on-call percentage and US duration per DAU (fixed dates)

Using the calls and users tables, compute two engagement metrics for specific dates:

1. For 2025-05-31, calculate the percentage of users whose country = 'France' that participated in at least one call on that date. Treat both caller and recipient as participants. Return this as a row with:

   • metric_name = 'fr_on_call_pct_yesterday'
   • metric_date = '2025-05-31'
   • metric_value = percentage of French users on at least one call (rounded to 2 decimals).

2. For 2025-06-01, compute the total person-level call duration for users in the United States on that date divided by the number of US daily active users (dau_flag = 1) on that same date. Again, treat both caller and recipient as participants. Return this as a row with:

   • metric_name = 'us_avg_call_duration_per_dau_today'
   • metric_date = '2025-06-01'
   • metric_value = total US person-level call duration / number of US DAUs (rounded to 2 decimals).

Your query should return exactly two rows (one for each metric) with columns: metric_name, metric_date, metric_value.

Table 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	123	2025-05-31	864.4	456
9395	032	2025-05-31	263.7	789
0879	456	2025-05-31	22	032
5001	123	2025-06-01	600	456
5002	456	2025-06-01	300	789

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	France	1	2025-05-31	Android	456
25-34	US	0	2025-05-31	iOS	789
25-34	US	1	2025-06-01	iOS	123
35-44	France	1	2025-06-01	Android	456
25-34	US	1	2025-06-01	iOS	789

Expected output

Two rows: (1) percentage of French users who were on at least one call on 2025-05-31, and (2) total person-level call duration for US users on 2025-06-01 divided by the number of US DAUs on 2025-06-01. Based on the sample data, all French users (only user 456) were on a call on 2025-05-31, and US users on 2025-06-01 (123 and 789) accumulated 900 seconds of person-level call duration over 2 DAUs.

metric_date	metric_name	metric_value
2025-05-31	fr_on_call_pct_yesterday	100
2025-06-01	us_avg_call_duration_per_dau_today	450

Hints

1. Treat both caller and recipient as participants by UNION ALL-ing them into a single call_participants table.
2. Filter by explicit dates: use ds = DATE '2025-05-31' for the France percentage and ds = DATE '2025-06-01' for the US duration metric.
3. For the France metric, compute distinct French users on calls divided by total French users; for the US metric, sum durations for US participants and divide by the count of US users with dau_flag = 1.
4. Use NULLIF in denominators to avoid divide-by-zero errors and ROUND(...) to 2 decimal places.