Issues with count(distinct) and partition by

user_18203273 0
Viewed 47

Using postgres.

My SQL is incredibly rusty and I'd greatly appreciate some advice to help me overcome my problem.

I want to find situations where 2+ cars were sold at exactly the same time, were the same make, but were different models

If I COUNT(model) OVER (PARTITION BY timestamp, make) I also get situations where cars of the same model are included in the view with no way to filter them out.

I have tried using distinct count: COUNT(DISTINCT model) OVER (PARTITION BY timestamp, make), which would work perfectly however counting distinct whilst partitioning is not allowed.

I would ideally like a solution where I don't have to join to the same table using an inequality join as I'm going to be querying a large dataset and performance is important.

Apologies if the solution to this is incredibly simple but, for the life of me, I cannot work it out.

Current output:

enter image description here

Desired output:

enter image description here

Note: I don't care about the count column, it was just to help describe the problem

Thanks very much

postgresql
1 Answers

You could use dense_rank and then get the maximum of those:

SELECT timestamp, make, model,
       max(rank) OVER (PARTITION BY timestamp, make) AS count
FROM (SELECT timestamp, make, model,
             dense_rank() OVER (PARTITION BY timestamp, make ORDER BY model) AS rank,
             count(*) OVER (PARTITION BY timestamp, make) AS num
      FROM cars) AS q
WHERE num > 1;

 timestamp  │    make    │ model  │ count 
════════════╪════════════╪════════╪═══════
 2022-01-01 │ Ford       │ Fiesta │     2
 2022-01-01 │ Ford       │ Focus  │     2
 2022-01-01 │ Volkswagen │ Golf   │     2
 2022-01-01 │ Volkswagen │ Polo   │     2
 2022-01-03 │ Ford       │ Focus  │     1
 2022-01-03 │ Ford       │ Focus  │     1
(6 rows)
user_64643080
Related