title: Insert multiple rows from result of Average by date and id
body: <p>I have a table with 1 result per day like this :</p>
  
  <pre><code>id |  item_id  | date       | amount
  -------------------------------------
  1       1        2019-01-01     1  
  2       1        2019-01-02     2
  3       1        2019-01-03     3
  4       1        2019-01-04     4
  5       1        2019-01-05     5
  6       2        2019-01-01     1
  7       2        2019-01-01     2
  8       2        2019-01-01     3
  9       2        2019-01-01     4
  10      2        2019-01-01     5
  11      3        2019-01-01     1
  12      3        2019-01-01     2
  13      3        2019-01-01     3
  14      3        2019-01-01     4
  15      3        2019-01-01     5 
  </code></pre>
  
  <p>First I was trying to average the column amount for each day.</p>
  
  <pre><code>SELECT 
      x.item_id AS id,avg(x.amount) AS result 
  FROM
      (SELECT 
           il.item_id, il.amount,  
           ROW_NUMBER() OVER (PARTITION BY il.item_id  ORDER BY il.date DESC) rn 
       FROM 
           item_prices il) x 
  WHERE 
      x.rn BETWEEN 1 AND 50 
  GROUP BY 
      x.item_id
  </code></pre>
  
  <p>The result is going to be the following if calculated on 2019-01-05</p>
  
  <pre><code>item_id |  average
     1         3
     2         3
     3         3
  </code></pre>
  
  <p>or, if calculated 2019-01-04 </p>
  
  <pre><code>item_id |  average
     1         2.5
     2         2.5
     3         2.5
  </code></pre>
  
  <p>My goal is to run the Average query , every day that would update the average automatically and insert it in 5th column "average" :</p>
  
  <pre><code>id |  item_id  | date       | amount | average
  5       1        2019-01-05     5        3
  10      2        2019-01-05     5        3
  15      3        2019-01-05     5        3
  </code></pre>
  
  <p>Issue is that every example i can find with Insert the Select they only update one row and they are over another table there is also the most recent date issue...</p>
  
  <p>Can someone point me in the right direction? </p>