Uber: Cities With Two or More Critical Safety Reports

Easy
980.

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.

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

ColumnType
trip_idint
cityvarchar
trip_datedate

Primary key: trip_id.

One row per trip.

Table: SafetyReport

ColumnTypeValues
report_idint
trip_idint
severityenumlow, medium, high, critical
reported_ondate

Primary key: report_id.

One row per safety report; trip_id always names an existing trip.

Example

The rows must come back in the order shown.

Trip

trip_idcitytrip_date
1Bengaluru2024-06-01
2Bengaluru2024-06-17
3Mumbai2024-06-29
4Mumbai2024-06-30
5Delhi2024-06-09
6Pune2024-06-11

SafetyReport

report_idtrip_idseverityreported_on
11critical2024-06-03
22critical2024-06-18
33critical2024-06-30
44critical2024-07-01
55critical2024-06-10
65critical2024-06-11
76high2024-06-12
86high2024-06-13
91critical2024-06-25

Output

citycritical_reports
Bengaluru3
Delhi2
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.

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

ColumnType
trip_idint
cityvarchar
trip_datedate

Primary key: trip_id.

One row per trip.

Table: SafetyReport

ColumnType
report_idint
trip_idint
severityenum(low, medium, high, critical)
reported_ondate

Primary key: report_id.

One row per safety report; trip_id always names an existing trip.

Examples

Example 1

Trip

trip_idcitytrip_date
1Bengaluru2024-06-01
2Bengaluru2024-06-17
3Mumbai2024-06-29
4Mumbai2024-06-30
5Delhi2024-06-09
6Pune2024-06-11

SafetyReport

report_idtrip_idseverityreported_on
11critical2024-06-03
22critical2024-06-18
33critical2024-06-30
44critical2024-07-01
55critical2024-06-10
65critical2024-06-11
76high2024-06-12
86high2024-06-13
91critical2024-06-25

Output

citycritical_reports
Bengaluru3
Delhi2

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.city

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