Search code examples
sqlgoogle-bigquerydata-analysis

How to compare rows after a particular column in BigQuery SQL?

SQL Gurus, I have got a problem and I have no idea how to solve it. I have a table like this in BigQuery:

id       ga_id        first_date     second_date
A        B            2023-08-01     2023-09-02

And I want to compare row like this:

id       ga_id        dates          rang
A        B            2023-08-01     1
A        B            2023-09-02     2

So how can I query this?

I'd be glad if you help me!

Solution

  • If you only need to compare two dates that are located in the same row :

    SELECT id, ga_id, dates, 
       RANK() OVER(PARTITION BY id, ga_id ORDER BY dates DESC) AS rang
FROM (
    SELECT id, ga_id, first_date AS dates
    FROM your_table
    UNION ALL
    SELECT id, ga_id, second_date AS dates
    FROM your_table
) AS subquery;