Compute percent of active users with 50+ calls

Problem

You work on a Messenger-like app. You want to measure how many active users in Great Britain (GB) today have been heavy callers recently.

Tables

Assume two tables (names can vary).

1) calls

One row per call record.

• call_id (STRING, primary key)
• caller_id (STRING)
• receiver_id (STRING)
• date (STRING) — call date in string format (e.g., '2025-11-06' )
• duration (INT) — call duration (seconds)

2) user_daily_status

One row per user per day describing user attributes and activity.

• user_id (STRING)
• country (STRING)
• is_active (INT) — 1 if the user is active on that date, else 0
• date (STRING) — snapshot date in string format (e.g., '2025-11-06' )

Definitions / assumptions

• “GB active users today” = users with country = 'GB' , is_active = 1 on today’s snapshot date.
• “Previous seven days” = the 7-day lookback window excluding today , i.e. [today - 7 days, today - 1 day] .
• A user’s “call records” count includes both:
  • outgoing calls where the user is the caller_id , and
  • incoming calls where the user is the receiver_id .
• Count calls as number of call records (e.g., count of call_id ).

Task

Write a SQL query to compute:

• the percentage of today’s GB active users who had more than 50 call records in the previous 7 days.

Output

Return at least:

• as_of_date
• active_users_today (denominator)
• active_users_gt_50_calls_last_7d (numerator)
• pct_active_users_gt_50_calls_last_7d

(You may assume your SQL dialect provides a way to parse the date string into a date type.)