Moonboard Exploration

Holds

Overall

By Version

DuckDB Queries

WITH unnested AS (
    SELECT
        problem_id,
        grade,
        unnest(holds) AS holds
    FROM moonboard
), grade_total AS (
    SELECT grade, count(problem_id) AS routes
    FROM moonboard
    GROUP BY 1
)
SELECT
    grade,
    upper(holds[:1]) AS letter,
    holds[2:]::int AS number,
    count(problem_id) AS routes,
    count(problem_id)/grade_total.routes AS routes_pctg
FROM unnested
JOIN grade_total USING (grade)
GROUP BY 1,2,3,grade_total.routes
ORDER BY grade, letter, number

WITH unnested AS (
    SELECT
        moonboard_version,
        problem_id,
        grade,
        unnest(holds) AS holds
    FROM moonboard
), grade_total AS (
    SELECT moonboard_version, grade, count(problem_id) AS routes
    FROM moonboard
    GROUP BY 1,2
)
SELECT
    moonboard_version,
    grade,
    upper(holds[:1]) AS letter,
    holds[2:]::int AS number,
    grade_total.routes AS grade_routes,
    count(problem_id) AS routes,
    count(problem_id)/grade_total.routes AS routes_pctg
FROM unnested
JOIN grade_total USING (moonboard_version, grade)
GROUP BY moonboard_version, grade, letter, number, grade_total.routes
ORDER BY moonboard_version, grade, letter, number