Write Call Analytics SQL Queries

Q: Write Call Analytics SQL Queries

This question evaluates SQL data manipulation and analytical competencies, including aggregation, joins between user and event tables, time-window filtering, deduplication for unique users, and computation of counts and pickup rates.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

Loading...

Assume you are given two tables.

Table: calls

call_id BIGINT
sender_id BIGINT
receiver_id BIGINT
call_time TIMESTAMP
pickup VARCHAR -- values: 'Y' or 'N'
call_type VARCHAR -- values: 'video' or 'voice'

Table: users

user_id BIGINT
country VARCHAR

Assume:

calls.sender_id and calls.receiver_id both reference users.user_id .
All timestamps are stored in UTC.
Unless otherwise stated, use the last 30 full days of data ending at CURRENT_DATE in UTC.
A 'caller' means sender_id .

Write SQL for the following tasks:

Return daily call volume and pickup rate by call type for the last 30 days. Required output columns:
- call_date
- call_type
- total_calls
- picked_up_calls
- pickup_rate
For a specified country (for example, 'US'), compute the percentage of calls initiated by callers in that country that were picked up during the same 30-day window. Required output columns:
- country
- total_calls
- picked_up_calls
- pickup_rate
Compute the percentage of unique callers who made at least one voice call and at least one video call during the same 30-day window. Required output columns:
- total_unique_callers
- callers_with_both_types
- pct_callers_with_both_types

Write Call Analytics SQL Queries

Quick Overview

Comments (0)