title: SUM distinct inside window function
body: <p>I have a data structure similar to this:</p>
  <pre><code>CREATE TABLE some_table (
      dude_id INTEGER, 
      main_date TIMESTAMP, 
      how_many INTEGER, 
      how_much NUMERIC(5,2), 
      their_ids INTEGER[]
  )
  </code></pre>
  <p>This is the query I've got so far</p>
  <pre><code>SELECT
      dude_id,
      main_date,
      how_many,
      how_much,
      their_ids,
      SUM(how_many) OVER (PARTITION BY dude_id ORDER BY main_date) AS count_stuff_WRONG,
      SUM(how_much) OVER (PARTITION BY dude_id ORDER BY main_date) AS cumulative_sum_WRONG
  FROM some_table
  </code></pre>
  <p>This is the result I'm trying to achieve:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>dude_id</th>
  <th>main_date</th>
  <th>how_many</th>
  <th>how_much</th>
  <th>their_ids</th>
  <th>count_stuff_EXPECTED</th>
  <th>cumulative_sum_EXPECTED</th>
  <th>count_stuff_WRONG</th>
  <th>cumulative_sum_WRONG</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>38</td>
  <td>2019-06-14</td>
  <td>1</td>
  <td>6</td>
  <td>373</td>
  <td>1</td>
  <td>6</td>
  <td>1</td>
  <td>6</td>
  </tr>
  <tr>
  <td>38</td>
  <td>2019-07-15</td>
  <td>1</td>
  <td>7</td>
  <td>374</td>
  <td>2</td>
  <td>13 (6+7)</td>
  <td>2</td>
  <td>13 (6+7)</td>
  </tr>
  <tr>
  <td>38</td>
  <td>2019-07-16</td>
  <td>1</td>
  <td>8</td>
  <td>375</td>
  <td>3</td>
  <td>21 (6+7+8)</td>
  <td>3</td>
  <td>21 (6+7+8)</td>
  </tr>
  <tr>
  <td>38</td>
  <td>2020-06-14</td>
  <td>1</td>
  <td>16</td>
  <td>373</td>
  <td>3</td>
  <td>31 (7+8+16)</td>
  <td>4</td>
  <td>37 (6+7+8+16)</td>
  </tr>
  <tr>
  <td>38</td>
  <td>2020-07-15</td>
  <td>1</td>
  <td>17</td>
  <td>374</td>
  <td>3</td>
  <td>41 (8+16+17)</td>
  <td>5</td>
  <td>54 (6+7+8+16+17)</td>
  </tr>
  <tr>
  <td>38</td>
  <td>2020-07-16</td>
  <td>1</td>
  <td>18</td>
  <td>375</td>
  <td>3</td>
  <td>51 (16+17+18)</td>
  <td>6</td>
  <td>72 (6+7+8+16+17+18)</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Columns <code>count_stuff_EXPECTED</code> and <code>cumulative_sum_EXPECTED</code> are what I'm trying to get, columns <code>count_stuff_WRONG</code> and <code>cumulative_sum_WRONG</code> are the ones my current query is returning.</p>
  <p>In other words, I want to get cumulative values for each main_date but without counting/summing multiple times the same <code>their_ids</code>. So on row 4, for example, the window partition has <code>their_ids {373}</code> more than once, so it should be considered only the most recent one (row 4) and not consider the first occurrence (row 1)</p>
  <p>NOTE: there's no need to show on the query how the sum was calculated, I just put it in there in parentheses for clarity.</p>
  <p>I tried using</p>
  <pre><code>SUM(DISTINCT how_many) over (PARTITION BY dude_id ORDER BY main_date) as count_stuff
  </code></pre>
  <p>but got</p>
  <blockquote>
  <p>ERROR:  DISTINCT is not implemented for window functions</p>
  </blockquote>
  <p>SQL Fiddle: <a href="http://sqlfiddle.com/#!17/44850/2" rel="nofollow noreferrer">http://sqlfiddle.com/#!17/44850/2</a></p>