Big Query - Group By based on Order

Question

I have this data:

WITH test_table AS (
  SELECT '1' number, 'eat' activity UNION ALL
  SELECT '2', 'eat' UNION ALL
  SELECT '3', 'drink' UNION ALL 
  SELECT '4', 'eat' UNION ALL 
  SELECT '5', 'drink' UNION ALL
  SELECT '6', 'drink'
)

select *
from test_table
order by 1

It will be shown as this

I want to group by them not only based on a variable, but also the order. So I expect an output like this:

Basically, if we do group by using common method:

SELECT 
    MAX(number) as number,
    activity
FROM test_table
group by 2

it will be classify them not on order, and will take the max number of variable instead of order which is not what I am looking for, as follow:

What would be the query to get the result I expect?

Answer 1

you need to look in the next row. Use the command lead or lag for these kind of operations.

WITH test_table AS (
  SELECT '1' number, 'eat' activity UNION ALL
  SELECT '2', 'eat' UNION ALL
  SELECT '3', 'drink' UNION ALL 
  SELECT '4', 'eat' UNION ALL 
  SELECT '5', 'drink' UNION ALL
  SELECT '6', 'drink'
)

Select *
from(
select number, activity , lead (activity) over (order by number) as activity_next
from test_table
)
where activity!=activity_next or activity_next is null
order by 1