title: SQL : automatically fill price between dates
body: <p>I'm trying to write a view from two tables, one referential table that contains products ID and weeks :</p>
  
  <pre><code>+------------+------+
  | Product_id | week |   t1
  +------------+------+
  |          1 |    1 |
  |          2 |    1 |
  |          1 |    2 |
  |          2 |    2 |
  |          1 |    3 |
  |          2 |    3 |
  +------------+------+ etc...
  </code></pre>
  
  <p>the other one contains Products ID, weeks when the product's price changed and the price</p>
  
  <pre><code>+------------+------+-------+
  | Product_id | week | price | t2
  +------------+------+-------+
  |          1 |    1 |    70 |
  |          1 |    2 |    50 |
  |          2 |    2 |    70 |
  |          1 |    4 |    30 |
  |          2 |    4 |    40 |
  +------------+------+-------+
  </code></pre>
  
  <p>I know how to achieve easily this by joining the two tables :</p>
  
  <pre><code>+------------+------+-------+
  | Product_id | week | price | 
  +------------+------+-------+
  |          1 |    1 |    70 |
  |          1 |    2 |    50 |
  |          1 |    3 |       |
  |          1 |    4 |    30 |
  |          1 |    5 |       |
  |          2 |    1 |       |
  |          2 |    2 |    70 |
  |          2 |    3 |       |
  |          2 |    4 |    40 |
  |          2 |    5 |       |
  +------------+------+-------+
  </code></pre>
  
  <p>But my goals would rather be to fill in the gaps and have the price for each week (without creating any new table), as such :</p>
  
  <pre><code>+------------+------+-------+
  | Product_id | week | price |
  +------------+------+-------+
  |          1 |    1 |    70 |
  |          1 |    2 |    50 |
  |          1 |    3 |    50 |
  |          1 |    4 |    30 |
  |          1 |    5 |    30 |
  |          2 |    1 |       |
  |          2 |    2 |    70 |
  |          2 |    3 |    70 |
  |          2 |    4 |    40 |
  |          2 |    5 |    40 |
  +------------+------+-------+ (product 2 isn't sold yet at week 1, so it doesn't have a price).
  </code></pre>
  
  <p>I can't see how I would do  this in SQL. I haven't used PARTITION BY or LAG yet, and it might be what I'm looking for. If anyone can push me in the right direction, I would appreciate it :)</p>