How can I return one single row for each customer based on the latest date in BigQuery?

Question

I have a table which contains data in the following structure.

customer_id;   status;   date
1;             1;       01-01-2019
1;            3;       01-02-2019
2;             4;        01-02-2019
2;             3;        01-03-2019

What I want to return is:

customer_id;   status;   date
1;            3;       01-02-2019
2;             3;        01-03-2019

So far I have worked with a max statement in the select clause for the date.

select    distinct customer_id,
          status,
          max(date) as date_max
from *table*
group by customer_id

This returns the error: Unexpected keyword GROUP

Hopefully you can help! Kind regards.

Answer 1

You want to filter the data, not aggregate it. Here is one method you can use:

select t.*
from t
where t.date = (select max(t2.date)
                from t t2
                where t2.customer_id = t.customer_id
               );

In BigQuery, you can also do:

select t.* except (seqnum)
from (select t.*, row_number() over (partition by customer_id order by date desc) as seqnum
      from t
     ) t;

Or, if you want to use aggregation:

select as value array_agg(t order by date desc limit 1)[offset(1)]
from t
group by customer_id;

Answer 2

This query should do it:

select t.customer_id, t.status, t.date
from YourTable t
inner join (select customer_id, max(date) as date
            from YourTable 
            group by customer_id) a
on (t.customer_id= a.customer_id and t.date = a.date)