COUNT DISTINCT and window functions

We can easily calculate the total number of PCs for each manufacturer, as well as the number of unique models of this manufacturer in the PC table:

select maker, count(*) models, count(distinct pc.model) unique_models
from product p 
    join pc on p.model=pc.model
group by maker
order by maker;
🚫
[[ error ]]
[[ column ]]
NULL [[ value ]]
makermodelsunique_models
A82
B31
E11

If we need to get details about each model, along with their total number for each manufacturer, we can use the window function:

select maker, pc.model, pc.price,
       count(*) over(partition by maker) models
from product p 
    join pc on p.model=pc.model
order by maker, pc.model;
🚫
[[ error ]]
[[ column ]]
NULL [[ value ]]
makermodelpricemodels
A1232600,008
A1232400,008
A1232350,008
A1232350,008
A1233600,008
A1233950,008
A1233980,008
A1233970,008
B1121850,003
B1121850,003
B1121850,003
E1260350,001

Now imagine that we need to supplement this information with the number of unique models. An expected attempt

select maker, pc.model,pc.price,
       count(*) over(partition by maker) models,
       count(distinct pc.model) over(partition by maker) unique_models
from product p 
    join pc on p.model=pc.model
order by maker, pc.model;
🚫
[[ error ]]
[[ column ]]
NULL [[ value ]]

fails:

The DISTINCT keyword is not allowed with the OVER clause.

The error message clearly describes the problem. The question is how to work around it.

Using a subquery

with cte as
    (select maker, pc.model, pc.price,
            count(*) over(partition by maker) models
    from product p 
        join pc on p.model=pc.model
    )
select maker, model, models,
       (select count(distinct model)
        from cte t 
        where t.maker=cte.maker) unique_models
from cte
order by maker,model;
🚫
[[ error ]]
[[ column ]]
NULL [[ value ]]
makermodelmodelsunique_models
A123282
A123282
A123282
A123282
A123382
A123382
A123382
A123382
B112131
B112131
B112131
E126011

Using DENSE_RANK

with cte as
    (select maker, pc.model, pc.price,
            count(*) over(partition by maker) models,
            DENSE_RANK() over(partition by maker order by pc.model) drnk
     from product p 
        join pc on p.model=pc.model
   )
select maker, model, price, models,
       max(drnk) over(partition by maker) unique_models 
from cte
order by maker, model;
🚫
[[ error ]]
[[ column ]]
NULL [[ value ]]

Here we take advantage of the fact that the last rank value, max(drnk), turns out to be equal to the number of unique models.