Uber: Cities With Two or More Critical Safety Reports
The safety team escalates to a city's operations lead when riders or drivers file repeated critical safety reports there in a month.
- Difficulty: Easy
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #980
Problem statement
The safety team escalates to a city's operations lead when riders or drivers file repeated critical safety reports there in a month.
Count the reports with severity critical filed (by reported_on) in June 2024, per city of the trip they concern. Return city and critical_reports for every city with at least 2 of them, ordered by critical_reports descending and then by city. Each report counts once, even when two reports concern the same trip.
A practice scenario set at Uber: the tables and data are invented, and this is not a question from, or endorsed by, Uber.
Tables
Table: Trip
| Column | Type |
|---|---|
| trip_id | int |
| city | varchar |
| trip_date | date |
Primary key: trip_id.
One row per trip.
Table: SafetyReport
| Column | Type |
|---|---|
| report_id | int |
| trip_id | int |
| severity | enum(low, medium, high, critical) |
| reported_on | date |
Primary key: report_id.
One row per safety report; trip_id always names an existing trip.
Examples
Example 1
Trip
| trip_id | city | trip_date |
|---|---|---|
| 1 | Bengaluru | 2024-06-01 |
| 2 | Bengaluru | 2024-06-17 |
| 3 | Mumbai | 2024-06-29 |
| 4 | Mumbai | 2024-06-30 |
| 5 | Delhi | 2024-06-09 |
| 6 | Pune | 2024-06-11 |
SafetyReport
| report_id | trip_id | severity | reported_on |
|---|---|---|---|
| 1 | 1 | critical | 2024-06-03 |
| 2 | 2 | critical | 2024-06-18 |
| 3 | 3 | critical | 2024-06-30 |
| 4 | 4 | critical | 2024-07-01 |
| 5 | 5 | critical | 2024-06-10 |
| 6 | 5 | critical | 2024-06-11 |
| 7 | 6 | high | 2024-06-12 |
| 8 | 6 | high | 2024-06-13 |
| 9 | 1 | critical | 2024-06-25 |
Output
| city | critical_reports |
|---|---|
| Bengaluru | 3 |
| Delhi | 2 |
How to solve Uber: Cities With Two or More Critical Safety Reports
Each report needs its trip's city, so the query joins SafetyReport to Trip on trip_id. Only critical reports filed in June count, which is a row filter in WHERE: severity equal to critical and reported_on between the first and the last day of June. Note the date that matters is the report's, not the trip's — Mumbai's trip on 30 June was reported on 1 July and falls outside.
GROUP BY t.city then counts the surviving reports per city, and HAVING COUNT(*) >= 2 keeps only repeat cities. Because the count is of report rows, two reports on Delhi's one trip count as two, as the statement asks.
The order is by count descending with the city name as a tie-breaker, which makes the output unique. The alternative counts with a conditional SUM over all reports and applies the threshold in an outer query — the same result. Both are one join and one aggregation.
Reference solution (MySQL)
SELECT t.city, COUNT(*) AS critical_reports
FROM SafetyReport s
JOIN Trip t ON t.trip_id = s.trip_id
WHERE s.severity = 'critical'
AND s.reported_on BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY t.city
HAVING COUNT(*) >= 2
ORDER BY critical_reports DESC, t.cityAnother way
SELECT city, critical_reports FROM (
SELECT t.city, SUM(CASE WHEN s.severity = 'critical' AND YEAR(s.reported_on) = 2024 AND MONTH(s.reported_on) = 6 THEN 1 ELSE 0 END) AS critical_reports
FROM Trip t JOIN SafetyReport s ON s.trip_id = t.trip_id
GROUP BY t.city
) c WHERE critical_reports >= 2
ORDER BY critical_reports DESC, city← Uber: Riders Who Gave Every Rated Trip Five Stars · Uber: Surge Share of Bengaluru Trips by Hour of Day →