Filter rows based on window function

Question

Recently, I saw a user, who was the only one being awarded some badge.

Curiously, I headed over to data.se and looked for similar examples with (data.se - Single awarded badges)

select count(*) as awarded,
       name, class
from badges
group by name, class
having count(*) = 1

---

Taking this further, I also wanted to see the users, who were owning these badges (data.se - Count awarded badges and users)

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

This gives me all entries, not only the single owning ones, e.g. count(*) = 1. To get only these, I must use a CTE or subquery, e.g. (data.se - Single awarded badges and users)

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

The drawback of these last two queries is performance. The (non-cached) query takes about 20 times of the simple group by/having query. Using a subquery gives no improvement.

---

I am aware, that you can't use the window function in a where or having clause, nor say where awarded = 1 in the second query.

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.

Answer 1

One solution is to take the group by/having query as a CTE and join with the badges and users tables (data.se - Single awarded badges and users)

with awarded as (select count(*) as num,
                        name, class
                 from badges
                 group by name, class
                 having count(*) = 1)
select b.name, b.class, u.displayname
from badges b
join awarded a on a.name = b.name and a.class = b.class
join users u on u.id = b.userid