You are given two tables.
users
-
user_id
BIGINT PRIMARY KEY
-
country_code
STRING
calls
-
call_id
BIGINT PRIMARY KEY
-
caller_id
BIGINT
-
recipient_id
BIGINT
-
call_type
STRING — values include
audio
and
video
-
initiated_at
TIMESTAMP
-
duration_seconds
INT
Assume each row in calls represents one completed call session. Both caller_id and recipient_id reference users.user_id. Use UTC for all timestamps. Interpret last 7 days as initiated_at >= CURRENT_TIMESTAMP - INTERVAL '7' DAY and initiated_at < CURRENT_TIMESTAMP.
Write SQL for both tasks:
-
Count the number of distinct users who initiated more than 10 calls in the last 7 days.
-
Required output column:
initiators_over_10_calls
-
Among users in Great Britain (
country_code = 'gb'
), compute the percentage of active users who had more than 50 video calls in the last 7 days, counting both calls they placed and calls they received.
-
Define an active GB user as a GB user with at least one call of any type in the last 7 days, either as caller or recipient.
-
Required output columns:
active_gb_users
,
gb_users_over_50_video_calls
,
pct_gb_active_users_over_50_video_calls