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;

The output:

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;

The output:

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;

The output:

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;

The output:

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

The output:

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

The output:

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;

The output:

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;

The output:

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;

The output:

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;

The output:

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;

The output: