How to get what percentage of total users joined what number of meetings in PostgreSQL?

Question

There are two tables, one is called "user_preference" that contains all users:

 id | firstname | lastname | email |

And "match" which combines users with meetups they joined:

 id | matcher | partner | meetup |

Both matcher and partner are foreign keys that represent user_preference.id, meaning that same user can be both matcher and a partner in the same meetup.

What I need to know is what percentage of total unique users joined what number of meetings.

For example:

17% of users joined 5 meetups
20% of users joined 3 meetups
40% of users joined 1 meetup
23% of users joined 0 meetups

The number of meetups should not be hardcoded but dynamic.

But I want to avoid duplication of users for a single meetup and count them only once. For example this:

 id | matcher | partner | meetup |
 1 | user1 | user2 | meetup1 |
 2 | user1 | user3 | meetup1 |
 3 | user5 | user1 | meetup1 |
 4 | user6 | user1 | meetup2 |

Should count that user1 visited only 2 meetups.

What I managed to do so far is to display the count of meetups each user visited but that is not what I need:

SELECT distinct up.email users, COUNT(m.user) meetups
FROM user_preference up
LEFT JOIN
(
  SELECT matcher AS user FROM match
  UNION ALL
  SELECT partner AS user FROM match
) m ON m.user = up.id
GROUP BY up.email
ORDER BY meetups desc;

Answer 1

In the end I did this by making simple queries and looping through them in the code, its far from elegant solution but it should work.

If someone posts SQL solution I will accept and upvote it...

export const getDevStats = async () => {
  const users = await getRepository(UserPreference).query(
    `SELECT * FROM user_preference;`
  );

  const meetups = await getRepository(Meetup).query(
    `SELECT * FROM meetup;`
  );

  const matches = await getRepository(Match).query(
    `SELECT * FROM match;`
  );

  let userMatches: any = {};
  users.forEach((user: any) => {
    userMatches[user.id] = []
    matches.forEach((match: any) => {
        if(user.id == match.matcher || user.id == match.partner) {
           if(userMatches[user.id].indexOf(match.meetup) === -1) {
             userMatches[user.id].push(match.meetup);
           }
        }
    });   
  });

  let matchStats: any = {};
  for (var userId of Object.keys(userMatches)) {
      if (typeof matchStats[userMatches[userId].length] === 'undefined') {
        matchStats[userMatches[userId].length] = 0;
      }
      matchStats[userMatches[userId].length]++;
  }



  return {
    users: users,
    meetups: meetups,
    matches: matches,
    userMatches: userMatches,
    matchStats: matchStats
  };
};