Cell Tower Dropped-Call Health Check

Easy
501.

Cell Tower Dropped-Call Health Check

The network operations centre grades each cell tower on last month's dropped calls. The drop rate is 100 * dropped_calls / total_calls.

Return every tower with the columns tower_id, site_name, drop_rate (the rate rounded to 2 decimals, or NULL for a tower with no calls) and health: 'no traffic' when total_calls is 0, 'critical' when the rate is 3 or more, 'watch' when it is 1.5 or more, otherwise 'healthy'. Grade on the exact, unrounded rate. Order by tower_id.

Tables

Table: Tower

ColumnType
tower_idint
site_namevarchar
total_callsint
dropped_callsint

Primary key: tower_id.

Last month's call counts per tower; dropped_calls is never more than total_calls.

Example

The rows must come back in the order shown.

Tower

tower_idsite_nametotal_callsdropped_calls
1Koramangala-01200070
2Whitefield-04150045
3HSR Layout-0280012
4Indiranagar-03120017
5Jayanagar-0500
6Marathahalli-0295020

Output

tower_idsite_namedrop_ratehealth
1Koramangala-013.5critical
2Whitefield-043critical
3HSR Layout-021.5watch
4Indiranagar-031.42healthy
5Jayanagar-05NULLno traffic
6Marathahalli-022.11watch
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.

Cell Tower Dropped-Call Health Check — SQL Basics Problem

The network operations centre grades each cell tower on last month's dropped calls. The drop rate is 100 * dropped_calls / total_calls.

  • Difficulty: Easy
  • Topics: Basics, Conditional Logic
  • Dialect: MySQL
  • Problem: #501

Problem statement

The network operations centre grades each cell tower on last month's dropped calls. The drop rate is 100 * dropped_calls / total_calls.

Return every tower with the columns tower_id, site_name, drop_rate (the rate rounded to 2 decimals, or NULL for a tower with no calls) and health: 'no traffic' when total_calls is 0, 'critical' when the rate is 3 or more, 'watch' when it is 1.5 or more, otherwise 'healthy'. Grade on the exact, unrounded rate. Order by tower_id.

Tables

Table: Tower

ColumnType
tower_idint
site_namevarchar
total_callsint
dropped_callsint

Primary key: tower_id.

Last month's call counts per tower; dropped_calls is never more than total_calls.

Examples

Example 1

Tower

tower_idsite_nametotal_callsdropped_calls
1Koramangala-01200070
2Whitefield-04150045
3HSR Layout-0280012
4Indiranagar-03120017
5Jayanagar-0500
6Marathahalli-0295020

Output

tower_idsite_namedrop_ratehealth
1Koramangala-013.5critical
2Whitefield-043critical
3HSR Layout-021.5watch
4Indiranagar-031.42healthy
5Jayanagar-05NULLno traffic
6Marathahalli-022.11watch

How to solve Cell Tower Dropped-Call Health Check

Two derived columns over one table, so no join or grouping is needed.

drop_rate divides dropped by total calls. NULLIF(total_calls, 0) turns a zero divisor into NULL, and any arithmetic with NULL is NULL, so a silent tower gets a NULL rate rather than an error or a misleading 0. The rate is rounded to two decimals only for display.

health is a CASE whose branches are tried top to bottom: the zero-traffic case first, then critical (rate ≥ 3), then watch (rate ≥ 1.5), and healthy as the fallback. Because the first true branch wins, the watch test does not need an upper bound. The reference compares in integers — dropped_calls * 100 >= total_calls * 3 is the rate ≥ 3 without any division — so a tower at exactly 3.00% or 1.50% lands in the higher band and no rounding can move it. Comparing the exact quotient, as the IF version does, gives the same grades. One pass over the table.

Reference solution (MySQL)

SELECT tower_id, site_name,
       ROUND(100 * dropped_calls / NULLIF(total_calls, 0), 2) AS drop_rate,
       CASE
         WHEN total_calls = 0 THEN 'no traffic'
         WHEN dropped_calls * 100 >= total_calls * 3 THEN 'critical'
         WHEN dropped_calls * 200 >= total_calls * 3 THEN 'watch'
         ELSE 'healthy'
       END AS health
FROM Tower
ORDER BY tower_id

Another way

SELECT tower_id, site_name,
       IF(total_calls = 0, NULL, ROUND(100 * dropped_calls / total_calls, 2)) AS drop_rate,
       IF(total_calls = 0, 'no traffic',
          IF(100 * dropped_calls / total_calls >= 3, 'critical',
             IF(100 * dropped_calls / total_calls >= 1.5, 'watch', 'healthy'))) AS health
FROM Tower
ORDER BY tower_id

← August Data Usage by Telecom Circle · Masked Mobile Numbers on Bill Statements →