CODEWITHSUNDEEP
mysql
zephyr325
zephyr325

Reputation: 23

Attending Streak MySQL Query

I'm working on a query that's trying to come up with what your current attendance record streak is. So in other words, person 1 has attended the last 5 events, person 2 the last 6 events, etc. The data file I've got for this is pretty simple:

Event Number - Unique Name
34 - Tim
34 - John
34 - Mike
34 - Larry
35 - John
35 - Mike
35 - Larry
35 - Paul
36 - John
36 - Steve
36 - Paul

And the endstate query result would hopefully produce:

John - 3
Paul - 2
Steve - 1

The additional fun part here is that there are several hundred events that have happened, and several hundred people who have/haven't attended. I've found several articles on mysql queries for current streak, but not if there isn't a definitive yes/no for each possible person.

Any thoughts on how to go about building this?

Folks - thanks for the quick responses! Let me clarify it a bit, though...I'm not trying to get the total attended events for each person. Instead, for the population of everyone who attended the last event, they have at least a streak of 1. But if you attended the last event, and you also attended the last 5 events, your streak would be 5. That would be the case even if you attended 100 events over the last 3 years, if you missed a date, and then showed up for the last 5 possible dates, your streak is 5. And I'd like to do this for everyone who attended the last event.

Upvotes: 2

Views: 409

Answers (3)

John Pick
John Pick

Reputation: 5650

Let's assume your table is Event and the columns are EventID and Name. We can determine the sequence (i.e., 1, 2, 3, etc.) in which each person attended events by the following query:

SELECT
  e1.Name, e1.EventID, COUNT(*) AS PersonalEventSequence
FROM
  Event e1
    INNER JOIN
  Event e2
    ON e1.Name = e2.Name AND e1.EventID >= e2.EventID
GROUP BY
  e1.Name, e1.EventID

We can leverage PersonalEventSequence to group each person's events into streaks:

SELECT
  Name, EventID - PersonalEventSequence AS StreakGroup
FROM
  (
    SELECT
      e1.Name, e1.EventID, COUNT(*) AS PersonalEventSequence
    FROM
      Event e1
        INNER JOIN
      Event e2
        ON e1.Name = e2.Name AND e1.EventID >= e2.EventID
    GROUP BY
      e1.Name, e1.EventID
  ) AS SubQuery1

Now that each person's events are grouped into streaks (having admittedly weird StreakGroup numbers!), we can determine the lengths of each person's streaks:

SELECT
  Name, StreakGroup, COUNT(*) AS StreakLength
FROM
  (
    SELECT
      Name, EventID - PersonalEventSequence AS StreakGroup
    FROM
      (
        SELECT
          e1.Name, e1.EventID, COUNT(*) AS PersonalEventSequence
        FROM
          Event e1
            INNER JOIN
          Event e2
            ON e1.Name = e2.Name AND e1.EventID >= e2.EventID
        GROUP BY
          e1.Name, e1.EventID
      ) AS SubQuery1
  ) SubQuery2
GROUP BY
  Name, StreakGroup

Now that we know the lengths of each person's streaks, we can determine the length of each person's longest streak:

SELECT
  Name, MAX(StreakLength) AS PersonalRecordStreakLength
FROM
  (
    SELECT
      Name, StreakGroup, COUNT(*) AS StreakLength
    FROM
      (
        SELECT
          Name, EventID - PersonalEventSequence AS StreakGroup
        FROM
          (
            SELECT
              e1.Name, e1.EventID, COUNT(*) AS PersonalEventSequence
            FROM
              Event e1
                INNER JOIN
              Event e2
                ON e1.Name = e2.Name AND e1.EventID >= e2.EventID
            GROUP BY
              e1.Name, e1.EventID
          ) AS SubQuery1
      ) SubQuery2
    GROUP BY
      Name, StreakGroup
  ) SubQuery3
GROUP BY
  Name

Notes:

  • The OP wanted only current streaks (i.e., streaks that include the latest event), but I'm leaving that specific solution up to the OP to figure out since the general solution shown here will be applicable to more programmers.
  • The code could be cleaned up by using Views instead of subqueries.
  • I haven't tried running this code. There could be errors.

Upvotes: 4

John Woo
John Woo

Reputation: 263803

try this:

SELECT    UniqueName, COUNT(*) as AttendedEventCount 
FROM      tableName
GROUP BY  UniqueName

Upvotes: 0

ScottJShea
ScottJShea

Reputation: 7111

I think it would be:

SELECT unique_name, count(event_number)
FROM <table>
GROUP BY unique_name
ORDER BY count(event_number) Desc

Unless I am missing something

Upvotes: 0

Related Questions