Analyze Recent Calling Behavior in France Using SQL

CALLS

+---------+---------+---------------------+-------------------+----------+
| call_id | user_id | call_start_time     | participant_cnt   | is_video |
+---------+---------+---------------------+-------------------+----------+
| 201     | 1       | 2023-09-14 10:00:00 | 4                 | 1        |
| 202     | 2       | 2023-09-14 11:30:00 | 2                 | 1        |
| 203     | 3       | 2023-09-13 09:45:00 | 5                 | 0        |
| 204     | 1       | 2023-09-12 20:10:00 | 6                 | 1        |
| 205     | 4       | 2023-09-12 08:00:00 | 3                 | 1        |

USERS

+---------+--------------+
| user_id | country_code |
+---------+--------------+
| 1       | fr           |
| 2       | fr           |
| 3       | us           |
| 4       | fr           |
| 5       | fr           |

DAILY_ACTIVITY

+--------------+---------+
| activity_date| user_id |
+--------------+---------+
| 2023-09-14   | 1       |
| 2023-09-14   | 2       |
| 2023-09-14   | 3       |
| 2023-09-14   | 4       |
| 2023-09-14   | 5       |

Scenario

WhatsApp analytics team wants to understand recent calling behavior and engagement in France.

Question

Write SQL to return the number of unique users who initiated a call with more than 3 participants in the last 7 days.

Write SQL to calculate the percentage of yesterday's daily active users whose country_code = 'fr' that were on a video call.

Explain whether COUNT(DISTINCT user_id) is still needed after grouping by user_id.

Compare UNION versus UNION ALL and when to use each.

Hints

Think about event-level tables, country filtering, GROUP BY-then-COUNT logic, and deduplication/performance trade-offs for UNION.

French Callers With More Than 3 Participants

Count the number of distinct French users (USERS.country_code = 'fr') who initiated at least one call with more than 3 participants from 2025-05-25 through 2025-05-31 inclusive. Return a single column named count_users.

Table USERS

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
country_code	VARCHAR(2)	NOT NULL

Sample data:

country_code	user_id
fr	1
fr	2
us	3
fr	4
fr	5

Table CALLS

column	type	constraints
call_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES USERS(user_id)
call_start_time	TIMESTAMP	NOT NULL
participant_cnt	INTEGER	NOT NULL
is_video	INTEGER	NOT NULL, CHECK (is_video IN (0,1))

Sample data:

call_id	call_start_time	is_video	participant_cnt	user_id
201	2025-05-31 10:00:00	1	4	1
202	2025-05-31 11:30:00	1	2	2
203	2025-05-30 09:45:00	0	5	3
204	2025-05-29 20:10:00	1	6	1
205	2025-05-29 08:00:00	1	3	4

Table DAILY_ACTIVITY

column	type	constraints
activity_date	DATE	NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES USERS(user_id)

Sample data:

activity_date	user_id
2025-05-31	1
2025-05-31	2
2025-05-31	3
2025-05-31	4
2025-05-31	5

Expected output

Consider the 7-day window from 2025-05-25 through 2025-05-31 inclusive. Within this window, user 1 (country_code = 'fr') initiated calls with more than 3 participants (call_id 201 and 204). User 3 also has such a call but is in the US, and user 4's call has only 3 participants. Therefore the number of unique French callers with >3 participants is 1.

count_users
1

Hints

1. Join CALLS to USERS on user_id.
2. Use participant_cnt > 3.
3. Use a half-open timestamp range ending before 2025-06-01.

French DAU Video Rate on 2025-05-31

Using DAILY_ACTIVITY, treat 2025-05-31 as the activity date of interest. Among French daily active users (country_code = 'fr') on that date, calculate the percentage who had at least one video call (is_video = 1) on 2025-05-31. Return one column named video_rate_pct, rounded to 2 decimal places.

Table USERS

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
country_code	VARCHAR(2)	NOT NULL

