Days the Air Quality Got Worse Than the Day Before — SQL Easy Problem

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

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

Output

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))