sql retrieve all users with multiple email invitations, ordered by biggest invitation number per email, and user grouped

Question

I have a table invitations keeping for each user the email invitations he received. One user can have many emails, on each email can be received many invitations.

create table invitation (
    user_id INT         NOT NULL,
    email   VARCHAR(32) NOT NULL
 );

insert into invitation (user_id, email)
values
 (3, '[email protected]'),
 (3, '[email protected]'),
 (3, '[email protected]'),
 (1, '[email protected]'),
 (1, '[email protected]'),
 (1, '[email protected]'),
 (1, '[email protected]'),
 (2, '[email protected]'),
 (2, '[email protected]'),
 (1, '[email protected]'),
 (1, '[email protected]'),
 (2, '[email protected]'),
 (2, '[email protected]');

I want to have the top of users with the most invitations. But for a user list full situation: all emails and counter of invitations, beside the email with biggest invitation. I don't need to see users with only one invitation.

Result should be like this:

userId.  email.         counter of invitations per email
--------------------------------
 1,     '[email protected]',   4,    <---- the biggest invitation number 
 1,     '[email protected]',   2,    <----   followed by the rest of invitations for same user
 2,     '[email protected]',   3,    <---- next biggest invitation number
 2,     '[email protected]',   1.    <----   followed by the rest of invitations for same user.

I tried SQL SELECT queries with group by and order by but it's clearly more complex:

select user_id, email, count(email) as counter
from invitation
group by email, user_id
having count(email) > 1
ORDER BY counter DESC

I use PostgreSQL.

Thanks.

UPDATE:

I realised that my small example was not really exact: we can not/should not rely on the order of the user_id (can be anything there).

So I transform the example a bit: values are

 (33, '[email protected]'),
 (33, '[email protected]'),
 (33, '[email protected]'),
 (11, '[email protected]'),
 (11, '[email protected]'),
 (11, '[email protected]'),
 (2, '[email protected]'),
 (2, '[email protected]'),
 (11, '[email protected]'),
 (11, '[email protected]'),
 (11, '[email protected]'),
 (2, '[email protected]'),
 (11, '[email protected]'),
 (2, '[email protected]');

Answer 1

If you group by user ID and email address, you get the counts that you want. Your problem is that you want to sort the users by their maximum count. A user's maximum count is

MAX(COUNT(*)) OVER (PARTITION BY user_id)

The complete query:

SELECT user_id, email, COUNT(*) AS counter
FROM invitation
GROUP BY user_id, email
ORDER BY MAX(COUNT(*)) OVER (PARTITION BY user_id) DESC,
         user_id,
         COUNT(*) DESC,
         email;