Sample data:

country_code	user_id
fr	1
fr	2
us	3
fr	4
fr	5

Table CALLS

column	type	constraints
call_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES USERS(user_id)
call_start_time	TIMESTAMP	NOT NULL
participant_cnt	INTEGER	NOT NULL
is_video	INTEGER	NOT NULL, CHECK (is_video IN (0,1))

Sample data:

call_id	call_start_time	is_video	participant_cnt	user_id
201	2025-05-31 10:00:00	1	4	1
202	2025-05-31 11:30:00	1	2	2
203	2025-05-30 09:45:00	0	5	3
204	2025-05-29 20:10:00	1	6	1
205	2025-05-29 08:00:00	1	3	4

Table DAILY_ACTIVITY

column	type	constraints
activity_date	DATE	NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES USERS(user_id)

Sample data:

activity_date	user_id
2025-05-31	1
2025-05-31	2
2025-05-31	3
2025-05-31	4
2025-05-31	5

Expected output

On 2025-05-31, DAILY_ACTIVITY shows users {1,2,3,4,5} as active. The French DAU are users {1,2,4,5}. Among them, users {1,2} were on at least one video call that day (CALLS rows 201 and 202 with is_video = 1 and call_start_time on 2025-05-31). Thus 2 of 4 French DAU were on a video call, i.e., 50.00%.

video_rate_pct
50

Hints

1. Build French DAU from DAILY_ACTIVITY joined to USERS.
2. Use DISTINCT users from same-day video calls for the numerator.
3. Divide by the DAU count and round the percentage to 2 decimals.

COUNT(DISTINCT) vs grouped count on French DAU

Using the French daily active users on 2025-05-31 (the latest activity_date in DAILY_ACTIVITY), demonstrate that COUNT(DISTINCT user_id) is redundant after grouping by user_id by returning both counts and showing they are equal.

Table USERS

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
country_code	VARCHAR(2)	NOT NULL

Sample data:

country_code	user_id
fr	1
fr	2
us	3
fr	4
fr	5

Table DAILY_ACTIVITY

column	type	constraints
activity_date	DATE	NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES USERS(user_id)

Sample data:

activity_date	user_id
2025-05-31	1
2025-05-31	2
2025-05-31	3
2025-05-31	4
2025-05-31	5

Expected output

On 2025-05-31, the French DAU are users {1,2,4,5}. Grouping fr_dau by user_id gives 4 rows. COUNT(DISTINCT user_id) on the same set is also 4, so the two counts are equal.

count_after_group_by	count_distinct_user_id
4	4

Hints

1. First build the set of French DAU for 2025-05-31, then group that result by user_id and count the rows.
2. Compare that grouped-row count to COUNT(DISTINCT user_id) on the same fr_dau set.

UNION vs UNION ALL on French DAU

Using the list of French daily active users on 2025-05-31 (the latest activity_date in DAILY_ACTIVITY), combine that list with itself and report how many rows are returned using UNION versus UNION ALL.

Table USERS

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
country_code	VARCHAR(2)	NOT NULL

Sample data:

country_code	user_id
fr	1
fr	2
us	3
fr	4
fr	5

Table DAILY_ACTIVITY

column	type	constraints
activity_date	DATE	NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES USERS(user_id)

Sample data:

activity_date	user_id
2025-05-31	1
2025-05-31	2
2025-05-31	3
2025-05-31	4
2025-05-31	5

Expected output

On 2025-05-31, the French DAU are 4 users {1,2,4,5}. UNION removes duplicates, so combining the list with itself returns 4 rows. UNION ALL preserves duplicates, so it returns 8 rows.

op	rows_returned
UNION	4
UNION ALL	8

Hints

1. Build the French DAU list for 2025-05-31 from DAILY_ACTIVITY joined to USERS.
2. Use UNION to de-duplicate and UNION ALL to keep duplicates, then compare the row counts.