Sessionise API Calls and Count Server Errors — SQL Problem
A merchant's integration talks to the payments API in bursts.
- Difficulty: Hard
- Topics: Window Functions, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #346
Problem statement
A merchant's integration talks to the payments API in bursts. The API team groups each client's calls into sessions: calls are taken in order of called_at (then call_id), and a call starts a new session when it comes more than 30 minutes (1800 seconds) after the client's previous call; exactly 30 minutes still continues the session. A client's first call starts session 1.
Return one row per session with the columns client_id, session_no (1, 2, … per client, in time order), session_start, session_end (first and last call times), calls and server_errors (calls with status_code 500 or above), ordered by client_id, then session_no.
Tables
Table: ApiCall
| Column | Type |
|---|---|
| call_id | int |
| client_id | varchar |
| called_at | datetime |
| status_code | int |
Primary key: call_id.
One row per API call. Client ids are lower case, e.g. 'merchant-104'.
Examples
Example 1
ApiCall
| call_id | client_id | called_at | status_code |
|---|---|---|---|
| 1 | merchant-104 | 2025-08-12 09:00:00 | 200 |
| 2 | merchant-104 | 2025-08-12 09:20:00 | 502 |
| 3 | merchant-104 | 2025-08-12 09:50:00 | 200 |
| 4 | merchant-104 | 2025-08-12 10:20:01 | 200 |
| 5 | merchant-104 | 2025-08-12 10:25:00 | 503 |
| 6 | merchant-221 | 2025-08-12 09:10:00 | 401 |
| 7 | merchant-221 | 2025-08-12 09:10:00 | 200 |
| 8 | merchant-221 | 2025-08-12 13:00:00 | 500 |
Output
| client_id | session_no | session_start | session_end | calls | server_errors |
|---|---|---|---|---|---|
| merchant-104 | 1 | 2025-08-12 09:00:00 | 2025-08-12 09:50:00 | 3 | 1 |
| merchant-104 | 2 | 2025-08-12 10:20:01 | 2025-08-12 10:25:00 | 2 | 1 |
| merchant-221 | 1 | 2025-08-12 09:10:00 | 2025-08-12 09:10:00 | 2 | 0 |
| merchant-221 | 2 | 2025-08-12 13:00:00 | 2025-08-12 13:00:00 | 1 | 1 |
How to solve Sessionise API Calls and Count Server Errors
Sessionisation turns a stream of events into groups separated by idle gaps, and it is two window passes. First LAG(called_at) OVER (PARTITION BY client_id ORDER BY called_at, call_id) puts each call next to the client's previous one (the call_id tie-break fixes the order of calls in the same second). A call starts a session when there is no previous call or TIMESTAMPDIFF(SECOND, prev_at, called_at) > 1800. Measuring in seconds matters: TIMESTAMPDIFF(MINUTE, …) truncates, so a gap of 30 minutes and 1 second would read as 30 and wrongly continue the session.
Second, a running SUM of the 0/1 start flags, with an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame, numbers the sessions 1, 2, … per client — the first call always contributes a 1, so numbering starts at 1. After that the answer is a GROUP BY on (client, session): first and last call times, the call count and a conditional count of 5xx responses. Two calls in the same second are 0 seconds apart and always share a session.
The alternative avoids LAG: a call starts a session when no earlier call of the client lies within 1800 seconds before it (if any earlier call is that close, the immediately previous one is too), and a call's session number is the count of session starts up to and including it. It is quadratic per client; the window version is a single sort.
Reference solution (MySQL)
WITH gaps AS (
SELECT call_id, client_id, called_at, status_code,
LAG(called_at) OVER (PARTITION BY client_id ORDER BY called_at, call_id) AS prev_at
FROM ApiCall
), numbered AS (
SELECT client_id, called_at, status_code,
SUM(CASE WHEN prev_at IS NULL OR TIMESTAMPDIFF(SECOND, prev_at, called_at) > 1800 THEN 1 ELSE 0 END)
OVER (PARTITION BY client_id ORDER BY called_at, call_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no
FROM gaps
)
SELECT client_id, session_no,
MIN(called_at) AS session_start, MAX(called_at) AS session_end,
COUNT(*) AS calls,
SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS server_errors
FROM numbered
GROUP BY client_id, session_no
ORDER BY client_id, session_noAnother way
WITH starts AS (
SELECT s.call_id, s.client_id, s.called_at FROM ApiCall s
WHERE NOT EXISTS (
SELECT 1 FROM ApiCall p
WHERE p.client_id = s.client_id
AND (p.called_at < s.called_at OR (p.called_at = s.called_at AND p.call_id < s.call_id))
AND TIMESTAMPDIFF(SECOND, p.called_at, s.called_at) <= 1800
)
), labelled AS (
SELECT c.client_id, c.called_at, c.status_code,
(SELECT COUNT(*) FROM starts st
WHERE st.client_id = c.client_id
AND (st.called_at < c.called_at OR (st.called_at = c.called_at AND st.call_id <= c.call_id))) AS session_no
FROM ApiCall c
)
SELECT client_id, session_no, MIN(called_at) AS session_start, MAX(called_at) AS session_end,
COUNT(*) AS calls, SUM(IF(status_code >= 500, 1, 0)) AS server_errors
FROM labelled
GROUP BY client_id, session_no
ORDER BY client_id, session_no← Repeat Contact Rate by First-Ticket Month · Median Time to Resolve Incidents by Severity →