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