support_events(case_id, user_id, event_type, event_at)

Return the cases whose first 'escalated' event came after at least three 'contact' events on the same case, together with how many contacts preceded it.

Return case_id, first_escalated_at and contacts_before_escalation, ordered by case_id.

Contacts logged after the escalation do not count, which is what makes the total contact count on a case the wrong number to use.

Tables

support_events

case_id  user_id  event_type  event_at
-------  -------  ----------  -------------------
1        10       contact     2026-02-01 09:00:00
1        10       contact     2026-02-02 09:00:00
1        10       contact     2026-02-03 09:00:00
1        10       escalated   2026-02-04 09:00:00
2        11       contact     2026-02-01 09:00:00
2        11       contact     2026-02-02 09:00:00
2        11       escalated   2026-02-03 09:00:00
2        11       contact     2026-02-04 09:00:00
... 9 more row(s)

Expected result

case_id  first_escalated_at   contacts_before_escalation
-------  -------------------  --------------------------
1        2026-02-04 09:00:00  3
3        2026-03-05 09:00:00  4