Days the Air Quality Got Worse Than the Day Before

Easy
45.

Days the Air Quality Got Worse Than the Day Before

EasyDates

MySQL

A city air-quality station records one AQI reading per day, but it sometimes goes offline, so some calendar days have no reading. A higher AQI means worse air.

Return reading_date and aqi for every day whose AQI is strictly higher than the reading of the previous calendar day. A day whose previous calendar day has no reading is not compared and is not in the result. Return the rows in any order.

Tables

Table: AqiReading

ColumnType
reading_idint
reading_datedate
aqiint

Primary key: reading_id.

One row per day with a reading; no date appears twice. reading_id values are not in date order.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

AqiReading

reading_idreading_dateaqi
42024-10-30180
22024-10-31210
72024-11-01260
12024-11-02260
52024-11-04300
32024-11-05240
62024-11-06255

Output

reading_dateaqi
2024-10-31210
2024-11-06255
2024-11-01260
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.

Days the Air Quality Got Worse Than the Day Before

A city air-quality station records one AQI reading per day, but it sometimes goes offline, so some calendar days have no reading.

  • Difficulty: Easy
  • Topics: Dates
  • Dialect: MySQL
  • Problem: #45

Problem statement

A city air-quality station records one AQI reading per day, but it sometimes goes offline, so some calendar days have no reading. A higher AQI means worse air.

Return reading_date and aqi for every day whose AQI is strictly higher than the reading of the previous calendar day. A day whose previous calendar day has no reading is not compared and is not in the result. Return the rows in any order.

Tables

Table: AqiReading

ColumnType
reading_idint
reading_datedate
aqiint

Primary key: reading_id.

One row per day with a reading; no date appears twice. reading_id values are not in date order.

Examples

Example 1

AqiReading

reading_idreading_dateaqi
42024-10-30180
22024-10-31210
72024-11-01260
12024-11-02260
52024-11-04300
32024-11-05240
62024-11-06255

Output

reading_dateaqi
2024-10-31210
2024-11-06255
2024-11-01260

How to solve Days the Air Quality Got Worse Than the Day Before

Each day is compared with a different row — the reading of the day before — so join the table to itself: t plays today and y yesterday.

The join condition is the important part. The previous row in date order is not necessarily the previous calendar day, because the station skips days, and reading_id is not even in date order. So match on the dates themselves: DATEDIFF(t.reading_date, y.reading_date) = 1, or equivalently y.reading_date = DATE_SUB(t.reading_date, INTERVAL 1 DAY). Date functions handle month ends, year ends and leap years — 1 March follows 29 February in 2024 but 28 February in 2025 — which subtracting day numbers by hand does not.

After the join, WHERE t.aqi > y.aqi keeps the days that got worse. Equal readings are not higher, and a day with no reading the day before finds no partner, so the inner join drops it.

A window-function version reads the previous row with LAG(reading_date) and LAG(aqi) over the date order, but must still check that the previous row is exactly one day earlier — skipping that check is the usual bug. A correlated subquery that fetches yesterday's AQI works too: when there is no reading yesterday it returns NULL and the comparison drops the row. With an index on reading_date each version is one lookup per day.

Reference solution (MySQL)

SELECT t.reading_date, t.aqi
FROM AqiReading t
JOIN AqiReading y ON DATEDIFF(t.reading_date, y.reading_date) = 1
WHERE t.aqi > y.aqi

Another way

SELECT t.reading_date, t.aqi FROM AqiReading t JOIN AqiReading y ON y.reading_date = DATE_SUB(t.reading_date, INTERVAL 1 DAY) WHERE t.aqi > y.aqi

Another way

SELECT reading_date, aqi FROM (
  SELECT reading_date, aqi,
         LAG(reading_date) OVER (ORDER BY reading_date) AS prev_date,
         LAG(aqi) OVER (ORDER BY reading_date) AS prev_aqi
  FROM AqiReading
) x
WHERE DATEDIFF(reading_date, prev_date) = 1 AND aqi > prev_aqi

Another way

SELECT reading_date, aqi FROM AqiReading t WHERE aqi > (SELECT y.aqi FROM AqiReading y WHERE y.reading_date = DATE_SUB(t.reading_date, INTERVAL 1 DAY))

← Daily Active Users in the 30 Days to 15 March · UPI Payments by Month and City →