What is the BigQuery equivalent of PostgreSQL's `DISTINCT ON`?

user_5531059 0
Viewed 60

I am migrating some queries from PostgreSQL dialect over to BigQuery. One nice pattern in PostgreSQL is DISTINCT ON (key), which returns the first row for every key based on the sequence as defined in the ORDER BY section.

Is there a similar pattern I can use in BigQuery that would avoid wrapping the query as a CTE?

PostgreSQL query:

SELECT
    DISTINCT ON (user_id)
    user_id,
    updated_on,
    net_change
FROM
    table_name
ORDER BY
    user_id, updated_on DESC
google-bigquery distinct-on window-functions distinct
1 Answers

The BigQuery equivalent of the PostgreSQL dialect's DISTINCT ON (key) is the QUALIFY clause with a window function:

SELECT
    user_id,
    updated_on,
    net_change
FROM
    table_name
QUALIFY
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_on DESC) = 1
user_55310590
Related