Percentage of French Daily Active Users Who Were on a Video Call Yesterday

A product logs one-to-one video calls and keeps a daily snapshot of every user. Write a query that returns the percentage of daily active users from France (country code `'fr'`) who were on a video call yesterday. ### Input Table(s) **`video_calls`**: one row per call. Every call is between exactly two people. | Column | Type | Meaning | | --- | --- | --- | | `caller` | BIGINT | ID of the user who made the call | | `recipient` | BIGINT | ID of the user who was called | | `ds` | TEXT | Date of the call, formatted `YYYY-MM-DD` | | `call_id` | BIGINT | ID of the call; primary key, unique per call | | `duration` | DOUBLE PRECISION | Length of the call in seconds | **`dim_all_users`**: one row per user per date. The pair (`user_id`, `ds`) is unique. | Column | Type | Meaning | | --- | --- | --- | | `user_id` | BIGINT | ID of the user | | `age_bucket` | TEXT | Age category of the user, such as `'25-34'` | | `country` | TEXT | Lowercase two-letter code of the country where the user is based, such as `'fr'` | | `dau_flag` | SMALLINT | 1 if the user was a daily active user on `ds`, else 0 | | `ds` | TEXT | Date of the record, formatted `YYYY-MM-DD` | ### Output Contract - Write one read-only PostgreSQL query: a single `SELECT`, which may use CTEs. - Return exactly one row with one column: | Column | Type | Meaning | | --- | --- | --- | | `pct_on_video_call` | NUMERIC | 100 times the number of French daily active users yesterday who were on at least one call yesterday, divided by the number of French daily active users yesterday, rounded to two decimal places | - The result always has exactly one row, so it needs no `ORDER BY`. ### Example **`dim_all_users`** | user_id | age_bucket | country | dau_flag | ds | | --- | --- | --- | --- | --- | | 1 | 25-34 | fr | 1 | 2019-07-01 | | 1 | 25-34 | fr | 1 | 2019-06-30 | | 2 | 18-24 | fr | 1 | 2019-07-01 | | 3 | 35-44 | fr | 1 | 2019-07-01 | | 4 | 45-54 | fr | 1 | 2019-07-01 | | 5 | 13-17 | fr | 0 | 2019-07-01 | | 6 | 25-34 | us | 1 | 2019-07-01 | | 7 | 65+ | fr | 1 | 2019-07-01 | | 8 | 25-34 | fr | 1 | 2019-06-30 | **`video_calls`** | caller | recipient | ds | call_id | duration | | --- | --- | --- | --- | --- | | 1 | 6 | 2019-07-01 | 11 | 300.0 | | 6 | 2 | 2019-07-01 | 12 | 50.0 | | 1 | 2 | 2019-07-01 | 13 | 20.0 | | 5 | 9 | 2019-07-01 | 14 | 15.0 | | 3 | 8 | 2019-06-30 | 15 | 60.0 | | 4 | 8 | 2019-07-02 | 16 | 40.0 | | 8 | 7 | 2019-07-01 | 17 | 25.0 | **Expected result** | pct_on_video_call | | --- | | 60.00 | - French daily active users on 2019-07-01 are users 1, 2, 3, 4 and 7: five users. User 5 is French but has `dau_flag` 0, user 6 is based in the US, and user 8 has a row only for 2019-06-30. - Of those five, user 1 made calls 11 and 13, user 2 received calls 12 and 13, and user 7 received call 17: three users, each counted once. User 3's only call is dated 2019-06-30 and user 4's is dated 2019-07-02, so neither counts. - `100 * 3 / 5 = 60.00`. ### Constraints and Clarifications - **Reference date.** Treat today as 2019-07-02, so yesterday is 2019-07-01. This fixed date replaces the current date so the answer is deterministic; do not use `CURRENT_DATE` or `NOW()`. - **French daily active user yesterday:** a `dim_all_users` row with `ds` equal to `'2019-07-01'`, `country` equal to `'fr'` and `dau_flag` equal to 1. Only that day's row decides a user's country and activity; rows for other dates are ignored. Country codes are stored in lowercase exactly as shown, and a NULL `country` is not France. - **On a video call yesterday:** the user appears as `caller` or as `recipient` in at least one `video_calls` row with `ds` equal to `'2019-07-01'`, whatever the call's `duration`. - Count people, not calls: a user on several calls, or both a caller and a recipient, counts once. - The other person on a call does not need to be French, a daily active user, or present in `dim_all_users`. Users who were on a call yesterday but are not French daily active users yesterday affect neither the numerator nor the denominator. - In `dim_all_users`, `user_id`, `dau_flag` and `ds` are never NULL; `age_bucket` and `country` may be NULL. In `video_calls`, `caller`, `recipient`, `ds` and `call_id` are never NULL, and `caller` never equals `recipient`. Every `ds` is a valid date in `YYYY-MM-DD` form. - The percentage is the exact ratio, rounded only at the end to two decimal places, with halves rounded away from zero as `ROUND` does on a `NUMERIC`. For example, 1 of 3 users gives 33.33 and 2 of 3 gives 66.67. - If there are no French daily active users yesterday, return one row with `pct_on_video_call` NULL. ```hint Start from the population Every user in the denominator comes from a single day of the user table; settle who belongs there before looking at any calls. ```