Game analytics platform: player events, live dashboards and chat search in one SQL engine

docker run -d --name serenedb -p 7890:7890 \
  -e POSTGRES_PASSWORD=<password> serenedb/serenedb:26.11.2

psql -h localhost -p 7890 -U postgres

CREATE TABLE players (
    player_id    INTEGER,
    installed_at TIMESTAMP,
    platform     VARCHAR      -- android, ios or pc
);

CREATE TABLE events (
    ts         TIMESTAMP,
    player_id  INTEGER,
    session_id BIGINT,
    event      VARCHAR,       -- install, session_start, tutorial_done,
                              -- match_end, purchase or session_end
    value      DOUBLE         -- a match's score, a purchase's amount
);

CREATE TABLE chat (
    id        INTEGER,
    ts        TIMESTAMP,
    player_id INTEGER,
    channel   VARCHAR,        -- global or match
    message   VARCHAR
);

CREATE TABLE reports (
    ts          TIMESTAMP,
    reporter_id INTEGER,
    player_id   INTEGER,      -- the reported player
    reason      VARCHAR       -- spam, abuse or cheating
);

CREATE TEXT SEARCH DICTIONARY chat_text AS
    split_text(case := 'lower')
    WITH (frequency, position, norm);

CREATE INDEX chat_idx ON chat
    USING inverted (id, ts, player_id, channel, message chat_text);

-- Two weeks of a free-to-play multiplayer game, generated in SQL. Season 3
-- opened on Monday 2026-10-19, and 2,400 players installed the game by
-- Sunday 2026-11-01. rnd(a, b) is a number in [0, 1) taken from an md5,
-- so every run gives the same rows.
CREATE MACRO rnd(a, b) AS (md5_number_lower(a || ':' || b) % 1000000) / 1000000.0;

INSERT INTO players
SELECT row_number() OVER (ORDER BY installed_at)::INTEGER, installed_at, platform
FROM (
    SELECT DATE '2026-10-19' + floor(14 * power((p - 0.5) / 2400, 1.8))::INTEGER
               + to_seconds(28800 + floor(54000 * rnd(p, 'at'))::BIGINT) AS installed_at,
           CASE WHEN rnd(p, 'os') < 0.5 THEN 'android'
                WHEN rnd(p, 'os') < 0.8 THEN 'ios' ELSE 'pc' END AS platform
    FROM range(1, 2401) AS r(p)
);

-- Sessions, and what happened in them. Patch 1.4.0 (2026-10-26 10:00) broke
-- tutorial step 3 on Android until hotfix 1.4.1 (2026-10-28 14:00), so the
-- Android players who installed in between could not finish the tutorial.
-- Players who do not finish it never come back. From 2026-10-29, player
-- 1906 cheats: every match scores three times the usual.
INSERT INTO events
WITH p AS (
    SELECT *,
           rnd(player_id, 'tutorial') < 0.82
             AND NOT (platform = 'android'
                      AND installed_at >= TIMESTAMP '2026-10-26 10:00:00'
                      AND installed_at <  TIMESTAMP '2026-10-28 14:00:00') AS onboarded
    FROM players
), days AS (
    SELECT p.*, k, installed_at::DATE + k::INTEGER AS day
    FROM p, range(14) AS r(k)
    WHERE installed_at::DATE + k::INTEGER <= DATE '2026-11-01'
      AND (k = 0 OR (onboarded AND rnd(player_id, k)
                       < 0.45 * power(k, -0.5) * (0.4 + rnd(player_id, 'keen'))))
), sessions AS (
    SELECT * FROM (
        SELECT player_id, onboarded, day, k, j,
               player_id * 100 + k * 3 + j AS session_id,
               CASE WHEN k = 0 THEN installed_at + to_hours(3 * j)
                    ELSE day + to_seconds(28800 + 18000 * j
                                          + floor(14400 * rnd(player_id, 'at' || k || j))
                                            ::BIGINT)
               END AS start_at,
               CASE WHEN onboarded THEN 8 + floor(36 * rnd(player_id, 'len' || k || j))
                    ELSE 4 + floor(6 * rnd(player_id, 'len' || k || j)) END::INTEGER AS minutes
        FROM days, range(3) AS s(j)
        WHERE j < 1 + floor(2.6 * rnd(player_id, 'n' || k)) AND (onboarded OR j = 0)
    )
    WHERE start_at < day + INTERVAL '23 hours'
)
SELECT installed_at, player_id, player_id * 100, 'install', NULL FROM players
UNION ALL
SELECT start_at, player_id, session_id, 'session_start', NULL FROM sessions
UNION ALL
SELECT start_at + INTERVAL '7 minutes', player_id, session_id, 'tutorial_done', NULL
FROM sessions WHERE onboarded AND k = 0 AND j = 0
UNION ALL
SELECT start_at + to_minutes(12 + 9 * m), player_id, session_id, 'match_end',
       (600 + floor(1800 * rnd(session_id, 'score' || m)))
         * CASE WHEN player_id = 1906 AND start_at >= DATE '2026-10-29' THEN 3 ELSE 1 END
