DEV Community

Cover image for SQL Data Exploration for BoardGameGeek datasets
Bojan Drangovski
Bojan Drangovski

Posted on • Originally published at ognard.com

SQL Data Exploration for BoardGameGeek datasets

Based on the BoardGameGeek data fetched with the Python script (and this script) I've created before, I have made some SQL data exploration for the datasets that are available in the repository. You can check the SQL queries in the queries.sql file in this repo, or clone the repo to your machine and use the datasets for your own exploration needs.

For start I will find number of games published in each year, and which year have the highest number of published games.

SELECT year_published, COUNT(*) total_games
FROM bgg.items
WHERE type = 'boardgame'
    AND year_published <> '0'
    AND rating <> '0'
    AND year_published < 2024
    AND year_published > 2012
GROUP BY year_published
ORDER BY year_published;
Enter fullscreen mode Exit fullscreen mode

The output:

year_published total_games
2013 2539
2014 2941
2015 3158
2016 3439
2017 3619

...

Next exploration is about the average rating of games per year for the past 20 years:

SELECT year_published, ROUND(AVG(rating), 3) AS rating
FROM bgg.items
WHERE type = 'boardgame'
    AND year_published <> '0'
    AND rating <> '0'
    AND year_published < 2024
    AND year_published > 2012
GROUP BY year_published
ORDER BY year_published;
Enter fullscreen mode Exit fullscreen mode

The output:

year_published rating
2013 6.284
2014 6.289
2015 6.369
2016 6.517
2017 6.645

...

Followed by a discovery for the top 10 board game categories in the past 10 years:

SELECT categories, SUM(games) AS total_games
FROM (
    SELECT categories, COUNT(*) AS games
    FROM bgg.items AS items
    LEFT JOIN bgg.categories AS categories
    USING (game_id)
    WHERE year_published <> '0'
        AND categories <> '0'
        AND type = 'boardgame'
        AND year_published > 2012
        AND year_published < 2024
    GROUP BY categories
) AS category_counts
GROUP BY categories
ORDER BY total_games DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

The output:

categories total_games
Card Game 14860
Party Game 5345
Wargame 5129
Fantasy 4928
Children's Game 4164

...

And also, beside the top categories, here are the 10 most popular mechanics in the past 10 years:

SELECT mechanics, SUM(games) AS total_games
FROM (
    SELECT mechanics, COUNT(*) AS games
    FROM bgg.items AS items
    LEFT JOIN bgg.mechanics AS mechanics
    USING (game_id)
    WHERE year_published <> '0'
        AND mechanics <> '0'
        AND type = 'boardgame'
        AND year_published > 2012
        AND year_published < 2024
    GROUP BY mechanics
) AS mechanics_counts
GROUP BY mechanics
ORDER BY total_games DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

The output:

mechanics total_games
Dice Rolling 10461
Hand Management 7833
Set Collection 5305
Variable Player Powers 4215
Cooperative Game 4195

...

Next in the list are the top five categories in the past 10 years with total games per category / per year ratio

WITH top_categories AS (
    SELECT categories, COUNT(*) AS total_games
    FROM bgg.items AS items
    LEFT JOIN bgg.categories AS category
    USING (game_id)
    WHERE type = 'boardgame'
        AND year_published <> '0'
        AND rating <> '0'
        AND year_published > 2012
        AND year_published < 2024
    GROUP BY categories
    ORDER BY total_games DESC
    LIMIT 5
)
SELECT year_published, categories, COUNT(*) AS total_games
FROM bgg.items AS items
LEFT JOIN bgg.categories AS category
USING (game_id)
WHERE type = 'boardgame'
    AND year_published <> '0'
    AND rating <> '0'
    AND year_published > 2012
    AND year_published < 2024
    AND categories IN (SELECT categories FROM top_categories)
GROUP BY year_published, categories
ORDER BY year_published
Enter fullscreen mode Exit fullscreen mode

The output:

year_published categories total_games
2013 Card Game 817
2013 Dice 238
2013 Fantasy 234
2013 Party Game 269
2013 Wargame 325
2014 Card Game 1022

...

In addition, I've done the similar exploration for the top five mechanics in the past 10 years with total games per category / per year ratio:

