SQL Schemas

Winrate has three SQL datasets. The SQL console and the assistant read ranked and pro games in ClickHouse. The SQL tab on a player's page reads a SQLite database of that player's games, and on a match page the assistant reads a SQLite database of that one match.

The table and column lists on this page are generated from the code when the site is built: the ClickHouse lists from the schema the console and the assistant use, the SQLite lists from the migrations that create each database.

Tables

ClickHouse: ranked and pro games

• summoner_match (219 columns): One row per player per match (main table)
• match (48 columns): One row per match (game-level data)
• summoner_match_timeline (50 columns): Build paths and early game stats
• summoner_match_timeline_frame (59 columns): Per-minute frame data
• match_timeline_event (35 columns): Individual match events (kills, items, etc.)
• champion_pairwise_stats (10 columns): Precomputed synergy/counter stats
• summoner_leaderboard (18 columns): Aggregated ranked ladder data for leaderboard queries
• pro_match (159 columns): One row per pro team-side game
• pro_summoner_match (132 columns): One row per pro player-game

summoner_leaderboard has one row per player per region.

SQLite: one player

• summoner_match (38 columns): primary key match_id, puuid
• summoner_match_stats (91 columns): primary key match_id, puuid
• summoner_match_timeline (23 columns): primary key match_id, puuid

These hold the player's own rows from ClickHouse's summoner_match and summoner_match_timeline, with some of their columns. summoner_match is split in two: the scoreboard stays in summoner_match, and damage, economy, objective and challenge stats go to summoner_match_stats.

SQLite: one match

• match (49 columns): primary key match_id
• match_timeline_event (29 columns): primary key match_id, event_id
• metadata (2 columns): primary key key
• summoner_match (86 columns): primary key match_id, puuid
• summoner_match_challenges (81 columns): primary key match_id, puuid
• summoner_match_timeline_frame (31 columns): primary key match_id, puuid, frame_number
• win_probability (6 columns): primary key match_id, frame_number

match has the game's totals and objective counts, summoner_match the ten players' final scoreboard, and summoner_match_challenges their advanced combat, vision and laning metrics. summoner_match_timeline_frame has each player's state at every frame, match_timeline_event the kills, objectives and item events, and win_probability blue side's win probability at every frame, with the gold and kill difference. metadata is the database's own sync state.

Encoded values

• tier and rank are NULL for an unranked player, and always NULL in a match's SQLite database.
• win is a Bool in ClickHouse's summoner_match and 1 or 0 in the pro and SQLite tables. summoner_match_timeline and ClickHouse's summoner_match_timeline_frame call it won.
• team_id is 100 for blue side and 200 for red, as in match's team_100_* and team_200_* columns. The pro tables have side instead: Blue or Red.
• game_duration is in seconds.
• In a match's SQLite database, timestamp is milliseconds since the game started.
• champion_id, the item_* columns and the perk_* columns hold Riot's ids. The pro tables name champions instead, in champion_name and the pick_* and ban_* columns.
• pro_match.position is always TEAM. The pro tables have league, year, split and playoffs (1 for a playoff game) where the ranked tables have region and tier.

How the tables join

In ClickHouse:

• summoner_match, summoner_match_timeline and summoner_match_timeline_frame share (match_id, puuid). A frame adds frame_number.
• match and match_timeline_event join to them on match_id. An event's puuid, killer_puuid and victim_puuid are players.
• The timeline, frame and event tables repeat champion_id, position, patch, queue_id, region and tier, so many questions need no join.
• These five tables are partitioned by patch. Filter every table in a join by patch.
• champion_pairwise_stats has one row per champion and position, other champion and position, is_ally (true for teammates, false for opponents), patch and tier. Sum games and wins over the patches and tiers you want.
• summoner_leaderboard joins to summoner_match on puuid.
• pro_summoner_match joins to its team's pro_match row on (match_id, side). The pro tables share no ids with the ranked ones.

In SQLite:

• A player's three tables join on (match_id, puuid).
• A match's summoner_match, summoner_match_challenges and summoner_match_timeline_frame join on puuid. Frames, events and win_probability join on frame_number.

Sample queries

A champion's win rate by role. Ahri is champion_id 103, and tier >= 6 is Diamond and above:

1SELECT2  position,3  count() AS games,4  round(avg(win) * 100, 1) AS win_rate5FROM summoner_match6WHERE patch = 16207  AND queue_id = 4208  AND tier >= 69  AND champion_id = 10310GROUP BY position11ORDER BY games DESC

A join. Gold difference at 10 minutes is in summoner_match_timeline and vision score is in summoner_match, here for Challenger junglers, wins against losses. Both tables are filtered:

1SELECT2  sm.win,3  count() AS games,4  round(avg(t.gold_diff_at_10)) AS gold_diff_at_10,5  round(avg(sm.vision_score), 1) AS vision_score6FROM summoner_match AS sm7INNER JOIN summoner_match_timeline AS t8  ON t.match_id = sm.match_id AND t.puuid = sm.puuid9WHERE sm.patch = 162010  AND sm.queue_id = 42011  AND sm.tier = 912  AND sm.position = 'JUNGLE'13  AND t.patch = 162014  AND t.tier = 915  AND t.position = 'JUNGLE'16GROUP BY sm.win17ORDER BY sm.win

One player's roles, in the SQL tab on their page (SQLite):

1SELECT2  m.position,3  COUNT(*) AS games,4  ROUND(AVG(m.win) * 100.0, 1) AS win_rate,5  ROUND(AVG(s.gold_per_minute)) AS gold_per_minute,6  ROUND(AVG(t.cs_diff_at_10), 1) AS cs_diff_at_107FROM summoner_match AS m8JOIN summoner_match_stats AS s9  ON s.match_id = m.match_id AND s.puuid = m.puuid10JOIN summoner_match_timeline AS t11  ON t.match_id = m.match_id AND t.puuid = m.puuid12GROUP BY m.position13ORDER BY games DESC

Columns

Each line is a column and its type. ClickHouse types are written as DESCRIBE TABLE prints them. SQLite columns are TEXT, INTEGER or REAL, and nullable marks the ones that can be NULL.

ClickHouse columns

Player columns (SQLite)

Match columns (SQLite)