Kinderkopje data: tables and fields

What is in the Kinderkopje database (SQLite), v0.1.0 (db55b20). Open the Data page of the tool to ask it questions in SQL (read only). The two views, v_team_points and v_results, are the easy way in.

Rankings, calendars and results come from the Union Cycliste Internationale (UCI): uci.org and DataRide. Team levels come from Wikipedia (CC BY-SA 4.0).

• team_snapshot: One row per captured UCI World Ranking (team ranking) date.
• team_rank: The teams of each captured ranking: position and points on that date.
• team_level: Which teams were WorldTeams or ProTeams in a season (from Wikipedia). Teams that are not listed are lower-level, mostly Continental.
• race_result: One row per captured race edition (a race in one year). Each edition has its own number on DataRide, UCI's results site.
• classification: A result list of a race: the General Classification or one stage (one DataRide event).
• result_row: One row per rider who scored UCI points in a result list.
• race: One row per race (men's or women's elite), the same race in every year whatever it was called. Races that changed their name are joined into one.
• race_name: The external names of each race: how a source wrote its name, with the years it was used. A race that changed sponsor has several.
• race_link: Where each race lives on the web: official site, Wikipedia, social media and FirstCycling.
• country: Every country code UCI uses, with its continent and UCI confederation, for filtering races, riders and teams by continent.
• queue_race: The competitions to capture, taken from the DataRide competition list pages you captured.
• queue_event: The events of a queued competition (General Classification, each stage), read from its DataRide competition page.
• kv: Texts and settings you edit on the pages, stored with the data.
• meta: Facts about the database file itself.
• v_team_points (view): team_rank with the season and date of its ranking: the easy way to ask about team points.
• v_results (view): Every point-scoring result with its race, in one table: the easy way to ask about riders and results.

team_snapshot

One row per captured UCI World Ranking (team ranking) date.

Links: team_rank.snapshot_id = team_snapshot.id

Example, which ranking dates have been captured?:

SELECT season, date, complete, (SELECT COUNT(*) FROM team_rank r WHERE r.snapshot_id = s.id) AS teams FROM team_snapshot s ORDER BY date

team_rank

The teams of each captured ranking: position and points on that date.

Links: snapshot_id -> team_snapshot.id; team_id also appears in team_level

Example, the top 10 of the newest captured ranking:

SELECT rank, code, name, points FROM v_team_points WHERE date = (SELECT MAX(date) FROM team_snapshot) ORDER BY rank LIMIT 10

team_level

Which teams were WorldTeams or ProTeams in a season (from Wikipedia). Teams that are not listed are lower-level, mostly Continental.

Links: team_id = team_rank.team_id

Example, worldteams and proteams per season:

SELECT season, level, COUNT(*) AS teams FROM team_level GROUP BY season, level ORDER BY season DESC, level

race_result

One row per captured race edition (a race in one year). Each edition has its own number on DataRide, UCI's results site.

Links: classification.uci_id = race_result.uci_id

Example, races captured so far, and by whom:

SELECT year, name, class, dates, contributor FROM race_result ORDER BY year, name

classification

A result list of a race: the General Classification or one stage (one DataRide event).

Links: uci_id -> race_result.uci_id; result_row.classification_id = classification.id

Example, how many rows scored points, out of how many:

SELECT r.name, r.year, c.label, c.total_rows, (SELECT COUNT(*) FROM result_row w WHERE w.classification_id = c.id) AS scoring_rows FROM classification c JOIN race_result r ON r.uci_id = c.uci_id ORDER BY r.year, r.name

result_row

One row per rider who scored UCI points in a result list.

Links: classification_id -> classification.id. The view v_results joins all three tables.

Example, the riders who scored most in the captured races:

SELECT rider, team, SUM(points) AS points, COUNT(*) AS results FROM v_results GROUP BY rider, team ORDER BY points DESC LIMIT 20

race

One row per race (men's or women's elite), the same race in every year whatever it was called. Races that changed their name are joined into one.

Links: race_name.race_id = race.race_id; race_result.race_id = race.race_id

Example, races and the years they cover:

SELECT name, category, first_year, last_year FROM race ORDER BY name

race_name

The external names of each race: how a source wrote its name, with the years it was used. A race that changed sponsor has several.

Links: race_id -> race.race_id

Example, every name each race has had:

SELECT r.name AS race, n.name AS external_name, n.first_year, n.last_year FROM race_name n JOIN race r ON r.race_id = n.race_id ORDER BY r.name, n.first_year

race_link

Where each race lives on the web: official site, Wikipedia, social media and FirstCycling.

Links: race_id -> race.race_id

Example, the links of every race that has any:

SELECT r.name, l.label, l.url FROM race_link l JOIN race r ON r.race_id = l.race_id ORDER BY r.name, l.kind

country

Every country code UCI uses, with its continent and UCI confederation, for filtering races, riders and teams by continent.

Links: country.code = queue_race.country, result_row.nation, team_rank.nation

Example, races captured per continent:

SELECT c.continent, COUNT(*) AS races FROM queue_race q JOIN country c ON c.code = q.country GROUP BY c.continent ORDER BY races DESC

queue_race

The competitions to capture, taken from the DataRide competition list pages you captured.

Links: uci_id = race_result.uci_id once the race has been captured; queue_event.uci_id = queue_race.uci_id

Example, races in the queue that are not captured yet:

SELECT q.year, q.name, q.start, q.hub FROM queue_race q WHERE q.uci_id NOT IN (SELECT uci_id FROM race_result) ORDER BY q.start

queue_event

The events of a queued competition (General Classification, each stage), read from its DataRide competition page.

Links: uci_id -> queue_race.uci_id; event_id = classification.event_id once captured

Example, events still to capture:

SELECT q.year, q.name, e.name AS event FROM queue_event e JOIN queue_race q ON q.uci_id = e.uci_id WHERE e.event_id NOT IN (SELECT event_id FROM classification WHERE event_id IS NOT NULL) ORDER BY q.start, e.event_id

kv

Texts and settings you edit on the pages, stored with the data.

Example, what is saved:

SELECT key, LENGTH(value) AS characters FROM kv ORDER BY key

meta

Facts about the database file itself.

Example, the schema version:

SELECT key, value FROM meta

v_team_pointsview

team_rank with the season and date of its ranking: the easy way to ask about team points.

Links: Group by team_id to follow one team through renames.

Example, one team over the years (uae has uci team number 595):

SELECT season, date, code, name, rank, points FROM v_team_points WHERE team_id = 595 ORDER BY date

v_resultsview

Every point-scoring result with its race, in one table: the easy way to ask about riders and results.

Example, points per team from the race results:

SELECT team, SUM(points) AS points, COUNT(DISTINCT uci_id) AS races FROM v_results GROUP BY team ORDER BY points DESC LIMIT 20