Daily Network Drop Rate Including Silent Days

Hard
520.

Daily Network Drop Rate Including Silent Days

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

ColumnTypeValues
call_idint
tower_idint
started_atdatetime
end_reasonenumnormal, dropped, failed

Primary key: call_id.

One call attempt across the whole network, dated by when it started.

Example

The rows must come back in the order shown.

CallAttempt

call_idtower_idstarted_atend_reason
11012024-06-30 23:59:00dropped
21012024-07-01 08:00:00normal
31022024-07-01 09:30:00dropped
41012024-07-01 21:10:00normal
51032024-07-02 00:00:00normal
61022024-07-02 13:45:00failed
71032024-07-04 18:20:00dropped
81012024-07-05 07:15:00failed
91022024-07-08 11:00:00normal
101012024-07-11 00:00:00dropped

Output

call_dateconnected_callsdropped_callsdrop_rate_pct
2024-07-013133.33
2024-07-02100
2024-07-0300NULL
2024-07-0411100
2024-07-0500NULL
2024-07-0600NULL
2024-07-0700NULL
2024-07-08100
2024-07-0900NULL
2024-07-1000NULL
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.

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

ColumnType
call_idint
tower_idint
started_atdatetime
end_reasonenum(normal, dropped, failed)

Primary key: call_id.

One call attempt across the whole network, dated by when it started.

Examples

Example 1

CallAttempt

call_idtower_idstarted_atend_reason
11012024-06-30 23:59:00dropped
21012024-07-01 08:00:00normal
31022024-07-01 09:30:00dropped
41012024-07-01 21:10:00normal
51032024-07-02 00:00:00normal
61022024-07-02 13:45:00failed
71032024-07-04 18:20:00dropped
81012024-07-05 07:15:00failed
91022024-07-08 11:00:00normal
101012024-07-11 00:00:00dropped

Output

call_dateconnected_callsdropped_callsdrop_rate_pct
2024-07-013133.33
2024-07-02100
2024-07-0300NULL
2024-07-0411100
2024-07-0500NULL
2024-07-0600NULL
2024-07-0700NULL
2024-07-08100
2024-07-0900NULL
2024-07-1000NULL

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_date

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