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, 'c1@gmail.com'),
(3, 'c2@gmail.com'),
(3, 'c3@gmail.com'),
(1, 'a1@gmail.com'),
(1, 'a1@gmail.com'),
(1, 'a1@gmail.com'),
(1, 'a2@gmail.com'),
(2, 'b1@gmail.com'),
(2, 'b1@gmail.com'),
(1, 'a1@gmail.com'),
(1, 'a2@gmail.com'),
(2, 'b1@gmail.com'),
(2, 'b2@gmail.com');
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, 'a1@gmail.com', 4, <---- the biggest invitation number
1, 'a2@gmail.com', 2, <---- followed by the rest of invitations for same user
2, 'b1@gmail.com', 3, <---- next biggest invitation number
2, 'b2@gmail.com', 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, 'c1@gmail.com'),
(33, 'c2@gmail.com'),
(33, 'c3@gmail.com'),
(11, 'a1@gmail.com'),
(11, 'a1@gmail.com'),
(11, 'a2@gmail.com'),
(2, 'b1@gmail.com'),
(2, 'b1@gmail.com'),
(11, 'a1@gmail.com'),
(11, 'a2@gmail.com'),
(11, 'a3@gmail.com'),
(2, 'b1@gmail.com'),
(11, 'a1@gmail.com'),
(2, 'b2@gmail.com');
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;