title: Snowflake SQL count distinct with window function
body: <p>I have a dataset with weekly customer and store information,</p>
  <p><strong>question- I have to calculate features such as total unique customers in last 1,2,3,4,5,6..so on weeks as of current week.</strong></p>
  <p>I am getting <strong>error</strong> while using count distinct of customers column over window function -</p>
  <p>I tried concat function to create array which also didn't work- thanks for help!</p>
  <pre><code>SELECT STORE,WEEK,
  
  count(distinct Customers) over (partition by STORE order by WEEK rows between 1 preceding and 1 preceding) as last_1_week_customers,
  count(distinct Customers) over (partition by STORE order by WEEK rows between 2 preceding and 2 preceding) as last_2_week_customers
      from TEST_TABLE
      group by STORE,WEEK
  </code></pre>
  <p><strong>error-    SQL compilation error: distinct cannot be used with a window frame or an order.</strong></p>
  <p>how can I fix this error?</p>
  <p><strong>Input</strong></p>
  <pre><code>CREATE TABLE TEST_TABLE (STORE STRING,WEEK STRING,Customers STRING);
  
  
  INSERT INTO TEST_TABLE VALUES
  
  ('A','1','AA'),
  ('A','1','DD'),
  ('A','2','AA'),
  ('A','2','BB'),
  ('A','2','CC'),
  ('A','3','AA'); 
  </code></pre>
  <p><a href="https://i.stack.imgur.com/SsFE3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SsFE3.png" alt="enter image description here" /></a></p>
  <p><strong>Output</strong></p>
  <p><a href="https://i.stack.imgur.com/tbYYI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tbYYI.png" alt="enter image description here" /></a></p>