Rising Temperature SQL Challenge - PostgreSQL Solution

1. Introduction

In this blog post, we will explore how to use PostgreSQL to find dates when the temperature was higher than the previous day. This type of query is common in data analysis for time-series data.

Problem

The task is to write a PostgreSQL query that finds all dates with temperatures higher than their previous dates in the Weather table.

Database Table:

Weather Table:

id	recordDate	temperature
1	2015-01-01	10
2	2015-01-02	25

3. PostgreSQL Solution

SELECT w1.id
FROM Weather w1
JOIN Weather w2 ON w1.recordDate = w2.recordDate + INTERVAL '1 day'
WHERE w1.temperature > w2.temperature;

Output:

id
2

Explanation:

1. The query uses a self-join on the Weather table, joining each row (w1) with its previous day (w2).

2. The join condition is that w1's recordDate should be exactly one day ahead of w2's recordDate.

3. The WHERE clause filters the records to only include those where w1's temperature is greater than w2's temperature.

4. This effectively identifies the dates where the temperature was higher than the previous day.