How can one calculate the age over multiple dates for customers where not all dates are populated?
Thinking of the problem, I am trying to get the minimum age in a temporary table and then use this table to get to a final table where an age exists for each year for each customer_id.
I know how to get to the min_age_table with each person's earliest recorded age and date. I am not sure how to use this to generate a table with each customer's age going sequentially backwards and forwards as shown in the figure below.
I have set up a minimum working example to try to implement this in the big-query SQL UI.
-- CREATE OR REPLACE TABLE `dataset_id.project_id.example_table`
WITH original_table
AS
(SELECT 'a' as customer_id, '2020-11-01' as snapshot_date, 20 as age UNION ALL
SELECT 'a', '2020-12-01', 21 UNION ALL
SELECT 'b', '2020-09-01', 25 UNION ALL
SELECT 'b', '2020-10-01', 25 UNION ALL
SELECT 'c', '2020-01-01', 45)
-- select customer_id, min_snapshot_date , age for min table
SELECT
--original_table.customer_id,
MIN(original_table.snapshot_date) AS min_snapshot_date,
--original_table.age
FROM original_table
-- use min table to get an age for 3 years (will need to be able to increase both ways)