title: COUNT(DISTINCT()) OVER (PARTITION BY) in Presto (Athena)?
body: <p>I have the following table in an Athena (Presto) DB, let's call it table1 (simplified for this question):</p>
  
  <pre><code>  | serverdate | colA | colB | colC | hash
    |-------------------------------------------
  0 | 2019-12-01 |    1 | OK   | 10   | hash1
  1 | 2019-12-02 |    2 | OK   | 10   | hash2
  2 | 2019-12-02 |    3 | NOK  | 100  | hash3
  3 | 2019-12-01 |    3 | OK   | 0    | hash4
  4 | 2019-12-03 |    6 | OK   | 1    | hash5
  5 | 2019-12-05 |    8 | NOK  | 0    | hash6
  6 | 2019-12-06 |    8 | NOK  | 0    | hash6
  </code></pre>
  
  <p>The following query is used to count how many distinct "hash" are in the table:</p>
  
  <pre><code>SELECT 'users' AS Type, round(count(DISTINCT hash)) AS uu
  FROM table1
  </code></pre>
  
  <p>This is later used as a subquery, but it is not important for this question. In this example, result should be:</p>
  
  <pre><code>  |  type | uu
    |-------------
  0 | users | 6
  </code></pre>
  
  <p><strong>What I want</strong>: I want to do this same counting, but grouping the results by <em>colA</em>. My result should be something like this:</p>
  
  <pre><code>  | colA | counthash
    |------------------
  0 |    1 | 1
  1 |    2 | 1
  2 |    3 | 2
  3 |    6 | 1
  4 |    8 | 1
  </code></pre>
  
  <p>I think in using COUNT(DISTINCT(hash)) OVER (PARTITION BY colA), but as far as I know, COUNT(DISTINCT()) is not allowed as a window function in Presto.</p>
  
  <p>Any ideas in how to do this? Thanks.</p>