This is essentially a gaps-and-islands problem. And when I have my SQL Server hat on, I often solve this kind of problem with two ROW_NUMBER()
calls. Sadly, MySQL, unlike many other major SQL products, does not support ROW_NUMBER()
, nor any other ranking function. To make up for that, however, you can use variable assignment in SELECTs, which MySQL does support (unlike many other major SQL products).
Below is a solution followed by an explanation:
SELECT
member_id,
member_name,
event_id,
COUNT(*) AS consecutive_times_missed,
MIN(event_date) AS first_date_missed,
MAX(event_date) AS last_date_missed
FROM (
SELECT
member_id,
member_name,
event_id,
event_date,
is_missed,
@occ_ranking := (event_id = @last_event) * (member_id = @last_member) * @occ_ranking + 1,
@att_ranking := (event_id = @last_event) * (member_id = @last_member)
* (is_missed = @last_missed) * @att_ranking + 1,
@occ_ranking - @att_ranking AS grp,
@last_member := member_id,
@last_event := event_id,
@last_missed := is_missed
FROM (
SELECT
m.member_id,
m.member_name,
e.event_id,
e.event_date,
(a.attendance_date IS NULL) AS is_missed
FROM members m
INNER JOIN event_dates e ON m.member_join_date <= e.event_date
LEFT JOIN attendance a ON m.member_id = a.member_id
AND e.event_id = a.event_id
AND e.event_date = a.attendance_date,
(
SELECT
@occ_ranking := 0,
@att_ranking := 0,
@last_member := 0,
@last_event := 0,
@last_missed := 0
) v
ORDER BY
m.member_id,
e.event_date
) s
) s
WHERE
is_missed = 1
GROUP BY
member_id,
member_name,
event_id,
grp
HAVING
COUNT(*) >= 3
;
Basically, you start with joining members
and event_dates
to get all the event occurrences the members could have attended based on their membership dates. Then you throw in the attendance
table (via a left join) to flag the missed occurrences. Here's an example of what you get by this time:
member_id event_id event_date is_missed
--------- -------- ---------- ---------
1 1 2012-07-10 0
2 1 2012-07-10 1
1 1 2012-07-14 0
2 1 2012-07-14 1
… … … …
At this point, the resulting set needs to be sorted by member_id, event_id
, because that is crucial to the subsequent rankings calculation.
Two different rankings are produced for every row. One is a ranking within the row's partition of (member_id, event_id)
(it is reset as soon as a new event or a new member is encountered). The other is a ranking within the specific group of consecutive event occurrences, either attended or missed, that the row belongs to (this ranking, in addition to being reset upon coming across a new member or event, also gets reset whenever the other group is encountered). And so you get something like this:
member_id event_id event_date is_missed ranking1 ranking2
--------- -------- ---------- --------- -------- --------
… … … … … …
1 1 2012-07-27 0 4 4
1 1 2012-07-28 0 5 5
1 1 2012-07-29 1 6 1
2 1 2012-07-10 1 1 1
2 1 2012-07-14 1 2 2
2 1 2012-07-25 1 3 3
2 1 2012-07-27 0 4 1
… … … … … …
As you may have noticed, the difference between the two ranking numbers is constant throughout the particular group of consecutive event occurrences of the same kind ("attended" or "missed") and is also unique for that group within its partition. Therefore, every such group can be identified by member_id
, event_id
and the just mentioned difference.
And it now remains simply to filter the events leaving just the missed ones, group the rows and get the necessary aggregated data, like the number of rows and, possibly, as in the query above, the dates of the first and the last event occurrence in the group. The number of rows is also used in an additional filter condition to omit groups with fewer rows than required.
Best Answer
This should be fairly simple :
To get only count :