SQL for last x events of type

Question

Im trying to find which members have had the best attendance over the last x events where event type matters

Example structure here

http://sqlfiddle.com/#!2/bde53/1

attended_id is the members id, Given many events with many event types i would like something like this if possible

 attended_id | last 6 event 1 | last 12 event 2 |  2013 event 3 |
       1             6               10                6
       2             5                9                12
       3             2                8                7

2013 event 3 means all event id's 3 which occured in 2013 is this possible or is it best to export to excel to get this information ?

Also open to new structures if it makes this query easier. The numbers should be easily changeable eg getting the last 8 event 1's instead of the last 6

I have SQL for each but cant combine them

Events in the last year by member id

SELECT attended_id, year(x.date), count(event_id)  FROM events e INNER JOIN events_types x USING (event_id)
INNER JOIN events_type t USING (event_type)
WHERE t.event_type = 1
group by attended_id, year(x.`date`);

last x events of type

SELECT attended_id, count(event_id) FROM events e INNER JOIN events_types x USING (event_id)
INNER JOIN events_type t USING (event_type)
WHERE t.event_type = 1 and
e.event_id >= (             
select event_id from events_types where event_type = 1 order by event_id desc
limit 1,1              
)
group by attended_id

I just cant combine these to show both on the same query

Answer 1

please try this sqlFiddle

SELECT T1.attended_id,
       T2.`last 6 event 1`,
       T3.`last 12 event 2`,
       T4.`2013 event 3`
FROM
    (SELECT DISTINCT attended_id
     FROM events)T1
LEFT JOIN
    (SELECT e.attended_id,COUNT(*) as `last 6 event 1`
     FROM events e
     INNER JOIN (SELECT event_id,event_type,date
                 FROM events_types
                 WHERE event_type = 1
                 ORDER BY date DESC
                 LIMIT 6
                ) et
     USING (event_id)
     GROUP BY e.attended_id
     )T2
ON (T1.attended_id = T2.attended_id)
LEFT JOIN
    (SELECT e.attended_id,COUNT(*) as `last 12 event 2`
     FROM events e
     INNER JOIN (SELECT event_id,event_type,date
                 FROM events_types
                 WHERE event_type = 2
                 ORDER BY date DESC
                 LIMIT 12
                ) et
     USING (event_id)
     GROUP BY e.attended_id
     )T3
ON (T1.attended_id = T3.attended_id)
LEFT JOIN
    (SELECT e.attended_id,COUNT(*) as `2013 event 3`
     FROM events e
     INNER JOIN (SELECT event_id,event_type,date
                 FROM events_types
                 WHERE event_type = 3
                 AND year(date) = 2013
                 ) et
     USING (event_id)
     GROUP BY e.attended_id
     )T4
ON (T1.attended_id = T4.attended_id)

your sqlFiddle doesn't have enough data so I added some random events and some event_types and I noticed that you don't need to join with event_type since you're not grabbing any information from event_type but only wanted a count.

If you wanted last 8 event 1, just change the LIMIT 6 to LIMIT 8 inside et of T2

OR you can try this sqlFiddle with code below

SELECT e.attended_id,
       SUM(IF(event_type = 1 AND typeRank BETWEEN 1 AND 6,1,0)) as `last 6 event 1`,
       SUM(IF(event_type = 2 AND typeRank BETWEEN 1 AND 12,1,0)) as `last 12 event 2`,
       SUM(IF(event_type = 3 AND YEAR(date) = 2013,1,0)) as `2013 event 3`
FROM events e
INNER JOIN (SELECT event_id,event_type,date,
                   IF (@prevType != event_type,@typeRank:=1,@typeRank:=@typeRank+1) as typeRank,
                   @prevType := event_type
            FROM events_types,(SELECT @prevType:=0,@typeRank:=0)R
            ORDER BY event_type,date DESC
            ) et
USING (event_id)
GROUP BY e.attended_id

if you wanted last 8 event 1 just change the BETWEEN 1 AND 6 to BETWEEN 1 AND 8

Answer 2

The query can be achieved by:

• using user defined variables to calculate the age rank of each event type in one pass
• joining the attendance records to the ranked results, grouping on attended_id

Here's the query:

select
  a.attended_id,
  sum(event_type = 1 and rank <= 6) `last 6 event 1`,
  sum(event_type = 2 and rank <= 12) `last 12 event 2`,
  sum(event_type = 3 and year(date) = 2013) `2013 event 3`
from events a
left join (
  select event_id, event_type, date,
    (@row := if(@prev is null or @row is null or @prev != event_type, 1, @row + 1)) rank,
    (@prev := event_type) prev
  from (select event_id, event_type, date
    from events_types
    order by event_type, date desc) e) r on r.event_id = a.event_id
group by 1

Here's the SQLFiddle

As you can see, it would be a simple matter to change the variables to have different event types and "last n" values, or even to add more columns for different breakdowns.

Notes:

• the inner-most query orders the rows by event then date oldest-to-newest. This is required for the rank logic to work properly
• the tests for the variables being null are only needed for the first row, but are used to avoid having to define the variables in a separate statement, making the query a single stand-alone query, which is often required because most database libraries don't support multiple queries executed in a single call
• lengthy case statements are avoided by summing a condition; in mysql booleans are 1 for true and 0 for false, so summing a condition counts how many times it was true

A final note, your table names are a bit confusing. They could be changed to make it more clear what their meaning is. I suggest these changes:

• events --> attendances
• events_types --> events
• events_type --> event_types