Write SQL for call pickup and usage metrics

You have two tables about 1:1 calls.

Table 1: calls

Each row is a call attempt.

• sender_id (BIGINT) — user who initiated the call
• receiver_id (BIGINT) — user who received the call
• call_time (TIMESTAMP) — when the call was initiated (assume UTC)
• picked_up (BOOLEAN) — whether the receiver picked up
• call_type (VARCHAR) — 'video' or 'voice'

Table 2: users

• user_id (BIGINT, PRIMARY KEY)
• country (VARCHAR)

Assume calls.sender_id and calls.receiver_id both reference users.user_id.

Q1) Basic aggregation

Return daily call volume and pickup rate by call type.

• Output columns: call_date , call_type , call_attempts , picked_up_calls , pickup_rate

Q2) Pickup percentage for a specific country

Compute the percentage of calls that are picked up for users in a given country (parameter :country).

• Define the metric as: among calls where the receiver is in :country , the fraction with picked_up = true .
• Output columns: country , call_attempts , picked_up_calls , pickup_rate

Q3) Callers who used both video and voice

Compute the percentage of distinct callers (sender_id) who made at least one video call and at least one voice call in a given time window [ :start_time, :end_time ).

• Output columns: total_callers , callers_both_types , pct_callers_both_types

Notes:

• Handle division-by-zero safely.
• You may assume standard SQL (Postgres-like syntax is fine).