sqlselectgoogle-bigquerytop-n

How to return max date per month for user

I have following table:

table1

And I would like to have returned maximum threshold date per each month for every user, so my final result should look like that:

table2

I wanted to use analytic function ROW_NUMBER and return maximum number of row but how to do it per month for each user? Is there any simpler way to do it in BigQuery?

Solution

  • You can partition the row_number by the user and the month, and then take the first one for each:

    SELECT user_id, threshold_date, net_deposists_usd
FROM   (SELECT user_id, threshold_date, net_deposists_usd,
               ROW_NUMBER () OVER (PARTITION BY user_id, EXTRACT (MONTH FROM threshold_date)
                                   ORDER BY net_deposists_usd DESC) AS rk
        FROM   mytable)
WHERE  rk = 1