How to combine BigQuery LAST_VALUE() and ARRAY_AGG()

Here is a toy example:

select * 
from(
select 1 as row_num,298807 as id1,104 as id2,'2018-07-10' as date
union all
select 2,298807,104,'2018-08-02'
union all
select 3,298807,104,'2018-08-06'
union all
select 4,298807,104,'2018-08-08'
union all
select 5,298807,104,'2018-08-24'
union all
select 6,298807,104,'2018-09-28'
union all
select 7,298807,104,'2018-10-01'
union all
select 8,298807,104,'2018-10-28'
union all
select 9,298807,300,'2018-10-30'
union all
select 10,298807,104,'2018-11-12'
union all
select 11,298807,300,'2018-11-20'
union all
select 12,298807,104,'2018-11-30'
union all
select 13,298807,104,'2018-12-02'
union all
select 14,298807,104,'2018-12-03') 

For each row, I would like to find the last row of distinct id2 within id1. For example for row #11 the output should be an array of two elements “row #9, row#10”, and for row #14 it is “row #11,row #13”.

here is an example output:

Answer

Below is for BigQuery Standard SQL

#standardSQL
SELECT * EXCEPT(candidates),
  ARRAY_TO_STRING(ARRAY(
    SELECT CAST(MAX(row_num) AS STRING) row_num
    FROM t.candidates
    GROUP BY id2
    ORDER BY row_num
  ), ',') AS output
FROM (
  SELECT *, ARRAY_AGG(STRUCT(id2, row_num)) OVER(win) candidates
  FROM `project.dataset.table` 
  WINDOW win AS (PARTITION BY id1 ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
) t
-- ORDER BY row_num

If to apply to sample data from your question – output is

Row row_num id1     id2 date        output   
1   1       298807  104 2018-07-10       
2   2       298807  104 2018-08-02  1    
3   3       298807  104 2018-08-06  2    
4   4       298807  104 2018-08-08  3    
5   5       298807  104 2018-08-24  4    
6   6       298807  104 2018-09-28  5    
7   7       298807  104 2018-10-01  6    
8   8       298807  104 2018-10-28  7    
9   9       298807  300 2018-10-30  8    
10  10      298807  104 2018-11-12  8,9  
11  11      298807  300 2018-11-20  10,9     
12  12      298807  104 2018-11-30  10,11    
13  13      298807  104 2018-12-02  11,12    
14  14      298807  104 2018-12-03  11,13