Return to Answer

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. If you have two or more customers with the same number of events, then I assume you don't want their events mixed up.

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.

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. If you have two or more customers with the same number of events, I assume you don't want their events mixed up.

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. If you have two or more customers with the same number of events, then I assume you don't want their events mixed up.

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 ; 

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. If you have two or more customers with the same number of events, I assume you don't want their events mixed up.