FROM sessions, range(4) AS g(m)
WHERE onboarded AND 12 + 9 * m < minutes
UNION ALL
SELECT start_at + INTERVAL '2 minutes', player_id, session_id, 'purchase',
       [4.99, 9.99, 19.99][1 + floor(3 * rnd(session_id, 'sku'))::INTEGER]
FROM sessions
WHERE onboarded AND rnd(player_id, 'payer') < 0.07 AND rnd(session_id, 'buy') < 0.25
UNION ALL
SELECT start_at + to_minutes(minutes), player_id, session_id, 'session_end', NULL FROM sessions;

-- Chat: talk after matches; the Android players stuck in the tutorial;
-- players 386 and 1310, who insult their teams; and three spam accounts
-- that post a "free gems" link on 2026-10-30 from 19:00 to 21:00.
INSERT INTO chat
SELECT row_number() OVER (ORDER BY ts, player_id)::INTEGER, ts, player_id, channel, message
FROM (
    SELECT ts - INTERVAL '20 seconds' AS ts, player_id, 'match' AS channel,
           CASE WHEN player_id IN (386, 1310)
                THEN ['uninstall noob', 'worst team ever, trash',
                      'report this noob, he keeps feeding', 'you are all trash lol'
                     ][1 + floor(4 * rnd(session_id, 'abuse' || ts))::INTEGER]
                ELSE ['gg', 'gg wp', 'nice shot', 'anyone up for ranked?',
                      'lag on eu servers tonight', 'need one more for a squad',
                      'that last round was close', 'who wants to duo?',
                      'how do i unlock the new map?', 'season 3 skins look great',
                      'rematch?', 'thanks for the carry', 'my team left early again',
                      'ranked queue is fast today', 'lol that was wild', 'good luck everyone'
                     ][1 + floor(16 * rnd(session_id, 'line' || ts))::INTEGER]
           END AS message
    FROM events
    WHERE event = 'match_end'
      AND rnd(session_id, 'talk' || ts)
            < CASE WHEN player_id IN (386, 1310) THEN 0.35 ELSE 0.08 END
    UNION ALL
    SELECT installed_at + INTERVAL '3 minutes', player_id, 'global',
           ['stuck on tutorial step 3, the button does nothing',
            'tutorail step 3 wont load after the update',
            'is the tutorial bugged on android? cant finish it',
            'tutorial freezes at step 3 every time',
            'cant get past the tutoral, any fix?',
            'step 3 of the tutorial is broken since the patch',
            'reinstalled twice, still stuck in the tutorial',
            'tutorial broke after 1.4.0 on my phone'
           ][1 + floor(8 * rnd(player_id, 'stuck'))::INTEGER]
    FROM players
    WHERE platform = 'android'
      AND installed_at >= TIMESTAMP '2026-10-26 10:00:00'
      AND installed_at <  TIMESTAMP '2026-10-28 14:00:00'
      AND rnd(player_id, 'complain') < 0.3
    UNION ALL
    SELECT TIMESTAMP '2026-10-30 19:00:00'
               + to_seconds(360 * n + 120 * b + floor(100 * rnd(b, n))::BIGINT),
           90000 + b, 'global',
           ['FREE GEMS at gemz-free.example, no survey',
            'free gemz here >> gemz-free.example',
            'get fre gems now: gemz-free.example',
            '1000 free gems, claim fast at gemz-free.example'
           ][1 + floor(4 * rnd(b, 'msg' || n))::INTEGER]
    FROM range(1, 4) AS bots(b), range(20) AS t(n)
);

