title: Filter rows based on window function
body: <p>Recently, I saw a user, who was the only one being awarded some badge.</p>
  <p>Curiously, I headed over to <a href="https://data.stackexchange.com/stackoverflow" rel="nofollow noreferrer">data.se</a> and looked for similar examples with (<a href="https://data.stackexchange.com/stackoverflow/query/561566/single-awarded-badges" rel="nofollow noreferrer">data.se - Single awarded badges</a>)</p>
  <pre><code>select count(*) as awarded,
         name, class
  from badges
  group by name, class
  having count(*) = 1
  </code></pre>
  <hr />
  <p>Taking this further, I also wanted to see the users, who were owning these badges (<a href="https://data.stackexchange.com/stackoverflow/query/561583/count-awarded-badges-and-users" rel="nofollow noreferrer">data.se - Count awarded badges and users</a>)</p>
  <pre><code>select count(*) over (partition by b.name, b.class) as awarded,
         b.name, b.class, u.displayname
  from badges b
  join users u on u.id = b.userid
  </code></pre>
  <p>This gives me all entries, not only the single owning ones, e.g. <code>count(*) = 1</code>. To get only these, I must use a CTE or subquery, e.g. (<a href="https://data.stackexchange.com/stackoverflow/query/561557/single-awarded-badges-and-users" rel="nofollow noreferrer">data.se - Single awarded badges and users</a>)</p>
  <pre><code>with q as (select count(*) over (partition by b.name, b.class) as awarded,
                    b.name, b.class, u.displayname
             from badges b
             join users u on u.id = b.userid)
  select name, class, displayname
  from q
  where awarded = 1
  </code></pre>
  <p>The drawback of these last two queries is performance. The (non-cached) query takes about 20 times of the simple <code>group by/having</code> query. Using a subquery gives no improvement.</p>
  <hr />
  <p>I am aware, that you can't use the <a href="https://stackoverflow.com/q/13997177/1741542">window function in a where or having clause</a>, nor say <code>where awarded = 1</code> in the second query.</p>
  <p>Are there faster and simpler queries, maybe even without a CTE or subquery? I give one (with CTE) as my own answer, but would like to see alternative solutions.</p>