Write an SQL query to find all dates' Id with higher temperatures compared to its previous dates (yesterday). Return the result table in any order.
table name: Weather
DATEADD Function in SQL
DATEADD(interval, number, date)
interval can be second, minute, day, month, year
Self Join in SQL
Self join is similar to other joins in sql. In which a column is required to connect the table(s). But in case of self join instead of connecting to some other tables it will connect to the same table.
Solution Explanation
- To know, temperatures of previous date we are going to use concept of self join.
- Join current date of weather table with previous date of weather table.
DATEADD(day,1, w.recordDate)= w1.recordDate
select w1.id from Weather as w
join Weather as w1 on DATEADD(day,1, w.recordDate)= w1.recordDate
- w is for previous day data
- w1 is for current day data
Apply a where clause to filter for all those rows which is having lower temparature on previous day than current day.
where w.temperature < w1.temperature
Final Solution:
select w1.id from Weather as w
join Weather as w1 on DATEADD(day,1, w.recordDate)= w1.recordDate
where w.temperature < w1.temperature
Output:
SQL Script:
CREATE TABLE [dbo].[Weather](
[id][int] NOT NULL,
[recordDate][date] NULL,
[temperature][int] NULL,
CONSTRAINT [PK_Weather] PRIMARY KEY CLUSTERED
(
[id]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Weather] ([id], [recordDate], [temperature]) VALUES (1, CAST(N'2015-01-01' AS Date), 10)
GO
INSERT [dbo].[Weather] ([id], [recordDate], [temperature]) VALUES (2, CAST(N'2015-01-02' AS Date), 25)
GO
INSERT [dbo].[Weather] ([id], [recordDate], [temperature]) VALUES (3, CAST(N'2015-01-03' AS Date), 20)
GO
INSERT [dbo].[Weather] ([id], [recordDate], [temperature]) VALUES (4, CAST(N'2015-01-04' AS Date), 30)
GO