-- Players report the spam, the insults and the cheater.
INSERT INTO reports
SELECT ts + to_seconds(20 + floor(100 * rnd(id, 'delay'))::BIGINT),
       1 + floor(2400 * rnd(id, 'reporter'))::INTEGER, player_id,
       CASE WHEN player_id > 90000 THEN 'spam' ELSE 'abuse' END
FROM chat
WHERE player_id IN (386, 1310, 90001, 90002, 90003) AND rnd(id, 'report') < 0.4
UNION ALL
SELECT ts + INTERVAL '1 minute', 1 + floor(2400 * rnd(session_id, 'by' || ts))::INTEGER,
       player_id, 'cheating'
FROM events
WHERE event = 'match_end' AND player_id = 1906 AND ts >= DATE '2026-10-29'
  AND rnd(session_id, 'report' || ts) < 0.4;

VACUUM (REFRESH_TABLE) chat;
SELECT (SELECT count(*) FROM players) AS players,
       (SELECT count(*) FROM events) AS events,
       (SELECT count(*) FROM chat) AS chat,
       (SELECT count(*) FROM reports) AS reports;

SELECT CASE WHEN installed_at < TIMESTAMP '2026-10-26 10:00:00' THEN '1.3.2'
            WHEN installed_at < TIMESTAMP '2026-10-28 14:00:00' THEN '1.4.0'
            ELSE '1.4.1' END AS release,
       CASE WHEN platform = 'android' THEN 'android' ELSE 'ios, pc' END AS platform,
       count(*) AS installs,
       round(100.0 * count(*) FILTER (WHERE tutorial) / count(*), 1) AS tutorial_pct,
       round(100.0 * count(*) FILTER (WHERE played) / count(*), 1) AS match_pct,
       round(100.0 * count(*) FILTER (WHERE back_d1) / count(*), 1) AS d1_pct
FROM (
    SELECT p.player_id, p.platform, p.installed_at,
           bool_or(e.event = 'tutorial_done') AS tutorial,
           bool_or(e.event = 'match_end') AS played,
           bool_or(e.ts::DATE = p.installed_at::DATE + 1) AS back_d1
    FROM players p JOIN events e USING (player_id)
    WHERE p.installed_at < TIMESTAMP '2026-11-01 00:00:00'
    GROUP BY ALL
)
GROUP BY ALL ORDER BY release, platform;

SELECT p.installed_at::DATE AS cohort,
       count(DISTINCT p.player_id) AS installs,
       round(100.0 * count(DISTINCT e.player_id)
                     FILTER (WHERE e.ts::DATE = p.installed_at::DATE + 1)
             / count(DISTINCT p.player_id), 1) AS d1_pct,
       CASE WHEN p.installed_at::DATE + 7 <= DATE '2026-11-01' THEN
            round(100.0 * count(DISTINCT e.player_id)
                          FILTER (WHERE e.ts::DATE = p.installed_at::DATE + 7)
                  / count(DISTINCT p.player_id), 1)
       END AS d7_pct
FROM players p JOIN events e USING (player_id)
WHERE p.installed_at < TIMESTAMP '2026-11-01 00:00:00'
GROUP BY cohort ORDER BY cohort;

SELECT c.ts::DATE AS day, count(*) AS messages,
       count(*) FILTER (WHERE NOT contains(c.message, 'tutorial')) AS misspelled,
       count(DISTINCT c.player_id) AS players,
       count(*) FILTER (WHERE p.platform = 'android') AS android
FROM chat_idx c JOIN players p USING (player_id)
WHERE c.message @@ ts_levenshtein('tutorial', 1)
GROUP BY day ORDER BY day;

SELECT c.player_id, count(*) AS messages,
       min(c.ts)::TIME AS first_at, max(c.ts)::TIME AS last_at,
       r.reports
FROM chat_idx c
LEFT JOIN (SELECT player_id, count(*) AS reports FROM reports GROUP BY player_id) r
       USING (player_id)
WHERE c.message @@ ts_levenshtein('free', 1)
  AND c.message @@ ts_levenshtein('gems', 1)
GROUP BY c.player_id, r.reports
ORDER BY c.player_id;

CREATE JOB events_history EVERY 1 DAY OFFSET 2 HOURS AS $$
    COPY (SELECT *, ts::DATE AS day FROM events
          WHERE ts < current_date - INTERVAL '90 days')
    TO '/tmp/events_history' (FORMAT parquet, PARTITION_BY (day), APPEND);
    DELETE FROM events WHERE ts < current_date - INTERVAL '90 days';
$$;