Write SQL for call analytics

You are given two tables.

Table: calls

• call_id BIGINT
• sender_id BIGINT
• receiver_id BIGINT
• call_ts TIMESTAMP — stored in UTC
• pickup CHAR(1) — 'Y' if the call was picked up, 'N' otherwise
• call_type VARCHAR — either 'video' or 'voice'

Assume each row represents one call attempt.

Table: users

• user_id BIGINT
• country VARCHAR

Assume calls.sender_id = users.user_id when you need the caller's country. Unless otherwise stated, compute results over the full available dataset.

Write SQL for the following:

1. Return the total number of calls and the number of picked-up calls for each call_type.

   • Output columns: call_type, total_calls, picked_up_calls

2. For a given country parameter, compute the percentage of calls placed by senders in that country that were picked up.

   • Output columns: country, pickup_rate_pct
   • Treat pickup_rate_pct as 100.0 * picked_up_calls / total_calls

3. Compute the percentage of distinct callers who have made at least one video call and at least one voice call.

   • Denominator: all distinct sender_id values in calls
   • Output column: pct_callers_both_types

Write SQL for call analytics

Table calls

column	type	constraints
call_id	BIGINT
sender_id	BIGINT
receiver_id	BIGINT
call_ts	TIMESTAMP
pickup	CHAR(1)
call_type	VARCHAR

Sample data:

call_id	call_ts	call_type	pickup	receiver_id	sender_id
1	2024-01-01 10:00:00	video	Y	3	1
2	2024-01-01 10:05:00	voice	N	4	1
3	2024-01-02 12:00:00	voice	Y	1	2
4	2024-01-02 12:10:00	video	Y	3	2
5	2024-01-03 09:00:00	video	N	4	3
6	2024-01-03 09:05:00	voice	N	2	3
7	2024-01-04 15:00:00	voice	Y	1	4
8	2024-01-05 08:00:00	video	Y	2	5
9	2024-01-05 08:10:00	video	N	3	5
10	2024-01-05 08:20:00	voice	Y	4	5

Table users

column	type	constraints
user_id	BIGINT
country	VARCHAR

Sample data:

country	user_id
US	1
US	2
CA	3
IN	4
US	5

Expected output

Single unified result set combining: part 1 per call_type counts, part 2 pickup rate for country='US', and part 3 overall percentage of callers who made both call types.

call_type	country	pct_callers_both_types	picked_up_calls	pickup_rate_pct	section	total_calls
video	NULL	NULL	3	NULL	1	5
voice	NULL	NULL	3	NULL	1	5
NULL	US	NULL	NULL	71.43	2	NULL
NULL	NULL	80	NULL	NULL	3	NULL

Hints

1. Use conditional aggregation with SUM(CASE WHEN pickup='Y' THEN 1 ELSE 0 END) for picked-up counts.
2. Join calls to users on calls.sender_id = users.user_id when filtering by sender country.
3. Avoid integer division by multiplying by 100.0 and/or casting counts to numeric before division.
4. Identify callers who made both types using GROUP BY sender_id HAVING COUNT(DISTINCT call_type) = 2.
5. If parameters are not supported, emulate a parameter via a CTE (e.g., params AS (SELECT 'US' AS country)).