How to compare two snaps of schema data in SQL?

I have two tables:

Current_Ranking:

​x
 
id   rank1     202     253     264     17​

Previous_Ranking

 
id rank1   202   263   184   175   5​

I want to get as a result all records in Previous_Ranking that don’t appear in Current_Ranking (means new id) and all records that their rank in Previous_Ranking is not the same as Current_Ranking

So expected result is:

id rank
2   26
3   18
5   5

 
id rank2   263   185   5​

How can I do that? I know I can do:

    SELECT   p.id, p.rank
    FROM     Previous_Ranking p
       LEFT JOIN Current_Ranking c USING (id)
    WHERE    c.id IS NULL

 
    SELECT   p.id, p.rank    FROM     Previous_Ranking p       LEFT JOIN Current_Ranking c USING (id)    WHERE    c.id IS NULL​

This should give me all the new rows. But How I continue from here?

I’m using BigQuery so this can be done with native SQL.

Answer

You may use a left join with two criteria:

SELECT p.id, p.rank
FROM Previous_Ranking p
LEFT JOIN Current_Ranking c
    ON p.id = c.id
WHERE
    c.id IS NULL OR p.rank <> c.rank;

 
SELECT p.id, p.rankFROM Previous_Ranking pLEFT JOIN Current_Ranking c    ON p.id = c.idWHERE    c.id IS NULL OR p.rank <> c.rank;​

Note: RANK is a reserved keyword in many versions of SQL (though apparently not in BigQuery). So, you might want to avoid using RANK as a name for columns and tables.

Advertisement

Answer