title: Aggregating tsrange values into day buckets with a tie-breaker
body: <p>So I've got a schema that lets people donate $ to a set of organizations, and that donation is tied to a certain arbitrary period of time. I'm working on a report that looks at each day, and for each organization shows the total number of donations and the total cumulative value of those donations for that organization's day.</p>
  
  <p>For example, here's a mockup of 3 donors, Alpha (orange), Bravo (green), and Charlie (Blue) donating to 2 different organizations (Foo and Bar) over various time periods:</p>
  
  <p><a href="https://i.stack.imgur.com/5sokm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5sokm.png" alt="enter image description here"></a></p>
  
  <p>I've created a SQLFiddle that implements the above example in a schema that somewhat reflects what I'm working with in reality: <a href="http://sqlfiddle.com/#!17/88969/1" rel="nofollow noreferrer">http://sqlfiddle.com/#!17/88969/1</a></p>
  
  <p>(The schema is broken out into more tables than what you'd come up with given the problem statement to better reflect the real-life version I'm working with)</p>
  
  <p>So far, the query that I've managed to put together looks like this:</p>
  
  <pre><code>WITH report_dates AS (
    SELECT '2018-01-01'::date + g AS date
    FROM generate_series(0, 14) g
  ), organizations AS (
    SELECT id AS organization_id FROM users
    WHERE type = 'Organization'
  )
  
  SELECT * FROM report_dates rd
  CROSS JOIN organizations o
  LEFT JOIN LATERAL (
    SELECT
      COALESCE(sum(doa.amount_cents), 0) AS total_donations_cents,
      COALESCE(count(doa.*), 0) AS total_donors
    FROM users
    LEFT JOIN donor_organization_amounts doa ON doa.organization_id = users.id
    LEFT JOIN donor_amounts da ON da.id = doa.donor_amounts_id
    LEFT JOIN donor_schedules ds ON ds.donor_amounts_id = da.id
    WHERE (users.id = o.organization_id) AND (ds.period &amp;&amp; tsrange(rd.date::timestamp, rd.date::timestamp + INTERVAL '1 day', '[)'))
  ) o2 ON true;
  </code></pre>
  
  <p>With the results looking like this:</p>
  
  <pre><code>|       date | organization_id | total_donations_cents | total_donors |
  |------------|-----------------|-----------------------|--------------|
  | 2018-01-01 |               1 |                     0 |            0 |
  | 2018-01-02 |               1 |                   250 |            1 |
  | 2018-01-03 |               1 |                   250 |            1 |
  | 2018-01-04 |               1 |                  1750 |            3 |
  | 2018-01-05 |               1 |                  1750 |            3 |
  | 2018-01-06 |               1 |                  1750 |            3 |
  | 2018-01-07 |               1 |                   750 |            2 |
  | 2018-01-08 |               1 |                   850 |            2 |
  | 2018-01-09 |               1 |                   850 |            2 |
  | 2018-01-10 |               1 |                   500 |            1 |
  | 2018-01-11 |               1 |                   500 |            1 |
  | 2018-01-12 |               1 |                   500 |            1 |
  | 2018-01-13 |               1 |                  1500 |            2 |
  | 2018-01-14 |               1 |                  1000 |            1 |
  | 2018-01-15 |               1 |                     0 |            0 |
  | 2018-01-01 |               2 |                     0 |            0 |
  | 2018-01-02 |               2 |                   250 |            1 |
  | 2018-01-03 |               2 |                   250 |            1 |
  | 2018-01-04 |               2 |                  1750 |            2 |
  | 2018-01-05 |               2 |                  1750 |            2 |
  | 2018-01-06 |               2 |                  1750 |            2 |
  | 2018-01-07 |               2 |                  1750 |            2 |
  | 2018-01-08 |               2 |                  2000 |            2 |
  | 2018-01-09 |               2 |                  2000 |            2 |
  | 2018-01-10 |               2 |                  1500 |            1 |
  | 2018-01-11 |               2 |                  1500 |            1 |
  | 2018-01-12 |               2 |                     0 |            0 |
  | 2018-01-13 |               2 |                  1000 |            2 |
  | 2018-01-14 |               2 |                   500 |            1 |
  | 2018-01-15 |               2 |                     0 |            0 |
  </code></pre>
  
  <p>That's pretty close, however the problem with this query is that on days where a donation ends and that same donor begins a new one, it should only count that donor's donation one time, using the higher amount donation as a tie-breaker for the cumulative $ count. An example of that is on 2018-01-13 for organization Foo: <code>total_donors</code> should be 1 and <code>total_donations_cents</code> 1000.</p>
  
  <p>I tried to implement a tie-breaker for using <code>DISTINCT ON</code> but I got off into the weeds... any help would be appreciated!</p>
  
  <p>Also, should I be worried about the performance implications of my implementation so far, given the CTEs and the <code>CROSS JOIN</code>?</p>