Extrapolate daily historical values from a table that only records when a value changes (Postgresql 9.3)
You could achieve it with usage of correlated subqueries and LATERAL
:
SELECT sub.date, sub.location_id, score
FROM (SELECT * FROM dw_dim_date
CROSS JOIN (SELECT DISTINCT location_id FROM score_history) s
WHERE date >= '2019-01-01'::date) sub
,LATERAL(SELECT score FROM score_history sc
WHERE sc.happened_at::date <= sub.date
AND sc.location_id = sub.location_id
ORDER BY happened_at DESC LIMIT 1) l
,LATERAL(SELECT MIN(happened_at::date) m1, MAX(happened_at::date) m2
FROM score_history sc
WHERE sc.location_id = sub.location_id) lm
WHERE sub.date BETWEEN lm.m1 AND lm.m2
ORDER BY location_id, date;
db<>fiddle demo
How it works:
1) s
(it is cross join of all dates per location_id)
2) l
(selecting score per location)
3) lm
(selecting min/max date per location for filtering)
4) WHERE
filter dates on range that is available, it could be relaxed if needed
I think you can try something like this. The main things I changed are wrapping things in DATE() and using another SO answer for the date finder:
SELECT
dw_dim_date.date,
(
SELECT
score
FROM
score_history
WHERE
DATE(score_history.happened_at) <= dw_dim_date.date
ORDER BY
score_history.happened_at DESC
LIMIT
1
) as last_score
FROM
dw_dim_date
WHERE
dw_dim_date.date >= DATE('2019-01-01')
This uses the SQL method from here to find the nearest past data to the one requested: PostgreSQL return exact or closest date to queried date