Skip to content

Instantly share code, notes, and snippets.

@odellt
Last active May 30, 2022 15:44
Show Gist options
  • Save odellt/1e3e01b2c447691f225bba48b14934f4 to your computer and use it in GitHub Desktop.
Save odellt/1e3e01b2c447691f225bba48b14934f4 to your computer and use it in GitHub Desktop.
BQ - Last Value
-- Demonstrates using `LAST_VALUE` to replace null values with the last defined value
-- Simulated data is for 2 applications following the happy path
-- It might make sense to separate the `overall_status` value from the `SCREENING_CHECK` status record
WITH application_status AS (
SELECT
'abc' as application_status_id,
'xyz' as customer_id,
'123' as application_submission_id,
null as cpf_check_status,
null as selfie_check_status,
null as screening_status,
'IN_PROGRESS' as overall_status,
'DETAILS_SUBMISSION' as stage_type,
TIMESTAMP '2022-01-01 12:00:00' as occurred_at
UNION ALL
SELECT
'abc',
'xyz',
'123',
'IN_PROGRESS',
null,
null,
null,
'CPF_DUPLICATE_CHECK',
TIMESTAMP '2022-01-01 12:01:00'
UNION ALL
SELECT
'abc',
'xyz',
'123',
'IN_PROGRESS',
null,
null,
null,
'CPF_DUPLICATE_CHECK',
TIMESTAMP '2022-01-01 12:02:00'
UNION ALL
SELECT
'abc',
'xyz',
'123',
'AUTO:APPROVED',
null,
null,
null,
'CPF_VALIDATION_CHECK',
TIMESTAMP '2022-01-01 12:03:00'
UNION ALL
SELECT
'abc',
'xyz',
'123',
null,
'IN_PROGRESS',
null,
null,
'SELFIE_CHECK',
TIMESTAMP '2022-01-01 12:04:00'
UNION ALL
SELECT
'abc',
'xyz',
'123',
null,
'AUTO:APPROVED',
null,
null,
'SELFIE_CHECK',
TIMESTAMP '2022-01-01 12:05:00'
UNION ALL
SELECT
'abc',
'xyz',
'123',
null,
null,
'AUTO:APPROVED',
'AUTO:APPROVED',
'SCREENING_CHECK',
TIMESTAMP '2022-01-01 12:06:00'
UNION ALL
SELECT
'def',
'uvw',
'456',
null,
null,
null,
'IN_PROGRESS',
'DETAILS_SUBMISSION' ,
TIMESTAMP '2022-01-01 12:00:00'
UNION ALL
SELECT
'def',
'uvw',
'456',
'IN_PROGRESS',
null,
null,
null,
'CPF_DUPLICATE_CHECK',
TIMESTAMP '2022-01-01 12:01:00'
UNION ALL
SELECT
'def',
'uvw',
'456',
'IN_PROGRESS',
null,
null,
null,
'CPF_DUPLICATE_CHECK',
TIMESTAMP '2022-01-01 12:02:00'
UNION ALL
SELECT
'def',
'uvw',
'456',
'AUTO:APPROVED',
null,
null,
null,
'CPF_VALIDATION_CHECK',
TIMESTAMP '2022-01-01 12:03:00'
UNION ALL
SELECT
'def',
'uvw',
'456',
null,
'IN_PROGRESS',
null,
null,
'SELFIE_CHECK',
TIMESTAMP '2022-01-01 12:04:00'
UNION ALL
SELECT
'def',
'uvw',
'456',
null,
'AUTO:APPROVED',
null,
null,
'SELFIE_CHECK',
TIMESTAMP '2022-01-01 12:05:00'
UNION ALL
SELECT
'def',
'uvw',
'456',
null,
null,
'AUTO:APPROVED',
'AUTO:APPROVED',
'SCREENING_CHECK',
TIMESTAMP '2022-01-01 12:06:00'
)
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,
occurred_at,
ROW_NUMBER() OVER(
PARTITION BY application_submission_id
ORDER BY occurred_at DESC
) AS last,
FROM `application_status`
)
WHERE last = 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment