I have an event table associated with users I would like to know if it possible to write an sql query, to select user who create event, and the last 3 events had the status 'canceled'
not count() where status = 'canceled', only where last 3 rows had status canceled
Select u.* from users AS u
Right Join events as e ON e.user_id = u.id
WHERE {last 3 rows have e.status = 'canceled'}
Order by e.created_at
Group by u.id
EVENTS table:
user_id, status, created_at
1, 'canceled', 2017-04-21
1, 'finished', 2017-04-20
1, 'finished', 2017-04-19
1, 'canceled', 2017-04-18
1, 'canceled', 2017-04-17
2, 'canceled', 2017-04-21
2, 'canceled', 2017-04-20
2, 'canceled', 2017-04-19
2, 'finished', 2017-04-18
2, 'finished', 2017-04-17
You can get the list of users using window functions. Here is one method:
select e.user_id
from (select e.*,
row_number() over (partition by e.user_id order by created_at desc) as seqnum
from event e
) e
where seqnum <= 3 and e.status = 'canceled'
group by e.user_id
having count(*) = 3;
The query enumerates the events from the end and then counts the number that are canceled.
You can get additional user information by joining back to users (or using in or exists).