title: redshift: count distinct customers over window partition
body: <p>Redshift doesn't support <code>DISTINCT</code> aggregates in its window functions. <a href="https://docs.aws.amazon.com/redshift/latest/dg/r_WF_COUNT.html" rel="noreferrer"><em>AWS documentation for <code>COUNT</code> states this</em></a>, and <code>distinct</code> isn't supported for any of the window functions.</p>
  
  <h3>My use case: count customers over varying time intervals and traffic channels</h3>
  
  <p>I desire monthly and YTD <strong>unique</strong> customer counts for the current year, and also split by traffic channel as well as total for all channels. Since a customer can visit more than once I need to count only distinct customers, and therefore the Redshift window aggregates won't help.</p>
  
  <ul>
  <li>I can count distinct customers using <code>count(distinct customer_id)...group by</code>, but this will give me only a single result of the four needed.</li>
  <li>I <strong>don't</strong> want to get into the habit of running a full query for each desired count piled up between a bunch of <code>union all</code>. I hope this is not the only solution.</li>
  </ul>
  
  <p>This is what I would write in postgres (or Oracle for that matter):</p>
  
  <pre><code>select order_month
         , traffic_channel
         , count(distinct customer_id) over(partition by order_month, traffic_channel) as customers_by_channel_and_month
         , count(distinct customer_id) over(partition by traffic_channel) as ytd_customers_by_channel
         , count(distinct customer_id) over(partition by order_month) as monthly_customers_all_channels
         , count(distinct customer_id) over() as ytd_total_customers
  
  from orders_traffic_channels
  /* otc is a table of dated transactions of customers, channels, and month of order */
  
  where to_char(order_month, 'YYYY') = '2017'
  </code></pre>
  
  <p><strong>How can I solve this in Redshift?</strong></p>
  
  <p>The result needs to work on a redshift cluster, furthermore this is a simplified problem and the actual desired result has product category and customer type, which multiplies the number of partitions needed. Therefore a stack of <code>union all</code> rollups is not a nice solution.</p>