title: Presto Window Function - (sum over partition by)
body: <p>I have the following Presto 2 tables, one storing budget information by client &amp; day, and the other one storing spend information by client &amp; day</p>
  <pre><code>select day, client_id, budget_id, budget_period, budget_amount
  from budget_table
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>day</th>
  <th>client_id</th>
  <th>budget_id</th>
  <th>budget_period</th>
  <th>budget_amount</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-02-27</td>
  <td>1</td>
  <td>1-1</td>
  <td>daily</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2021-02-28</td>
  <td>1</td>
  <td>1-1</td>
  <td>daily</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2021-03-01</td>
  <td>1</td>
  <td>1-1</td>
  <td>daily</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2021-03-02</td>
  <td>1</td>
  <td>1-1</td>
  <td>daily</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2021-03-03</td>
  <td>1</td>
  <td>1-1</td>
  <td>daily</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2021-03-04</td>
  <td>1</td>
  <td>1-2</td>
  <td>monthly</td>
  <td>500</td>
  </tr>
  <tr>
  <td>2021-03-05</td>
  <td>1</td>
  <td>1-2</td>
  <td>monthly</td>
  <td>500</td>
  </tr>
  <tr>
  <td>2021-03-06</td>
  <td>1</td>
  <td>1-2</td>
  <td>monthly</td>
  <td>500</td>
  </tr>
  <tr>
  <td>2021-02-27</td>
  <td>2</td>
  <td>2-1</td>
  <td>monthly</td>
  <td>400</td>
  </tr>
  <tr>
  <td>2021-02-28</td>
  <td>2</td>
  <td>2-1</td>
  <td>monthly</td>
  <td>400</td>
  </tr>
  <tr>
  <td>2021-03-01</td>
  <td>2</td>
  <td>2-1</td>
  <td>monthly</td>
  <td>400</td>
  </tr>
  <tr>
  <td>2021-03-02</td>
  <td>2</td>
  <td>2-1</td>
  <td>monthly</td>
  <td>400</td>
  </tr>
  <tr>
  <td>2021-03-03</td>
  <td>2</td>
  <td>2-2</td>
  <td>one_time</td>
  <td>1000</td>
  </tr>
  <tr>
  <td>2021-03-04</td>
  <td>2</td>
  <td>2-2</td>
  <td>one_time</td>
  <td>1000</td>
  </tr>
  <tr>
  <td>2021-03-05</td>
  <td>2</td>
  <td>2-2</td>
  <td>one_time</td>
  <td>1000</td>
  </tr>
  <tr>
  <td>2021-03-06</td>
  <td>2</td>
  <td>2-2</td>
  <td>one_time</td>
  <td>1000</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code>select day, client_id, spend
  from spend_table
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>day</th>
  <th>client_id</th>
  <th>spend</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-02-27</td>
  <td>1</td>
  <td>8</td>
  </tr>
  <tr>
  <td>2021-02-28</td>
  <td>1</td>
  <td>9</td>
  </tr>
  <tr>
  <td>2021-03-01</td>
  <td>1</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2021-03-02</td>
  <td>1</td>
  <td>7</td>
  </tr>
  <tr>
  <td>2021-03-03</td>
  <td>1</td>
  <td>6</td>
  </tr>
  <tr>
  <td>2021-03-04</td>
  <td>1</td>
  <td>16</td>
  </tr>
  <tr>
  <td>2021-03-05</td>
  <td>1</td>
  <td>19</td>
  </tr>
  <tr>
  <td>2021-03-06</td>
  <td>1</td>
  <td>18</td>
  </tr>
  <tr>
  <td>2021-02-27</td>
  <td>2</td>
  <td>13</td>
  </tr>
  <tr>
  <td>2021-02-28</td>
  <td>2</td>
  <td>15</td>
  </tr>
  <tr>
  <td>2021-03-01</td>
  <td>2</td>
  <td>14</td>
  </tr>
  <tr>
  <td>2021-03-02</td>
  <td>2</td>
  <td>15</td>
  </tr>
  <tr>
  <td>2021-03-03</td>
  <td>2</td>
  <td>20</td>
  </tr>
  <tr>
  <td>2021-03-04</td>
  <td>2</td>
  <td>25</td>
  </tr>
  <tr>
  <td>2021-03-05</td>
  <td>2</td>
  <td>18</td>
  </tr>
  <tr>
  <td>2021-03-06</td>
  <td>2</td>
  <td>27</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Below is desired output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>day</th>
  <th>client_id</th>
  <th>budget_id</th>
  <th>budget_period</th>
  <th>budget_amount</th>
  <th>spend</th>
  <th>spend_over_period</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-02-27</td>
  <td>1</td>
  <td>1-1</td>
  <td>daily</td>
  <td>10</td>
  <td>8</td>
  <td>8</td>
  </tr>
  <tr>
  <td>2021-02-28</td>
  <td>1</td>
  <td>1-1</td>
  <td>daily</td>
  <td>10</td>
  <td>9</td>
  <td>9</td>
  </tr>
  <tr>
  <td>2021-03-01</td>
  <td>1</td>
  <td>1-1</td>
  <td>daily</td>
  <td>10</td>
  <td>10</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2021-03-02</td>
  <td>1</td>
  <td>1-1</td>
  <td>daily</td>
  <td>10</td>
  <td>7</td>
  <td>7</td>
  </tr>
  <tr>
  <td>2021-03-03</td>
  <td>1</td>
  <td>1-1</td>
  <td>daily</td>
  <td>10</td>
  <td>6</td>
  <td>6</td>
  </tr>
  <tr>
  <td>2021-03-04</td>
  <td>1</td>
  <td>1-2</td>
  <td>monthly</td>
  <td>500</td>
  <td>16</td>
  <td>16</td>
  </tr>
  <tr>
  <td>2021-03-05</td>
  <td>1</td>
  <td>1-2</td>
  <td>monthly</td>
  <td>500</td>
  <td>19</td>
  <td>35</td>
  </tr>
  <tr>
  <td>2021-03-06</td>
  <td>1</td>
  <td>1-2</td>
  <td>monthly</td>
  <td>500</td>
  <td>18</td>
  <td>53</td>
  </tr>
  <tr>
  <td>2021-02-27</td>
  <td>2</td>
  <td>2-1</td>
  <td>monthly</td>
  <td>400</td>
  <td>13</td>
  <td>13</td>
  </tr>
  <tr>
  <td>2021-02-28</td>
  <td>2</td>
  <td>2-1</td>
  <td>monthly</td>
  <td>400</td>
  <td>15</td>
  <td>28</td>
  </tr>
  <tr>
  <td>2021-03-01</td>
  <td>2</td>
  <td>2-1</td>
  <td>monthly</td>
  <td>400</td>
  <td>14</td>
  <td>14</td>
  </tr>
  <tr>
  <td>2021-03-02</td>
  <td>2</td>
  <td>2-1</td>
  <td>monthly</td>
  <td>400</td>
  <td>15</td>
  <td>29</td>
  </tr>
  <tr>
  <td>2021-03-03</td>
  <td>2</td>
  <td>2-2</td>
  <td>one_time</td>
  <td>1000</td>
  <td>20</td>
  <td>20</td>
  </tr>
  <tr>
  <td>2021-03-04</td>
  <td>2</td>
  <td>2-2</td>
  <td>one_time</td>
  <td>1000</td>
  <td>25</td>
  <td>45</td>
  </tr>
  <tr>
  <td>2021-03-05</td>
  <td>2</td>
  <td>2-2</td>
  <td>one_time</td>
  <td>1000</td>
  <td>18</td>
  <td>63</td>
  </tr>
  <tr>
  <td>2021-03-06</td>
  <td>2</td>
  <td>2-2</td>
  <td>one_time</td>
  <td>1000</td>
  <td>27</td>
  <td>90</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I have tried</p>
  <pre><code>select s.day, 
         s.client_id, 
         b.budget_id, 
         b.budget_period, 
         b.budget_amount,
         s.spend,
         case when b.budget_period = 'daily' then s.spend
              when b.budget_period = 'monthly' then sum(s.spend) over (partition by b.budget_id, month(date(s.day))) 
              when as spend_over_period = 'one_time' then sum(s.spend) over (partition by b.budget_id)
         end as budget_over_period
  from spend_table as s
      join budget_table as b
          on s.day = b.day
          and s.client_id = b.client_id 
      group by 1,2,3,4,5,6
  </code></pre>
  <p>But, I get u'EXPRESSION_NOT_AGGREGATE' error.
  Does anybody know how to query to get the desired output in Presto?</p>