WITH top_mechanics AS (
    SELECT mechanics, COUNT(*) AS total_games
    FROM bgg.items AS items
    LEFT JOIN bgg.mechanics AS mechanic
    USING (game_id)
    WHERE type = 'boardgame'
        AND year_published <> '0'
        AND rating <> '0'
        AND year_published > 2012
        AND year_published < 2024
    GROUP BY mechanics
    ORDER BY total_games DESC
    LIMIT 5
)
SELECT year_published, mechanics, COUNT(*) AS total_games
FROM bgg.items AS items
LEFT JOIN bgg.mechanics AS mechanic
USING (game_id)
WHERE type = 'boardgame'
    AND year_published <> '0'
    AND rating <> '0'
    AND year_published > 2012
    AND year_published < 2024
    AND mechanics IN (SELECT mechanics FROM top_mechanics)
GROUP BY year_published, mechanics
ORDER BY year_published
Enter fullscreen mode Exit fullscreen mode

The output:

year_published mechanics total_games
2013 Cooperative Game 148
2013 Dice Rolling 635
2013 Hand Management 487
2013 Set Collection 327
2013 Variable Player Powers 260

...

Next are the top 15 most active publishers in the past 10 years,

SELECT publishers, COUNT(*) AS total_games
FROM bgg.items as items
LEFT JOIN bgg.publishers
USING (game_id)
WHERE publishers <> '0'
    AND year_published > 2012
    AND year_published < 2024
    AND type = 'boardgame'
    AND publishers NOT IN (
        '(Self-Published)',
        '(Web published)',
        'Inc.',
        'LLC',
        'Ltd.',
        '(Unknown)',
        '(Looking for a publisher)',
        '(Public Domain)'
    )
GROUP BY publishers
ORDER BY total_games DESC
LIMIT 15;
Enter fullscreen mode Exit fullscreen mode

The output:

publishers total_games
Hasbro 506
Pegasus Spiele 468
Hobby World 397
Korea Boardgames Co. 384
Rebel Sp. z o.o. 369

...

top 15 game designers in the past 10 years,

SELECT designers, COUNT(*) AS total_games
FROM bgg.items AS items
LEFT JOIN bgg.designers
USING (game_id)
WHERE type = "boardgame"
    AND year_published > 2012
    AND year_published < 2024
    AND designers NOT IN (
        '0',
        '(Uncredited)',
        'Jr.'
    )
GROUP BY designers
ORDER BY total_games DESC
LIMIT 15;
Enter fullscreen mode Exit fullscreen mode

The output:

designers total_games
Reiner Knizia 203
Paul Rohrbaugh 130
Joseph Miranda 110
Prospero Hall 109
Charles Darrow 108

...

and the top 15 game artists in the past 10 years:

SELECT artists, COUNT(*) AS total_games
FROM bgg.items AS items
LEFT JOIN bgg.artists
USING (game_id)
WHERE type = "boardgame"
    AND year_published > 2012
    AND year_published < 2024
    AND artists NOT IN (
        '0',
        '(Uncredited)'
    )
GROUP BY artists
ORDER BY total_games DESC
LIMIT 15;
Enter fullscreen mode Exit fullscreen mode

The output:

artists total_games
Joe Youst 168
Mark Mahaffey 135
Ilya Kudriashov 123
Klemens Franz 109
Michael Menzel 106

...

There are two more discoveries that I have made, one being the top 15 categories in the past 10 years that have the highest average rating (although, these are not ordered by the games published in these categories; some other categories have more games published)

SELECT categories, ROUND(AVG(rating), 3) AS average_rating, COUNT(*) AS total_games
FROM bgg.items AS items
LEFT JOIN bgg.categories AS categories
USING (game_id)
WHERE type = 'boardgame'
    AND categories <> '0'
    AND year_published > 2012
    AND year_published < 2024
GROUP BY categories
ORDER BY average_rating DESC
LIMIT 15;
Enter fullscreen mode Exit fullscreen mode

The output:

categories average_rating total_games
World War II 6.504 1448
Vietnam War 6.473 96
Civilization 6.437 466
Renaissance 6.382 311
Civil War 6.353 199

...

And finally, the top 15 mechanics in the past 10 years that have the highest average rating:

SELECT mechanics, ROUND(AVG(rating), 3) AS average_rating, COUNT(*) AS total_games
FROM bgg.items AS items
LEFT JOIN bgg.mechanics AS mechanics
USING (game_id)
WHERE type = 'boardgame'
    AND mechanics <> '0'
    AND year_published > 2012
    AND year_published < 2024
GROUP BY mechanics
ORDER BY average_rating DESC
LIMIT 15;
Enter fullscreen mode Exit fullscreen mode

The output:

mechanics average_rating total_games
Auction: English 8.348 2
Tags 7.6 32
Neighbor Scope 7.557 8
Auction Compensation 7.374 2
Ratio / Combat Results Table 7.374 137

...

Top comments (0)