How can I select the most recent unique record based on another column for each ID in SQL?

user_11792040 0
Viewed 216

So I'm having the following issue, using Snowflake SQL.

I have a table with many columns, including User_ID, Event, USD_Amount, Date.

There are many duplicate columns, and I am thus trying to write a query which will return all the columns in this table, but only only one (most recent) row for each Event of each User_ID.

For example:

User_ID Event Date USD_Amount ....
432980 Closed Account 2021-01-30 15:30:10 ... ....
434092 Logged In 2021-01-30 14:20:15 ... ....
432980 Created Account 2021-01-20 11:45:25 ... ....
432980 Created Account 2021-01-20 11:45:25 ... ....
434092 Created Account 2021-01-19 10:30:05 ... ....
434092 Created Account 2021-01-19 10:30:05 ... ....

I'd want to return:

User_ID Event Date USD_Amount ....
432980 Closed Account 2021-01-30 15:30:10 ... ....
434092 Logged In 2021-01-30 14:20:15 ... ....
432980 Created Account 2021-01-20 11:45:25 ... ....
434092 Created Account 2021-01-19 10:30:05 ... ....
491831 Logged Out 2021-01-19 10:10:00 ... ....
491831 Created Account 2021-01-18 20:18:40 ... ....

I haven't found a similar question on StackOverflow and have only figured out solutions for this, such as using a partition or a sub-select when selecting the most recent row for each User_ID, not taking into account Event or another column. I am not sure how to do this taking into account more than just the User_ID and Date.

Thanks in advance!

snowflake-cloud-data-platform sql
1 Answers

QUALIFY allows you to run a filter after all the other stages of the WHERE/GROUPBY have completed. So pairing that with ROW_NUMBER() partitioned over the partitions, and order in descending, so the newest date is the first, we can the "select to keep" only that row:

SELECT *
FROM table_name
QUALIFY row_number() over (partition by User_ID, Event order by date desc) = 1;

Which is the same as Lemon's second example thus WHERE rank = 1, but more compact.

user_439920
Related