Query to find users who are in more than one user group

Question

I'm working with a site that has a few thousand user accounts. With the exception of core accounts with CP access, each user account exists for the purpose of access to event URLs which eventually expire.

But, although the vast majority of users belong to only one event-specific user group, a select few belong to more than one.

For the purposes of housekeeping, I'd like to be able to easily find such user accounts, so I can exclude them from a periodic account deletion process.

What query could serve this purpose for a report of multi-user-group users?

Answer 1

If you're looking for a SQL query (as opposed to an element query), then you can use this:

SELECT userId FROM craft_usergroups_users GROUP BY userId HAVING COUNT(*) > 1;

This only gets the userId.