197. Rising Temperature

Problem¶

Given one weather row per date in Weather table, return the id of every row whose temperature is strictly higher than the temperature on the previous calendar day.

Input:

# Weather
---+------------+------------
id | recordDate | temperature
---+------------+------------
1  | 2015-01-01 | 10
2  | 2015-01-02 | 25
3  | 2015-01-03 | 20
4  | 2015-01-04 | 30

Output:

--
id
--
2
4

Key trick¶

Join each row with the row whose date is exactly one day before; do not just compare with the previous row after sorting.

Trap¶

Missing dates matter.
LAG() alone is wrong unless you also check that the lagged date is exactly yesterday.
The comparison must be strict: higher means >, not >=.
Return today's id, not yesterday's id.

Why is it interesting?¶

It tests whether you understand the difference between previous row and previous calendar day, which is a common time-series bug.

SQL solution¶

SQLite¶

-- Match each day with the row from exactly one calendar day before.
SELECT
    today.id
FROM Weather AS today
JOIN Weather AS yesterday
  ON yesterday.recordDate = date(today.recordDate, '-1 day')
WHERE today.temperature > yesterday.temperature;

PostgreSQL¶

-- Same logic as SQLite, but PostgreSQL uses interval arithmetic.
SELECT
    today.id
FROM Weather AS today
JOIN Weather AS yesterday
  ON yesterday.recordDate = today.recordDate - INTERVAL '1 day'
WHERE today.temperature > yesterday.temperature;

Pandas solution¶

import pandas as pd

def rising_temperature(weather: pd.DataFrame) -> pd.DataFrame:
    df = weather.copy()

    # Build the exact previous calendar date for each row.
    df["yesterday"] = df["recordDate"] - pd.Timedelta(days=1)

    # Join today's row to yesterday's row by date.
    merged = df.merge(
        df,
        left_on="yesterday",
        right_on="recordDate",
        how="inner",
        suffixes=("", "_yesterday"),
    )

    # Keep today's id only when today's temperature is strictly higher.
    return merged.loc[
        merged["temperature"] > merged["temperature_yesterday"],
        ["id"],
    ]

Pytest test¶

import sqlite3

import pandas as pd
import pytest


SQLITE_QUERY = """
SELECT
    today.id
FROM Weather AS today
JOIN Weather AS yesterday
  ON yesterday.recordDate = date(today.recordDate, '-1 day')
WHERE today.temperature > yesterday.temperature;
"""


def rising_temperature(weather: pd.DataFrame) -> pd.DataFrame:
    df = weather.copy()
    df["yesterday"] = df["recordDate"] - pd.Timedelta(days=1)

    merged = df.merge(
        df,
        left_on="yesterday",
        right_on="recordDate",
        how="inner",
        suffixes=("", "_yesterday"),
    )

    return merged.loc[
        merged["temperature"] > merged["temperature_yesterday"],
        ["id"],
    ]


def run_sqlite(rows):
    con = sqlite3.connect(":memory:")
    con.execute(
        """
        CREATE TABLE Weather (
            id INTEGER,
            recordDate TEXT,
            temperature INTEGER
        );
        """
    )
    con.executemany(
        """
        INSERT INTO Weather (id, recordDate, temperature)
        VALUES (?, ?, ?);
        """,
        rows,
    )

    result = pd.read_sql_query(SQLITE_QUERY, con)
    con.close()
    return sorted(result["id"].tolist())


@pytest.mark.parametrize(
    "rows, expected_ids",
    [
        (
            [
                (1, "2015-01-01", 10),
                (2, "2015-01-02", 25),
                (3, "2015-01-03", 20),
                (4, "2015-01-04", 30),
            ],
            [2, 4],
        ),
        (
            [
                (1, "2015-01-01", 10),
                (2, "2015-01-03", 30),
            ],
            [],
        ),
        (
            [
                (1, "2015-01-01", 10),
                (2, "2015-01-02", 10),
                (3, "2015-01-03", 9),
            ],
            [],
        ),
        (
            [
                (3, "2015-01-03", 5),
                (1, "2015-01-01", -2),
                (2, "2015-01-02", -1),
                (4, "2015-01-04", 6),
            ],
            [2, 4],
        ),
        (
            [],
            [],
        ),
    ],
)
def test_rising_temperature_sqlite_and_pandas(rows, expected_ids):
    sql_ids = run_sqlite(rows)

    weather = pd.DataFrame(rows, columns=["id", "recordDate", "temperature"])
    weather = weather.astype({"id": "int64", "temperature": "int64"}, errors="ignore")
    weather["recordDate"] = pd.to_datetime(weather["recordDate"])

    pandas_result = rising_temperature(weather)
    pandas_ids = sorted(pandas_result["id"].tolist())

    assert sql_ids == expected_ids
    assert pandas_ids == expected_ids

Comment on my solution¶

Your SQL logic is correct for PostgreSQL because it joins on the exact previous calendar day.
It is not SQLite syntax because SQLite does not support INTERVAL '1 day'.
Your rejected LAG() solution correctly identifies the main trap: missing dates make previous row different from yesterday.
Your Pandas solution is correct and interview-ready.
Minor improvement: use subtraction directly for readability.

WITH weather_yesterday AS (
    SELECT
        w1.*,
        w2.temperature AS yesterday_temperature
    FROM Weather AS w1
    JOIN Weather AS w2
      ON w1.recordDate = w2.recordDate + INTERVAL '1 day'
)
SELECT
    id
FROM weather_yesterday
WHERE temperature > yesterday_temperature;

-- Wrong Answer: 13/15 testcases passed
-- Don't work because there can be missing days.  So here we're
-- comparing with the last recorded temperature which can be a few
-- days before.
WITH yesterday AS (
    SELECT
        *,
        LAG(temperature) OVER (ORDER BY recordDate) AS yesterday_temp
    FROM Weather
)
SELECT
    id
FROM yesterday AS y
WHERE y.temperature > y.yesterday_temp;

import pandas as pd

def rising_temperature(weather: pd.DataFrame) -> pd.DataFrame:
    df = weather.copy()
    df["yesterday"] = (df["recordDate"] + pd.Timedelta(days=-1))
    merged = (
        df.merge(df, left_on="yesterday", right_on="recordDate", how="inner",
                 suffixes=("", "_yesterday"))
    )
    result = (
        merged.loc[merged["temperature"] > merged["temperature_yesterday"],
                   ["id"]]

    )
    return result