I have a table in BigQuery with 2 dates, the reception date and the date load date. I need to set a mark on the last LOAD_DATE before the RECEPCION_DATE and another mark, on the first load of the same day. I've been trying with Windows Functions, but it is not working. Any idea about how this can be done?
Thanks!
RECEPTION_DATE LOAD_DATE LastLoadBeforeDate FirstLoadOnDate
23-02-2024 25-02-2024 8:07 0 0
23-02-2024 24-02-2024 16:06 0 0
23-02-2024 24-02-2024 12:07 0 0
23-02-2024 24-02-2024 8:07 0 0
23-02-2024 23-02-2024 16:06 0 0
23-02-2024 23-02-2024 12:07 0 0
23-02-2024 23-02-2024 8:07 0 1
23-02-2024 22-02-2024 16:06 1 0
23-02-2024 22-02-2024 12:07 0 0
23-02-2024 22-02-2024 8:07 0 0
23-02-2024 21-02-2024 16:06 0 0
23-02-2024 21-02-2024 12:07 0 0