I'm trying to output the data in a very redundant format to help load it into a rigid external system. The external system expects a row for each location * every date. The goal is to represent the last score value for each location for each date. So if the score changed 3 times in a given date only the score closest to midnight would be considered that locations closing score for the day. I imagine this is similar to the challenge of creating a close of business inventory level fact table.
I have a handy star schema style date dimension table which has a row for every date fully covering this sample period and well into the future.
That table looks like
So, if I had only 3 records in the score_history table...
1, 2019-01-01:10:13:01, 100, 5.0 2, 2019-01-05:20:00:01, 100, 5.8 3, 2019-01-05:23:01:22, 100, 6.2
The desired output would be:
2019-01-01, 100, 5.0 2019-01-02, 100, 5.0 2019-01-03, 100, 5.0 2019-01-04, 100, 5.0 2019-01-05, 100, 6.2
SELECT dw_dim_date.date, (SELECT score FROM score_history WHERE score_history.happened_at::DATE < dw_dim_date.date OR score_history.happened_at::DATE = dw_dim_date.date ORDER BY score_history.id desc limit 1) as last_score FROM dw_dim_date WHERE dw_dim_date.date > '2019-06-01'