Leetcode: Rising Temperature(SQL)

This question is mainly for SQL practice and it has to deal with the rising temperatures. We want to return the result table in any order. We want to write a SQL query to find the ID with higher temperatures compared to its previous states.

So we basically have the temperature for every given date just one entry per dat and an id, and want to find id higher than temperature the previous day. We output if we have a higher temperature today vs yesterday. Here are some examples:

Usually if you see something like that, before you want to get that information from a different row. We want to compare the previous day's temperature to a current day's terrible, and these are in separate rows, in order to get that into a single row, we need to use a self-join, we could just use a WHERE condition in the case of the same row. 

So we do 

This now joins the weather which is the day before, etc. 

This is what the join does: 

{"headers": ["Id", "RecordDate", "Temperature", "Id", "RecordDate", "Temperature"], "values": [[1, "2015-01-01", 10, 2, "2015-01-02", 25], [2, "2015-01-02", 25, 3, "2015-01-03", 20], [3, "2015-01-03", 20, 4, "2015-01-04", 30]]}

SELECT w2.id FROM Weather w1 JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = -1 AND w2.temperature > w1.temperature;

We just make that part of the join condition and filter on that if we join. We used self join. That's the final answer to the question.

Let's have a database. The martian people are a list of other people. If you are living on Mars, you are no longer an earthling - you're a Martian. 

We want to Join tTables together by the Base Id. 

Here's a visualization on how to join the table:

We will figure out whether to leave data out with no data, or something like that. 

Here's an example:

SELECT * 

FROM martian AS m

INNER JOIN base as b

ON m.base_id = b.base_id;

How we have all the columns from martian table and base table regardless, and we can now update this query to return only the column we need for the report. 

We will call martian the left table and base the right table. The ON clause specifies how the rows from the 2 tables will be connected. 

An INNER join will only return connected rows when there is a matching base id in both tables. A LEFT join returns all connected rows and all unconnected rows from the left table. RIGHT join returns all connected rows and unconnected rows from the right table. FULL join joins both connected and unconnected rows on both of the sides.