Temperature Fluctuations

#analystbuilder

Write a query to find all dates with higher temperatures compared to the previous dates (yesterday).

Order dates in ascending order.

table name: temperatures

Solution Explanation: We are going to use self join concept where a table join to itself. temperature table as t and temperature table as t2 on column t.date = t2.prevDate.

select t.*,t2.* from temparatures as t 
join temparatures as t2 on t.date = dateadd(day,-1,t2.date)
order by t2.date

DATEADD(interval,number,date)

interval: values could be one of three day, month and year.

number: will how much day, month or year you want to add to the particular date.

date: the actual date value in which you want to add days, months or years.

Final Solution:

select t2.date from temparatures as t 
join temparatures as t2 on t.date = dateadd(day,-1,t2.date)
where t.temparature < t2.temparature
order by t2.date

Final Output:

SQL Script:

USE [AnalystBuilder]
GO
/****** Object:  Table [dbo].[temparatures]    Script Date: 04-02-2024 22:38:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[temparatures](
	[date] [datetime] NULL,
	[temparature] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[temparatures] ([date], [temparature]) VALUES (CAST(N'2022-01-01T00:00:00.000' AS DateTime), 65)
GO
INSERT [dbo].[temparatures] ([date], [temparature]) VALUES (CAST(N'2022-01-02T00:00:00.000' AS DateTime), 70)
GO
INSERT [dbo].[temparatures] ([date], [temparature]) VALUES (CAST(N'2022-01-03T00:00:00.000' AS DateTime), 55)
GO
INSERT [dbo].[temparatures] ([date], [temparature]) VALUES (CAST(N'2022-01-04T00:00:00.000' AS DateTime), 58)
GO
INSERT [dbo].[temparatures] ([date], [temparature]) VALUES (CAST(N'2022-01-05T00:00:00.000' AS DateTime), 90)
GO
INSERT [dbo].[temparatures] ([date], [temparature]) VALUES (CAST(N'2022-01-06T00:00:00.000' AS DateTime), 88)
GO
INSERT [dbo].[temparatures] ([date], [temparature]) VALUES (CAST(N'2022-01-07T00:00:00.000' AS DateTime), 76)
GO
INSERT [dbo].[temparatures] ([date], [temparature]) VALUES (CAST(N'2022-01-08T00:00:00.000' AS DateTime), 82)
GO
INSERT [dbo].[temparatures] ([date], [temparature]) VALUES (CAST(N'2022-01-09T00:00:00.000' AS DateTime), 88)
GO
INSERT [dbo].[temparatures] ([date], [temparature]) VALUES (CAST(N'2022-01-10T00:00:00.000' AS DateTime), 72)
GO