International Voice Call Share per Caller

Medium
513.

International Voice Call Share per Caller

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

ColumnTypeValues
cdr_idint
caller_numbervarchar
callee_numbervarchar
call_typeenumvoice, sms
started_atdatetime
duration_secint

Primary key: cdr_id.

One call detail record. Numbers are stored in E.164 form, e.g. +919845012345, +14155550123.

Example

The rows must come back in the order shown.

Cdr

cdr_idcaller_numbercallee_numbercall_typestarted_atduration_sec
1+919845012345+919900123456voice2024-08-01 10:00:00120
2+919845012345+14155550123voice2024-08-01 22:15:00600
3+919845012345+971501234567voice2024-08-02 23:40:00840
4+919845012345+447700900123sms2024-08-02 23:45:000
5+919731122334+919845012345voice2024-08-03 09:10:0060
6+919731122334+918123456789voice2024-08-03 13:00:0030
7+919731122334+6591234567voice2024-08-04 08:20:00300
8+918123456789+14155550123sms2024-08-04 12:00:000
9+917019988776+919845012345voice2024-08-05 19:00:0095

Output

caller_numbervoice_callsintl_share_pct
+919845012345366.67
+919731122334333.33
+91701998877610
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
cdr_idint
caller_numbervarchar
callee_numbervarchar
call_typeenum(voice, sms)
started_atdatetime
duration_secint

Primary key: cdr_id.

One call detail record. Numbers are stored in E.164 form, e.g. +919845012345, +14155550123.

Examples

Example 1

Cdr

cdr_idcaller_numbercallee_numbercall_typestarted_atduration_sec
1+919845012345+919900123456voice2024-08-01 10:00:00120
2+919845012345+14155550123voice2024-08-01 22:15:00600
3+919845012345+971501234567voice2024-08-02 23:40:00840
4+919845012345+447700900123sms2024-08-02 23:45:000
5+919731122334+919845012345voice2024-08-03 09:10:0060
6+919731122334+918123456789voice2024-08-03 13:00:0030
7+919731122334+6591234567voice2024-08-04 08:20:00300
8+918123456789+14155550123sms2024-08-04 12:00:000
9+917019988776+919845012345voice2024-08-05 19:00:0095

Output

caller_numbervoice_callsintl_share_pct
+919845012345366.67
+919731122334333.33
+91701998877610

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_number

Another 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_number

Another 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 →