LeetCode 197 - Rising Temperature

Database Language: SQL Server

Difficulty: Easy

Problem Description

Input

Table: Weather

| Column Name   | Type    |
| ------------- | ------- |
| id            | int     |
| recordDate    | date    |
| temperature   | int     |

`id` is the column with unique VALUES for this table. There are no different rows with the same recordDate. This table contains information about the temperature on a certain day.

Requirement

Write a solution to find all dates' Id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

The result format is in the following example.

Examples

Example 1

Input

Weather table:

| 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  |
Explanation:

In 2015-01-02, the temperature was higher than the previous day (10 -> 25).

In 2015-01-04, the temperature was higher than the previous day (20 -> 30).

SQL Schema

CREATE TABLE [dbo].[Weather] (id INT PRIMARY KEY, recordDate DATE, temperature INT);

TRUNCATE TABLE [dbo].[Weather];
INSERT INTO [dbo].[Weather] (id, recordDate, temperature) VALUES ('1', '2015-01-01', '10');
INSERT INTO [dbo].[Weather] (id, recordDate, temperature) VALUES ('2', '2015-01-02', '25');
INSERT INTO [dbo].[Weather] (id, recordDate, temperature) VALUES ('3', '2015-01-03', '20');
INSERT INTO [dbo].[Weather] (id, recordDate, temperature) VALUES ('4', '2015-01-04', '30');

Solution

Given that there is only 1 table provided and that the question requires to compare 2 rows in the same table, this means that a SELF JOIN will be involved. A SELF JOIN is a type of join where a table is joined with itself. To join a table with itself, an alias to the table needs to be used to differentiate it when the table is being used to identify the temperature yesterday (`Weather AS yesterday`) and when the table is being used to identify the temperature today (`Weather AS today`).

Since we are only interested on the temperature of 2 consecutive days, an INNER JOIN will be used between the weather `yesterday` and the weather `today` joining on the `recordDate` wherein 1 day is subtracted to `recordDate` `today` and compared against the `recordDate` `yesterday`:

FROM Weather AS yesterday INNER JOIN Weather AS today
  ON DATEADD(DAY, -1, today.recordDate) = yesterday.recordDate

The `DATEADD` function, a function that adds a number (a signed integer) to a `datepart` of an input date, and returns a modified date/time value, is included in the query because the `recordDate` column is of `DATE` datatype. If the `DATEADD` function was not used and instead replaced with a straight subtraction operator:

FROM Weather AS yesterday INNER JOIN Weather AS today
  ON today.recordDate - 1 = yesterday.recordDate

The following error will be encountered:

Query 1 ERROR: Msg: 206, Line 1, State: 2, Level: 16
Operand type clash: date is incompatible with int

To continue, since we are only interested on today's temperature that is higher than yesterday's temperature, a condition will be added to the query to address this requirement:

yesterday.temperature < today.temperature

This condition can either be added as part of the INNER JOIN condition or in the WHERE clause:

Part of the INNER JOIN:

FROM Weather AS yesterday INNER JOIN Weather AS today
  ON DATEADD(DAY, -1, today.recordDate) = yesterday.recordDate AND
     yesterday.temperature < today.temperature

Part of the WHERE clause:

FROM Weather AS yesterday INNER JOIN Weather AS today
  ON DATEADD(DAY, -1, today.recordDate) = yesterday.recordDate
WHERE yesterday.temperature < today.temperature

Lastly, the requirement wants to return just the `id` for all rows with higher temperatures compared to yesterday's temperature, so the `id` of `today` will be returned instead of the `id` of `yesterday`:

SELECT today.id

Putting this all together yields the following queries:

# Final Solution Query
SELECT today.id
FROM Weather yesterday INNER JOIN Weather today
  ON DATEADD(DAY, -1, today.recordDate) = yesterday.recordDate AND
     yesterday.temperature < today.temperature

or

# Alternate Solution Query
SELECT today.id
FROM Weather yesterday INNER JOIN Weather today
  ON DATEADD(DAY, -1, today.recordDate) = yesterday.recordDate
WHERE yesterday.temperature < today.temperature

The query plan generated by both of these queries are the same suggesting that these 2 queries are going to be executed the same way internally:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([today].[temperature], [Expr1002]))
    |--Compute Scalar(DEFINE:([Expr1002]=dateadd(day,(-1),[leetcode].[dbo].[Weather].[recordDate]
       as [today].[recordDate])))
      |--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Weather].[PK_Weather] AS [today]))
    |--Index Spool(SEEK:([yesterday].[recordDate]=[Expr1002] AND
       [yesterday].[temperature] < [leetcode].[dbo].[Weather].[temperature] as [today].[temperature]))
          |--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Weather].[PK_Weather] AS [yesterday]))

Here's the fastest runtime for this query:

One other alternative answer that does not use the `DATEADD` function is to convert the `recordDate` into a `DATETIME` data type and use the subtraction operator:

/* Alternative Solution */
SELECT today.id
FROM Weather yesterday INNER JOIN Weather today
 ON CAST(today.recordDate AS DATETIME) - 1 = yesterday.recordDate AND
    yesterday.temperature < today.temperature

The query plan for this alternative query is as follows:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([today].[temperature], [Expr1002]))
      |--Compute Scalar(DEFINE:([Expr1002]=CONVERT(datetime,[dbo].[Weather].[recordDate] as
         [today].[recordDate],0)-'1900-01-02 00:00:00.000'))
    | |--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Weather].[PK_Weather] AS [today]))
    |--Index Spool(SEEK:([yesterday].[recordDate]=[Expr1002] AND
       [yesterday].[temperature] < [dbo].[Weather].[temperature] as [today].[temperature]))
          |--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Weather].[PK_Weather] AS [yesterday]))

And the fastest runtime for this query is as follows:

Related Articles: