Sort group members and aggregate with navigation function

Question

I'm trying to group the table below, on columns id and due_month:

id	created_at	due_month	status
1	2021-02-05	2021-02	paused
1	2021-01-31	2021-02	normal
1	2021-01-15	2021-01	normal
2	2021-03-18	2021-03	normal
2	2021-03-07	2021-03	paused
2	2021-03-31	2021-08	normal

then within each group, sort the members on created_at in ascending order, and finally pick the last item's status value with latest created_at date (assumes that created_at never repeat on records with the same id).

Hence the output will look like this:

id	due_month	status
1	2021-01	normal
1	2021-02	paused
2	2021-03	normal
2	2021-08	normal

I tried out query like this but it didn't work (syntax error):

SELECT
  `id`,
  `due_month`,
  LAST_VALUE(`status`) OVER (ORDER BY `created_at`) AS `status`
FROM `some_table`
GROUP BY
  `id`,
  `due_month`
;

Also I know it's possible to join information like MAX(`created_at`) AS latest to the original table, then filter by WHERE created_at = latest to get what's needed, but that doesn't look very efficient.

Any better ideas for writing down this type of logics in BigQuery?

user_5221944 · Answer

Consider below approach

select id, due_month, status
from your_table
where true 
qualify 1 = row_number() over win
window win as (partition by id, due_month order by created_at desc)

if applied to sample data in your question - output is

Sort group members and aggregate with navigation function

1 Answers