Order by values with most incidence

Question

I have a table as this example:

CREATE TABLE customerEvent(
    customerID  int NOT NULL,
    eventID     int NOT NULL
)

customerID may appear in many records. I use the following query to identify most frequent customerID:

SELECT
    customerID,eventID
    ,row_number() OVER(PARTITION BY customerID ORDER BY eventID) as frequency
FROM customerEvent
ORDER BY frequency DESC

With this I see those most frequent customerID. But they appear only once on the top of the list. Example:

customerID  eventID frequency
7           3       5
15          9       4
7           1       4
9           4       3
7           10      3
15          2       3

I need all records of the most frequent customerID to appear on the top, example:

customerID  eventID frequency
7           3       5
7           1       4
7           10      3
15          9       4
15          2       3
9           4       3

Any idea how I can build a query that does that?

Answer 1

It seems you don't really need the "frequency" for the sorting, only the "maximum frequency" per customer. You can use the COUNT(*) OVER (PARTITION BY customerID):

SELECT
    customerID, 
    eventID,
    ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY eventID) AS frequency  
FROM 
    customerEvent
ORDER BY 
    COUNT(*) OVER (PARTITION BY customerID) DESC,
    customerID,
    frequency DESC ; 

The second part of the order by needs to be customerID just in case of ties. I assume that if you have two or more customers with the same number of events, you don't want their events mixed up.