title: Extrapolate daily historical values from a table that only records when a value changes (Postgresql 9.3)
body: <p>I have a table that records a row for each time a score for a location has changed.</p>
  
  <p>score_history:</p>
  
  <ul>
  <li>id  int PK (uuid auto incrementing int)</li>
  <li>happened_at timestamp (when the score changed)</li>
  <li>location_id int FK (the location that the value is for)</li>
  <li>score float (the new score)</li>
  </ul>
  
  <p>This was done with an eye on efficiency and being able to simply retrieve a list of changes for a given location and serves that purpose nicely.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>That table looks like</p>
  
  <p>dw_dim_date:</p>
  
  <ul>
  <li>date date PK</li>
  <li>a bunch of other columns like week number, is_us_holiday etc.</li>
  </ul>
  
  <p>So, if I had only 3 records in the score_history table...</p>
  
  <pre class="lang-none prettyprint-override"><code>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
  </code></pre>
  
  <p>The desired output would be:</p>
  
  <pre class="lang-none prettyprint-override"><code>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
  </code></pre>
  
  <p>3 Requirements:</p>
  
  <ol>
  <li>One row per day per location even if there are no score records
  for that day.</li>
  <li>If there are score records for that day the last
  one before midnight should be the score value for the row. In the event of a tie the greater of the two should "win".</li>
  <li>If there are zero score records for that day the score should be the most recent previous score.</li>
  </ol>
  
  <p>I've been chasing my tail through subqueries and window functions.</p>
  
  <p>Because I'm hesitant to post something without something I tried I'll share this trainwreck which produces output but of no meaning...</p>
  
  <pre><code>SELECT dw_dim_date.date,
         (SELECT score 
          FROM score_history 
          WHERE score_history.happened_at::DATE &lt; 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 &gt; '2019-06-01'
  </code></pre>
  
  <p>Grateful for guidance or pointers to other questions to read.</p>