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
Thanks.
4
You can simply GROUP BY user_id AND email. Also sort by the user ascending and count descending
SELECT user_id, email, count(*)
FROM invitation
GROUP BY user_id, email
ORDER BY user_id asc, count(*) desc
| User_id | Count | |
|---|---|---|
| 1 | [email protected] | 4 |
| 1 | [email protected] | 2 |
| 2 | [email protected] | 3 |
| 2 | [email protected] | 1 |
| 3 | [email protected] | 1 |
| 3 | [email protected] | 1 |
| 3 | [email protected] | 1 |
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;