PostgreSQL Question posted in
The official documentation can be found here.

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

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]');
Tags:

2

Answers


  1. 0

    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 Email 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
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search