Last active
May 30, 2022 15:44
-
-
Save odellt/1e3e01b2c447691f225bba48b14934f4 to your computer and use it in GitHub Desktop.
BQ - Last Value
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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