max date value from one column in comparison to another date field partitioned by id SQL BigQuery

Question

I need to create a custom_key field that is a concat of date plus id columns. Each of the custom_key should hold a value in another column. This value is a max date from last_edited_date partitioned by id AND could not be later than date value itself. So when the code hits row 2, for instance, it needs to loop over the values in last_edited_date and pick the latest date available per id for this particular row and that is also not later than the row value of column date itself - and that is the tricky part. Table below:

date              last_edited_date       id 
2022-06-30         2022-06-27           13b 
2022-06-30         2022-06-30           13b
2022-06-29         2022-06-30           13b 
2022-06-29         2022-06-29           13b
2022-06-18         2018-05-06           74c
2022-06-18         2021-05-06           74c 
2022-06-30         2018-05-06           74c

Desired output is:

custom_key         custom_date 
2022-06-30_13b     2022-06-30
2022-06-29_13b     2022-06-29
2022-06-18_74b     2021-05-06
2022-06-30_74b     2021-05-06

I have tried:

SELECT
    CONCAT(date, '_', id) as custom_key,
    MAX(last_edited_date) AS custom_date
FROM
    `my_table`  
WHERE
     last_edited_date <= date
GROUP BY 1

Output is:

custom_key         custom_date 
2022-06-30_13b     2022-06-30
2022-06-29_13b     2022-06-29
2022-06-18_74b     2021-05-06
2022-06-30_74b     2018-05-06

Answer 1

One of possible option would be:

SELECT date || '_' || id AS custom_key, MAX(last_edited_date) AS custom_date
  FROM (
    SELECT id, ARRAY_AGG(DISTINCT date) dates, ARRAY_AGG(DISTINCT last_edited_date) last_edited_dates
      FROM my_table
     GROUP BY 1
  ), UNNEST(dates) date, UNNEST(last_edited_dates) last_edited_date
 WHERE date >= last_edited_date
 GROUP BY 1;