Calculate French User Engagement and U.S. Call Duration

call_logs

+---------+-----------+------------+---------+----------+
| 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     |
+---------+-----------+------------+---------+----------+

user_profile

+---------+-----------+---------+------------+----------+------------+
| 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     | FR      | iOS        | 0        | 2019-01-01 |
+---------+-----------+---------+------------+----------+------------+

Scenario

A video-calling app wants to monitor user engagement. Analysts must compute (1) what proportion of French users placed at least one video call yesterday, and (2) today’s average total call duration per U.S. daily-active user.

Question

Write an SQL query to return the percentage of users whose country = 'France' that made or received at least one call yesterday.

Write an SQL query to return total call-time divided by count of U.S. DAU for today.

Hints

Join call_logs to user_profile twice (caller, recipient). Filter by ds with DATE_SUB/CURRENT_DATE. Use DISTINCT users to avoid double-counting, then aggregate.

French Engagement and Avg U.S. DAU Call Duration on 2019-01-01

Using the tables call_logs and user_profile, compute both of the following metrics for 2019-01-01:

1. pct_fr_users_engaged: the percentage (0–100) of users with country = 'FR' who made or received at least one call on 2019-01-01. Use all users from user_profile where ds = '2019-01-01' and country = 'FR' as the denominator, and consider a user engaged if they appear as either caller or recipient in call_logs on that date.

2. avg_call_duration_per_us_dau: the average total call duration per U.S. daily-active user (DAU) on 2019-01-01. Consider users in user_profile where ds = '2019-01-01', country = 'US', and dau_flag = 1 as the U.S. DAU set. For each such user, sum the durations of all calls they participated in that day (as caller or recipient), treating users with zero calls as having total duration 0. Then take the average of these per-user totals.

Return a single row with two columns: pct_fr_users_engaged and avg_call_duration_per_us_dau.

Table call_logs

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

Sample data:

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

Table user_profile

column	type	constraints
user_id	VARCHAR(20)	NOT NULL
age_bucket	VARCHAR(10)	NOT NULL
country	VARCHAR(2)	NOT NULL
primary_os	VARCHAR(20)	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	2019-01-01	iOS	123
35-44	FR	1	2019-01-01	Android	456
18-24	FR	0	2019-01-01	iOS	789

Expected output

Single-row result with two metrics for 2019-01-01: the percentage of French users (from the user_profile snapshot) who made or received at least one call, and the average total call duration across U.S. DAUs (counting non-participating DAUs as 0).

avg_call_duration_per_us_dau	pct_fr_users_engaged
864.4	100

Hints

1. Start from user_profile snapshots on 2019-01-01 and identify the FR users and the U.S. DAUs separately.
2. For French engagement, find which FR users appear in call_logs that day as either caller or recipient, and avoid double-counting users with multiple calls.
3. For the U.S. DAU metric, collect all calls where a U.S. DAU is caller or recipient, sum durations per user (including 0 for users with no calls), then average those per-user totals.
4. You can compute each metric in its own CTE and return both in a single final SELECT.