Steam Market Intelligence
A live, in-browser SQL analytics dashboard, real SQLite compiled to WebAssembly, querying a 1,400-title game-market dataset on every page load. No backend, no precomputed export, just a database and a query engine running on the visitor's machine.
This started as a SQL case study: a synthetic but statistically realistic game-market dataset and seven .sql files covering window functions, recursive CTEs, self-joins, and rolling calculations. The first version worked, but the dashboard was static HTML reading pre-exported JSON. It looked like a live analytics tool. It wasn't.
So I rebuilt it to ship two files — index.html and the raw steam_games.db — running SQLite compiled to WebAssembly (sql.js) in the browser. Nothing is precomputed; every number on the page came from a query that just ran on your machine.
db.exec() call the rest of the dashboard uses. You can run the sample queries or write your own SELECT against the real database, not a styled mockup of a terminal.
The dataset covers 1,400 deduplicated game listings across a 17-year release window (2008–2024), joined against a genre bridge table for category-level analysis.
Average positive-rating ratio per genre, joined across the many-to-many game↔genre bridge table, JOIN games_deduped → game_genres, GROUP BY genre_name. Adventure and Sandbox titles edge out the rest, though the spread across all 15 genres is tight (65–70%).
Games are bucketed by price tier with a CASE WHEN expression, then averaged for rating ratio, GROUP BY the derived bucket, not a stored column.
Two queries built for a publishing-deal use case: titles with excellent ratings but low visibility (a subquery + HAVING filter), and developers who are reliably good across 3+ releases rather than one lucky hit.
| Game | Developer | Price | Rating |
|---|---|---|---|
| Lost Wasteland | Quiet Harbor Games | $4.99 | |
| Radiant Empire: Throne | Nova Forge Studios | $4.99 | |
| Hollow Throne: Throne | Frostbyte Collective | $4.99 | |
| Forgotten Sanctuary: Area | Echo Chamber Games | $2.99 | |
| Drifting Voyage: Rift | Solar Anvil | $0.99 |
| Studio | Titles | Avg. Rating |
|---|---|---|
| Mega Pulse Studios | 59 | |
| Solar Anvil | 69 | |
| NeonByte | 66 | |
| Lonewolf Dev | 64 | |
| Stardust Pictures | 69 |
Four formulas power every chart on this page. Each tab below breaks down the formula, what it means, and a worked example using real rows from this exact dataset.
SELECT yr, releases,
SUM(releases) OVER (
ORDER BY yr
ROWS UNBOUNDED PRECEDING
) AS cumulative
FROM yearly_countsA window function re-evaluates SUM() per row, over rows up to the current one (ROWS UNBOUNDED PRECEDING). Unlike GROUP BY, it keeps one row per year while exposing the running total — exactly what the line chart plots.
yr releases cumulative 2008 87 87 2009 69 156 2010 85 241 2011 84 325 2012 72 397 2013 101 498 ← single best year 2024 88 1400 ← final total
rating = positive_ratings
/ (positive_ratings + negative_ratings)SQLite does integer division by default, so the numerator is cast with 1.0* to force a float result. Every "average rating" on this page, per genre, per price bucket, per studio, is the AVG() of this ratio computed per game, not a single global ratio, so one outlier game can't dominate a genre's number.
Game: "Lost Wasteland"
positive_ratings = 369 negative_ratings = 4
rating = 1.0 × 369 / (369 + 4)
= 369 / 373
= 0.989 → 98.9%WITH ranked AS (
SELECT genre_name, name,
RANK() OVER (
PARTITION BY genre_name
ORDER BY positive_ratings DESC
) AS rnk
FROM games_deduped d
JOIN game_genres g ON g.app_id = d.app_id
)
SELECT * FROM ranked WHERE rnk <= 3PARTITION BY resets the ranking counter per genre, so each genre gets its own top-3 rather than one global list dominated by the largest genre. SQLite has no QUALIFY, so the window function is wrapped in a CTE and filtered in an outer query.
genre_name name rnk Adventure "Sunken Citadel" 1 Adventure "Velvet Horizon" 2 Adventure "Glass Meridian" 3 Shooter "Iron Vanguard" 1 Shooter "Crimson Drift" 2 Shooter "Static Hollow" 3 → filtered from ALL ranked rows down to rnk <= 3 per genre
WITH RECURSIVE ancestry(genre_id, path, depth) AS ( SELECT genre_id, genre_name, 0 FROM genres WHERE parent_genre IS NULL UNION ALL SELECT g.genre_id, a.path || ' → ' || g.genre_name, a.depth+1 FROM genres g JOIN ancestry a ON g.parent_genre = a.path ) SELECT * FROM ancestry ORDER BY depth
The genres table is self-referencing — every row's parent_genre points at another row in the same table. A recursive CTE starts at the root (parent_genre IS NULL) and UNION ALLs each level down until no children remain, building the ancestry path as it goes.
depth 0: Games depth 1: Games → Action depth 1: Games → Adventure depth 1: Games → Strategy depth 2: Games → Action → Shooter depth 2: Games → Action → Platformer depth 2: Games → Adventure → RPG depth 2: Games → Strategy → Simulation depth 3: Games → Strategy → Simulation → Sandbox
There's no backend and no build step that runs SQL ahead of time. Everything below happens fresh, in-browser, every time the page opens:
sql.js loads and initializes a real SQLite engine compiled to WebAssembly, running entirely inside the page..db fetch that can 404, just one self-contained file.sql.js loads and initializes a real SQLite engine compiled to WebAssembly, running entirely inside the page.
The SQLite file is embedded directly in the page as base64, no separate .db fetch that can 404, just one self-contained file.
Five analysis queries execute live on load: cumulative release growth, genre breakdown, price-vs-rating, a "hidden gems" subquery, and consistent-studio aggregates, each with its execution time captured.
Query output feeds straight into the charts. Edit a query, refresh, and the dashboard's behavior changes immediately, no rebuild, no export script.
The bottom of the live page is a real query box wired to db.exec(), not a styled mockup. Below is an interactive walkthrough of the five sample queries it ships with, using the actual result sets they return against this dataset. Pick one and hit Run.
SELECT name, developer, positive_ratings, negative_ratings,
ROUND(1.0*positive_ratings/(positive_ratings+negative_ratings), 3) AS rating
FROM games_deduped
WHERE (positive_ratings+negative_ratings) >= 100
ORDER BY rating DESC
LIMIT 5;
steam_games.db · timing is simulated here, but identical on the live page where the engine genuinely runs the queryI deliberately didn't stand up a server to run these queries. Shipping the database file and running it client-side via WASM was a conscious trade-off, not a shortcut.