title: Multiply to latest previous values from other tables
body: <p>I got a problem on multiply a value with the other latest (but not the same date) value from other table with the same key.</p>
  
  <p>For ease of understanding I'll explain it below.</p>
  
  <p>Suppose we have two tables <code>variables</code> and <code>coefficients</code>:</p>
  
  <p>table <code>coefficients</code>        </p>
  
  <pre><code>date            key     coeff
  -----------------------------
  2019-06-01      A       1
  2019-06-02      B       2
  2019-06-03      A       3
  2019-06-05      B       4
  2019-06-06      B       5
  2019-06-08      A       6
  </code></pre>
  
  <p>table <code>variables</code>       </p>
  
  <pre><code>date            key     var
  ---------------------------
  2019-06-02      A       7
  2019-06-05      B       8
  2019-06-05      A       9
  2019-06-06      B       10
  2019-06-07      A       11
  2019-06-08      B       12
  2019-06-09      A       13
  </code></pre>
  
  <p>The goal is to  multiply the value on <code>variable</code> column with the nearest previous <code>coeff</code> value.</p>
  
  <p>In the end we'll have result like:</p>
  
  <pre><code>---------------------------
  date            key     val     &lt;-- var *   coeff   
  ---------------------------
  2019-06-02      A       7       &lt;-- 7   *   1       
  2019-06-05      B       16      &lt;-- 8   *   2
  2019-06-05      A       27      &lt;-- 9   *   3
  2019-06-06      B       40      &lt;-- 10  *   4
  2019-06-07      A       33      &lt;-- 11  *   3
  2019-06-08      B       60      &lt;-- 12  *   5
  2019-06-09      A       78      &lt;-- 13  *   6
  </code></pre>
  
  <p>On the <code>variables</code> table, the first row (key <code>A</code>) was dated <code>2019-06-02</code> so the we multiply <code>variables.var</code> with the previous latest (not the same date) <code>coefficients.coeff</code> of <code>A</code>, which is the <code>2019-06-01</code>'s <code>coeff</code>. Same with the second row of <code>B</code>. </p>
  
  <p>I don't know where to start the query since if we <code>join</code> it on each <code>key</code> seems not working.</p>