Database Language: SQL Server
Difficulty: Easy
| 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.
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.
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 |
| id | | -- | | 2 | | 4 |
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).
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');
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:
Runtime: 372ms
Beats: 95.93% as of July 16, 2024
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:
Runtime: 415ms
Beats: 84.42% as of July 16, 2024