Clickhouse: Latency quantiles between two events, every hour.

SET param_tableName = 'my_table';
SET param_startDate = '2024-11-01';

WITH pairs AS (
    WITH
        toStartOfInterval(fromUnixTimestamp64Milli(timestamp_ms), INTERVAL 1 HOUR) AS bucket_start,
        leadInFrame(action) OVER (
            PARTITION BY client_id
            ORDER BY timestamp_ms
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_action,
        leadInFrame(timestamp_ms) OVER (
            PARTITION BY client_id
            ORDER BY timestamp_ms
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_timestamp_ms
    SELECT
        client_id,
        bucket_start,
        timestamp_ms,
        next_timestamp_ms,
        action,
        next_action
    FROM {tableName:Identifier}
    WHERE fromUnixTimestamp64Milli(timestamp_ms) > {startDate:DateTime64}
),
grouped as (
    SELECT
        bucket_start,
        quantiles(0.5, 0.9, 0.95, 0.99)(age('milliseconds',
            fromUnixTimestamp64Milli(timestamp_ms),
            fromUnixTimestamp64Milli(next_timestamp_ms))
        ) q
    FROM pairs
    WHERE action = 'DESIRED_START_EVENT'
    AND next_action = 'DESIRED_END_EVENT'
    GROUP BY bucket_start
    ORDER BY bucket_start
)
SELECT
    bucket_start,
    q[1] p50,
    q[2] p90,
    q[3] p95,
    q[4] p99
FROM grouped;