BQ Queries

bq-queries.md

Get Latest application_status's, with backfill

SELECT * EXCEPT (last)
FROM (
    SELECT application_status_id,
    customer_id,
    application_submission_id,
    LAST_VALUE(cpf_check_status IGNORE NULLS) OVER (
        PARTITION BY application_submission_id
        ORDER BY occurred_at ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as cpf_check_status,
    LAST_VALUE(selfie_check_status IGNORE NULLS) OVER (
        PARTITION BY application_submission_id
        ORDER BY occurred_at ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as selfie_check_status,
    LAST_VALUE(screening_status IGNORE NULLS) OVER (
        PARTITION BY application_submission_id
        ORDER BY occurred_at ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as screening_status,
    LAST_VALUE(overall_status IGNORE NULLS) OVER (
        PARTITION BY application_submission_id
        ORDER BY occurred_at ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as overall_status,
    stage_type,
    stage_id,
    application_revision,
    identity_revision,
    occurred_at,
    inserted_at,
    ROW_NUMBER() OVER(
        PARTITION BY application_submission_id
        ORDER BY occurred_at DESC
    ) AS last,
    FROM `toms-bigquery-testing.testing_dataset.1648821481190_testing_table`
)
WHERE last = 1