Completed Port-Outs by Recipient Operator

Easy
503.

Completed Port-Outs by Recipient Operator

Nimbus Mobile tracks every mobile number portability request in which it is either the donor (the number leaves) or the recipient (the number joins). The churn team wants to know who is winning its customers.

Count the completed requests in 2024 (by requested_on) where donor_operator is 'Nimbus Mobile', per recipient. Return recipient_operator and port_outs, ordered by port_outs from highest to lowest, then by recipient_operator alphabetically. Operators that took no completed port-out do not appear.

Tables

Table: PortRequest

ColumnTypeValues
request_idint
mobile_numbervarchar
donor_operatorvarchar
recipient_operatorvarchar
requested_ondate
statusenumcompleted, rejected, pending, withdrawn

Primary key: request_id.

One row per porting request. The donor is the operator the number is leaving, the recipient the one it moves to.

Example

The rows must come back in the order shown.

PortRequest

request_idmobile_numberdonor_operatorrecipient_operatorrequested_onstatus
19845012345Nimbus MobileTara Telecom2024-02-11completed
29900123456Nimbus MobileVega Wireless2024-03-02completed
39731122334Tara TelecomNimbus Mobile2024-03-05completed
48123456789Nimbus MobileTara Telecom2024-04-19rejected
57019988776Nimbus MobileVega Wireless2024-05-23completed
69611223344Nimbus MobileSanchar One2023-12-30completed
79886655443Nimbus MobileTara Telecom2024-06-01completed

Output

recipient_operatorport_outs
Tara Telecom2
Vega Wireless2
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.

Completed Port-Outs by Recipient Operator — SQL Problem

Nimbus Mobile tracks every mobile number portability request in which it is either the donor (the number leaves) or the recipient (the number joins).

  • Difficulty: Easy
  • Topics: Basics, Aggregation
  • Dialect: MySQL
  • Problem: #503

Problem statement

Nimbus Mobile tracks every mobile number portability request in which it is either the donor (the number leaves) or the recipient (the number joins). The churn team wants to know who is winning its customers.

Count the completed requests in 2024 (by requested_on) where donor_operator is 'Nimbus Mobile', per recipient. Return recipient_operator and port_outs, ordered by port_outs from highest to lowest, then by recipient_operator alphabetically. Operators that took no completed port-out do not appear.

Tables

Table: PortRequest

ColumnType
request_idint
mobile_numbervarchar
donor_operatorvarchar
recipient_operatorvarchar
requested_ondate
statusenum(completed, rejected, pending, withdrawn)

Primary key: request_id.

One row per porting request. The donor is the operator the number is leaving, the recipient the one it moves to.

Examples

Example 1

PortRequest

request_idmobile_numberdonor_operatorrecipient_operatorrequested_onstatus
19845012345Nimbus MobileTara Telecom2024-02-11completed
29900123456Nimbus MobileVega Wireless2024-03-02completed
39731122334Tara TelecomNimbus Mobile2024-03-05completed
48123456789Nimbus MobileTara Telecom2024-04-19rejected
57019988776Nimbus MobileVega Wireless2024-05-23completed
69611223344Nimbus MobileSanchar One2023-12-30completed
79886655443Nimbus MobileTara Telecom2024-06-01completed

Output

recipient_operatorport_outs
Tara Telecom2
Vega Wireless2

How to solve Completed Port-Outs by Recipient Operator

The question is a filtered count per group. The WHERE clause keeps only the rows that are a port-out from Nimbus Mobile (donor_operator = 'Nimbus Mobile' — a port-in has Nimbus as the recipient instead), that actually completed (rejected, pending and withdrawn requests did not move a customer), and that were requested in 2024 (BETWEEN '2024-01-01' AND '2024-12-31', both ends included).

The remaining rows are grouped by recipient_operator and counted. Because the filter runs before grouping, an operator whose only requests were rejected has no row left and no group, which is what the statement asks for.

Counting with conditional aggregation — SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) — and dropping the zero groups with HAVING gives the same answer and is the pattern to reach for when several statuses need their own columns. The cost is one scan and a sort of a handful of operators.

Reference solution (MySQL)

SELECT recipient_operator, COUNT(*) AS port_outs
FROM PortRequest
WHERE donor_operator = 'Nimbus Mobile'
  AND status = 'completed'
  AND requested_on BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY recipient_operator
ORDER BY port_outs DESC, recipient_operator

Another way

SELECT recipient_operator, SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS port_outs
FROM PortRequest
WHERE donor_operator = 'Nimbus Mobile' AND YEAR(requested_on) = 2024
GROUP BY recipient_operator
HAVING SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) > 0
ORDER BY port_outs DESC, recipient_operator

← Masked Mobile Numbers on Bill Statements · Electricity Bills Paid After the Due Date →