Sessionise API Calls and Count Server Errors

Hard
346.

Sessionise API Calls and Count Server Errors

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

ColumnType
call_idint
client_idvarchar
called_atdatetime
status_codeint

Primary key: call_id.

One row per API call. Client ids are lower case, e.g. 'merchant-104'.

Example

The rows must come back in the order shown.

ApiCall

call_idclient_idcalled_atstatus_code
1merchant-1042025-08-12 09:00:00200
2merchant-1042025-08-12 09:20:00502
3merchant-1042025-08-12 09:50:00200
4merchant-1042025-08-12 10:20:01200
5merchant-1042025-08-12 10:25:00503
6merchant-2212025-08-12 09:10:00401
7merchant-2212025-08-12 09:10:00200
8merchant-2212025-08-12 13:00:00500

Output

client_idsession_nosession_startsession_endcallsserver_errors
merchant-10412025-08-12 09:00:002025-08-12 09:50:0031
merchant-10422025-08-12 10:20:012025-08-12 10:25:0021
merchant-22112025-08-12 09:10:002025-08-12 09:10:0020
merchant-22122025-08-12 13:00:002025-08-12 13:00:0011
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
call_idint
client_idvarchar
called_atdatetime
status_codeint

Primary key: call_id.

One row per API call. Client ids are lower case, e.g. 'merchant-104'.

Examples

Example 1

ApiCall

call_idclient_idcalled_atstatus_code
1merchant-1042025-08-12 09:00:00200
2merchant-1042025-08-12 09:20:00502
3merchant-1042025-08-12 09:50:00200
4merchant-1042025-08-12 10:20:01200
5merchant-1042025-08-12 10:25:00503
6merchant-2212025-08-12 09:10:00401
7merchant-2212025-08-12 09:10:00200
8merchant-2212025-08-12 13:00:00500

Output

client_idsession_nosession_startsession_endcallsserver_errors
merchant-10412025-08-12 09:00:002025-08-12 09:50:0031
merchant-10422025-08-12 10:20:012025-08-12 10:25:0021
merchant-22112025-08-12 09:10:002025-08-12 09:10:0020
merchant-22122025-08-12 13:00:002025-08-12 13:00:0011

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_no

Another 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 →