title: Cumulative count distinct in Netezza
body: <p>I'm using Netezza.  I'm working with marketing data, specifically coupons.  Right now I'm counting distinct daily coupon redeemers; no big deal.  I'd like to get the count of distinct redeemers to date, though.  Note that it's not just the sum of daily redeemers, since a customer might redeem on different days, and therefore the sum of daily redeemers could be multi-counting customers.</p>
  
  <p>I closed my eyes, made a wish, and executed the following query hoping it would work:</p>
  
  <pre><code>select redemption_date
        ,count(distinct(customer_id)) as day_redeemers
        ,count(distinct(customer_id)) over (partition by null rows unbounded preceding) as cml_redeemers
  from coupon_history
  group by 1
  order by 1
  </code></pre>
  
  <p>But Netezza complains:
      ERROR [HY000] ERROR:  Attribute CUSTOMER_ID must be GROUPed or used in an aggregate function</p>
  
  <p>...and so I close my eyes, make a wish, and execute the following (note the addition to the group by):</p>
  
  <pre><code>select redemption_date
        ,count(distinct(customer_id)) as day_redeemers
        ,count(distinct(customer_id)) over (partition by null rows unbounded preceding) as cml_redeemers
  from coupon_history
  group by 1,customer_id
  order by 1
  </code></pre>
  
  <p>Netezza complains as follows:</p>
  
  <pre><code>ERROR [HY000] ERROR:  DISTINCT aggregate not allowed in window with ORDER BY or frame specification
  </code></pre>
  
  <p>That error leads me to think that internally Netezza is ordering the customer_id in order to count transitions and thus distincts.  But it does leave me at sort of a loss for what I should try next.  I was hoping for something simple, but apparently it's not my lucky day.</p>
  
  <p>Any ideas on how to make my original query work, or suggestions on an alternate approach?</p>
  
  <p>Thanks!</p>