Grasping the Concept Through an Example

To simplify our task, I've adapted the original question to fit our earthquake dataset:

Formulate a query to retrieve all IDs where the magnitude of an earthquake exceeds that of the previous day.

The key takeaway here is the need to compare earthquake magnitudes across two different dates. However, since we only have a single table, this situation necessitates the use of a SELF JOIN.

As previously stated, a SELF JOIN involves joining a table to itself using the JOIN keyword. Given that both tables are identical, using table aliases becomes crucial.

The pivotal step in this task lies in how you execute the JOIN operation. Initially, we will join the two tables based on a common column, ID:

SELECT t1.*, t2.*

FROM sql_practice.earthquakes AS t1

JOIN sql_practice.earthquakes AS t2

ON t1.ID = t2.ID

WHERE t1.Magnitude > t2.Magnitude

AND DATEDIFF(t1.Dates, t2.Dates) = 1;

However, this will yield an empty dataset.

Note: Here, `t1` and `t2` represent today's and yesterday's tables, respectively. Thus, when you state `t1.Magnitude > t2.Magnitude`, you're effectively comparing today's magnitude against yesterday's.

Upon closer inspection of the original dataset, you will notice that each ID is linked to a single date-time value and a corresponding magnitude. Therefore, joining the table with itself based on ID will not yield two different magnitudes. Consequently, the condition t1.Magnitude > t2.Magnitude will never hold true, resulting in an empty dataset.

So, what’s the solution?

Instead of the commonly used equality operator =, we need to utilize a non-equality operator (anything from >, <, <=, >=, <>). These types of joins are categorized as Non-Equi Joins.

Let’s reformulate the solution with an adjusted JOIN clause:

SELECT t1.*, t2.*

FROM sql_practice.earthquakes AS t1

JOIN sql_practice.earthquakes AS t2

ON t1.Magnitude > t2.Magnitude;

Now, we're joining the two tables in such a way that today's magnitude is consistently greater than yesterday's. This quickly yields results:

The dates from today's dataset (marked in red) will always exceed those from yesterday's (marked in yellow). However, the difference between the two dates on a single row may not always equal 1; often, it exceeds 1.

At this juncture, we must utilize the DATEDIFF function to ensure the date difference is restricted to just one day:

SELECT t1.*, t2.*

FROM sql_practice.earthquakes AS t1

JOIN sql_practice.earthquakes AS t2

ON t1.Magnitude > t2.Magnitude

AND DATEDIFF(t1.Dates, t2.Dates) = 1;

Now, you can observe that the date difference for every record is precisely one day, as required. To finalize the output, you need only the IDs where the magnitude exceeded that of the day before, which can be simplified as follows:

SELECT t1.ID

FROM sql_practice.earthquakes AS t1

JOIN sql_practice.earthquakes AS t2

ON t1.Magnitude > t2.Magnitude

AND DATEDIFF(t1.Dates, t2.Dates) = 1;

In a similar manner, you can tackle the Leetcode problem utilizing Non-Equi Join and SQL DATE Functions as discussed:

SELECT today.id

FROM Weather AS today

JOIN Weather AS yesterday

ON today.temperature > yesterday.temperature

AND TIMESTAMPDIFF(DAY, yesterday.recordDate, today.recordDate) = 1;

You can also rewrite this query using the DATEDIFF function and validate your results.

That’s it for this guide!

I trust you found this example insightful and have gained a better understanding of how to utilize SQL SELF JOIN, Non-Equi Joins, and DATE Functions collectively. This knowledge is incredibly valuable, as questions surrounding date-time operations and self joins frequently arise in data science job interviews, serving significant purposes in real-world analytics.

If you're interested in exploring limitless stories on Medium, consider becoming a Medium Member to access unlimited content while supporting my work at no extra cost to you.

Don’t forget to subscribe to my email list to stay updated on future articles covering data science guides, tips, and tricks for SQL and Python.

Thank you for your time!