Count how many times a user logged in 1x, 2x, 3x-CodePudding

I'm just beginning to learn SQL and this has completely stumped me. I join two tables on user_id where the event was a login. So far so good. Then I need to group those occurrences and count them to return the answer. How many times did users log in 1x, 2x, 3x...?

What I am having trouble with is referencing the first count (occurrences) and the fact that I can't group by occurrences since it is an aggregate function.

Here is the code, it returns two columns, user_id and occurrences. The data is on www.mode.com.

SELECT
  Users.user_id,
  COUNT(Users.user_id) AS occurrences
FROM
  tutorial.playbook_users Users
  JOIN tutorial.playbook_events EVENTS ON Users.user_id = EVENTS.user_id
WHERE
  EVENTS.event_name = 'login'
GROUP BY
  1
ORDER BY
  2

CodePudding user response：

So just aggregate it again

SELECT 
  q.UserLogins AS occurrences, 
  COUNT(*) AS Total
FROM 
(
  SELECT
    Users.user_id,
    COUNT(EVENTS.user_id) AS UserLogins
  FROM tutorial.playbook_users Users 
  JOIN tutorial.playbook_events EVENTS 
    ON EVENTS.user_id = Users.user_id
   AND EVENTS.event_name = 'login'
  GROUP BY Users.user_id
) q
GROUP BY q.UserLogins
ORDER BY q.UserLogins