Daily Network Drop Rate Including Silent Days — SQL Problem
The NOC dashboard shows the dropped-call rate for every day from 2024-07-01 to 2024-07-10, even a day on which no call connected.
- Difficulty: Hard
- Topics: Dates, Joins, Conditional Logic
- Dialect: MySQL
- Problem: #520
Problem statement
The NOC dashboard shows the dropped-call rate for every day from 2024-07-01 to 2024-07-10, even a day on which no call connected. A call connected if it ended 'normal' or 'dropped'; 'failed' attempts never connected. The day's drop rate is 100 * dropped / connected.
Return exactly ten rows with the columns call_date, connected_calls, dropped_calls and drop_rate_pct (rounded to 2 decimals). On a day with no connected calls the counts are 0 and the rate is NULL. Attempts outside the window are ignored. Order by call_date.
Tables
Table: CallAttempt
| Column | Type |
|---|---|
| call_id | int |
| tower_id | int |
| started_at | datetime |
| end_reason | enum(normal, dropped, failed) |
Primary key: call_id.
One call attempt across the whole network, dated by when it started.
Examples
Example 1
CallAttempt
| call_id | tower_id | started_at | end_reason |
|---|---|---|---|
| 1 | 101 | 2024-06-30 23:59:00 | dropped |
| 2 | 101 | 2024-07-01 08:00:00 | normal |
| 3 | 102 | 2024-07-01 09:30:00 | dropped |
| 4 | 101 | 2024-07-01 21:10:00 | normal |
| 5 | 103 | 2024-07-02 00:00:00 | normal |
| 6 | 102 | 2024-07-02 13:45:00 | failed |
| 7 | 103 | 2024-07-04 18:20:00 | dropped |
| 8 | 101 | 2024-07-05 07:15:00 | failed |
| 9 | 102 | 2024-07-08 11:00:00 | normal |
| 10 | 101 | 2024-07-11 00:00:00 | dropped |
Output
| call_date | connected_calls | dropped_calls | drop_rate_pct |
|---|---|---|---|
| 2024-07-01 | 3 | 1 | 33.33 |
| 2024-07-02 | 1 | 0 | 0 |
| 2024-07-03 | 0 | 0 | NULL |
| 2024-07-04 | 1 | 1 | 100 |
| 2024-07-05 | 0 | 0 | NULL |
| 2024-07-06 | 0 | 0 | NULL |
| 2024-07-07 | 0 | 0 | NULL |
| 2024-07-08 | 1 | 0 | 0 |
| 2024-07-09 | 0 | 0 | NULL |
| 2024-07-10 | 0 | 0 | NULL |
How to solve Daily Network Drop Rate Including Silent Days
A report that must show days with no data cannot be driven by the data table: a day without rows has no group. So the query starts from a calendar. The recursive CTE seeds 2024-07-01 and adds one day while the date is before 2024-07-10, producing exactly the ten dates; the bound keeps the recursion short.
Separately, the attempts are grouped by their date — CAST(started_at AS DATE) drops the time, so 00:00:00 and 23:59:00 belong to their own day — with two conditional counts: connected (normal or dropped) and dropped. Failed attempts add to neither. The calendar is then LEFT JOINed to these counts, which keeps every date and also discards attempts outside the window, such as 30 June or 11 July.
On a date with no attempts the joined counts are NULL; COALESCE(…, 0) turns them into 0. The rate divides by NULLIF(connected, 0), so a silent day — or one with only failed attempts — gets NULL instead of a division error. Instead of recursion, a ten-row numbers table works as the calendar, with correlated counts over each day's half-open time range.
Reference solution (MySQL)
WITH RECURSIVE days AS (
SELECT CAST('2024-07-01' AS DATE) AS d
UNION ALL
SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM days WHERE d < '2024-07-10'
), daily AS (
SELECT CAST(started_at AS DATE) AS d,
SUM(CASE WHEN end_reason IN ('normal', 'dropped') THEN 1 ELSE 0 END) AS connected,
SUM(CASE WHEN end_reason = 'dropped' THEN 1 ELSE 0 END) AS dropped
FROM CallAttempt
GROUP BY CAST(started_at AS DATE)
)
SELECT days.d AS call_date,
COALESCE(daily.connected, 0) AS connected_calls,
COALESCE(daily.dropped, 0) AS dropped_calls,
ROUND(100 * daily.dropped / NULLIF(daily.connected, 0), 2) AS drop_rate_pct
FROM days
LEFT JOIN daily ON daily.d = days.d
ORDER BY call_dateAnother way
WITH n AS (
SELECT 0 AS k UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
), days AS (
SELECT DATE_ADD('2024-07-01', INTERVAL k DAY) AS d, DATE_ADD('2024-07-01', INTERVAL k + 1 DAY) AS next_d FROM n
), counted AS (
SELECT d,
(SELECT COUNT(*) FROM CallAttempt a WHERE a.started_at >= days.d AND a.started_at < days.next_d
AND a.end_reason <> 'failed') AS connected,
(SELECT COUNT(*) FROM CallAttempt a WHERE a.started_at >= days.d AND a.started_at < days.next_d
AND a.end_reason = 'dropped') AS dropped
FROM days
)
SELECT d AS call_date, connected AS connected_calls, dropped AS dropped_calls,
IF(connected = 0, NULL, ROUND(100 * dropped / connected, 2)) AS drop_rate_pct
FROM counted
ORDER BY call_date← Prepaid Recharge Next-Month Retention by Cohort · Top Two Data Consumers per Circle, Ties Included →