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
| Column | Type |
|---|---|
| request_id | int |
| mobile_number | varchar |
| donor_operator | varchar |
| recipient_operator | varchar |
| requested_on | date |
| status | enum(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_id | mobile_number | donor_operator | recipient_operator | requested_on | status |
|---|---|---|---|---|---|
| 1 | 9845012345 | Nimbus Mobile | Tara Telecom | 2024-02-11 | completed |
| 2 | 9900123456 | Nimbus Mobile | Vega Wireless | 2024-03-02 | completed |
| 3 | 9731122334 | Tara Telecom | Nimbus Mobile | 2024-03-05 | completed |
| 4 | 8123456789 | Nimbus Mobile | Tara Telecom | 2024-04-19 | rejected |
| 5 | 7019988776 | Nimbus Mobile | Vega Wireless | 2024-05-23 | completed |
| 6 | 9611223344 | Nimbus Mobile | Sanchar One | 2023-12-30 | completed |
| 7 | 9886655443 | Nimbus Mobile | Tara Telecom | 2024-06-01 | completed |
Output
| recipient_operator | port_outs |
|---|---|
| Tara Telecom | 2 |
| Vega Wireless | 2 |
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_operatorAnother 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 →