International Voice Call Share per Caller — SQL Problem
The fraud team watches for prepaid numbers whose calling suddenly turns international.
- Difficulty: Medium
- Topics: Strings, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #513
Problem statement
The fraud team watches for prepaid numbers whose calling suddenly turns international. Numbers in the call detail records carry their country code, so an Indian number starts with +91 and any callee number that does not start with +91 is international.
Considering voice calls only (call_type = 'voice'; SMS rows are ignored), return each caller with at least one voice call, with the columns caller_number, voice_calls and intl_share_pct — the percentage of their voice calls that went to international numbers, rounded to 2 decimals. Order by intl_share_pct from highest to lowest, then by caller_number.
Tables
Table: Cdr
| Column | Type |
|---|---|
| cdr_id | int |
| caller_number | varchar |
| callee_number | varchar |
| call_type | enum(voice, sms) |
| started_at | datetime |
| duration_sec | int |
Primary key: cdr_id.
One call detail record. Numbers are stored in E.164 form, e.g. +919845012345, +14155550123.
Examples
Example 1
Cdr
| cdr_id | caller_number | callee_number | call_type | started_at | duration_sec |
|---|---|---|---|---|---|
| 1 | +919845012345 | +919900123456 | voice | 2024-08-01 10:00:00 | 120 |
| 2 | +919845012345 | +14155550123 | voice | 2024-08-01 22:15:00 | 600 |
| 3 | +919845012345 | +971501234567 | voice | 2024-08-02 23:40:00 | 840 |
| 4 | +919845012345 | +447700900123 | sms | 2024-08-02 23:45:00 | 0 |
| 5 | +919731122334 | +919845012345 | voice | 2024-08-03 09:10:00 | 60 |
| 6 | +919731122334 | +918123456789 | voice | 2024-08-03 13:00:00 | 30 |
| 7 | +919731122334 | +6591234567 | voice | 2024-08-04 08:20:00 | 300 |
| 8 | +918123456789 | +14155550123 | sms | 2024-08-04 12:00:00 | 0 |
| 9 | +917019988776 | +919845012345 | voice | 2024-08-05 19:00:00 | 95 |
Output
| caller_number | voice_calls | intl_share_pct |
|---|---|---|
| +919845012345 | 3 | 66.67 |
| +919731122334 | 3 | 33.33 |
| +917019988776 | 1 | 0 |
How to solve International Voice Call Share per Caller
The rule that classifies a call is a prefix test on a string: a callee is domestic when the number starts with +91. callee_number LIKE '+91%' expresses it (the % matches the rest), as does LEFT(callee_number, 3) = '+91'. A UAE number like +971… starts with +97, not +91, so it is correctly international.
After WHERE call_type = 'voice' throws away SMS rows, the rows are grouped per caller. COUNT(*) is the number of voice calls and SUM(CASE WHEN … THEN 0 ELSE 1 END) the number of international ones — conditional aggregation. Their ratio times 100, rounded to two decimals, is the share. Because the filter runs first, a caller who only sent SMS has no group and is not reported, and the division never sees a zero count.
Averaging an indicator that is 100 for international and 0 for domestic computes the same percentage in one step. Keeping SMS rows and filtering inside the CASEs works too, with HAVING dropping SMS-only callers. Each is one scan with a group per caller.
Reference solution (MySQL)
SELECT caller_number, COUNT(*) AS voice_calls,
ROUND(100 * SUM(CASE WHEN callee_number LIKE '+91%' THEN 0 ELSE 1 END) / COUNT(*), 2) AS intl_share_pct
FROM Cdr
WHERE call_type = 'voice'
GROUP BY caller_number
ORDER BY intl_share_pct DESC, caller_numberAnother way
SELECT caller_number, COUNT(*) AS voice_calls,
ROUND(AVG(IF(LEFT(callee_number, 3) = '+91', 0, 100)), 2) AS intl_share_pct
FROM Cdr
WHERE call_type = 'voice'
GROUP BY caller_number
ORDER BY intl_share_pct DESC, caller_numberAnother way
SELECT caller_number,
SUM(CASE WHEN call_type = 'voice' THEN 1 ELSE 0 END) AS voice_calls,
ROUND(100 * SUM(CASE WHEN call_type = 'voice' AND SUBSTRING(callee_number, 1, 3) <> '+91' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN call_type = 'voice' THEN 1 ELSE 0 END), 2) AS intl_share_pct
FROM Cdr
GROUP BY caller_number
HAVING SUM(CASE WHEN call_type = 'voice' THEN 1 ELSE 0 END) > 0
ORDER BY intl_share_pct DESC, caller_number← Busiest Calling Hour per Cell Tower · Number Port Requests Breaching the 72-Hour SLA →