title: oracle SQL select the distinct customers in the past x day rolling period
body: <p>say you have a table of customers with dates as follows:<br>
  [customer_table]  </p>
  
  <pre><code>+----------+-----------+----------+
  | customer | date      | purchase |
  +----------+-----------+----------+
  | 1        | 1/01/2016 | 12       |
  +----------+-----------+----------+
  | 1        | 1/12/2016 | 3        |
  +----------+-----------+----------+
  | 2        | 5/03/2016 | 5        |
  +----------+-----------+----------+
  | 3        | 1/16/2016 | 6        |
  +----------+-----------+----------+
  | 3        | 3/22/2016 | 1        |
  +----------+-----------+----------+  
  </code></pre>
  
  <p>I want to write a query to count how many distinct customers made a purchase in the last 10 days as a rolling period, starting from each calendar day and counting 10 days backwards. So for each unique day in 2016 the final output would be a calendar, where each day has a count of distinct customers that exist in the prior 10 days of that day of the calendar like the following:<br>
  [result_table]  </p>
  
  <pre><code>+-----------+------------------+
  | date      | unique customers |
  +-----------+------------------+
  | 1/01/2016 | 112              |
  +-----------+------------------+
  | 1/02/2016 | 104              |
  +-----------+------------------+
  | 1/03/2016 | 140              |
  +-----------+------------------+
  | 1/04/2016 | 133              |
  +-----------+------------------+
  | ....      | 121              |
  +-----------+------------------+  
  </code></pre>
  
  <p>One solution I came up with was to create a calendar table that is one single column, then join the calendar table to the customer table with an inequality join. I believe this is extremely inefficient and am seeking a faster solution. So my first step was create a calendar like so:<br>
  [calendar]  </p>
  
  <pre><code>+-----------+
  | date      |
  +-----------+
  | 1/01/2016 |
  +-----------+
  | 1/02/2016 |
  +-----------+
  | 1/03/2016 |
  +-----------+
  | 1/04/2016 |
  +-----------+
  | 1/05/2016 |
  +-----------+  
  </code></pre>
  
  <p>Then for each day in that calendar, to count the distinct set of customers prior to each day, I join with an inequality like so:  </p>
  
  <pre><code>select
  count(distinct customer) as unique customers
  from calendar c
  left join mytable m
  on c.date&gt;=m.date and m.date&gt;=c.date-10  
  </code></pre>
  
  <p>While I believe this is correct, it runs extremely slow (say for 2 years of a calendar with a few million customers). Is there an oracle analytic function that may help me out here?</p>