SELECT
grade,
CASE
WHEN grade IN ('5+') THEN 'V2'
WHEN grade IN ('6A', '6A+') THEN 'V3'
WHEN grade IN ('6B', '6B+') THEN 'V4'
WHEN grade IN ('6C', '6C+') THEN 'V5'
WHEN grade IN ('7A') THEN 'V6'
WHEN grade IN ('7A+') THEN 'V7'
WHEN grade IN ('7B', '7B+') THEN 'V8'
WHEN grade IN ('7C') THEN 'V9'
WHEN grade IN ('7C+') THEN 'V10'
WHEN grade IN ('8A') THEN 'V11'
WHEN grade IN ('8A+') THEN 'V12'
WHEN grade IN ('8B') THEN 'V13'
WHEN grade IN ('8B+') THEN 'V14'
END AS grade_v,
count(DISTINCT moonboard_version) AS versions,
count(problem_id) AS routes,
count(DISTINCT setter_id) AS setters,
count(problem_id) FILTER (WHERE is_benchmark) AS benchmarks,
count(DISTINCT setter_id) FILTER (WHERE is_benchmark) AS benchmark_setters,
sum(repeats) AS total_climbs
FROM moonboard
GROUP BY 1
ORDER BY grade ASC
SELECT
moonboard_version,
grade,
CASE
WHEN grade IN ('5+') THEN 'V2'
WHEN grade IN ('6A', '6A+') THEN 'V3'
WHEN grade IN ('6B', '6B+') THEN 'V4'
WHEN grade IN ('6C', '6C+') THEN 'V5'
WHEN grade IN ('7A') THEN 'V6'
WHEN grade IN ('7A+') THEN 'V7'
WHEN grade IN ('7B', '7B+') THEN 'V8'
WHEN grade IN ('7C') THEN 'V9'
WHEN grade IN ('7C+') THEN 'V10'
WHEN grade IN ('8A') THEN 'V11'
WHEN grade IN ('8A+') THEN 'V12'
WHEN grade IN ('8B') THEN 'V13'
WHEN grade IN ('8B+') THEN 'V14'
END AS grade_v,
count(DISTINCT moonboard_version) AS versions,
count(problem_id) AS routes,
count(DISTINCT setter_id) AS setters,
count(problem_id) FILTER (WHERE is_benchmark) AS benchmarks,
count(DISTINCT setter_id) FILTER (WHERE is_benchmark) AS benchmark_setters,
sum(repeats) AS total_climbs
FROM moonboard
GROUP BY 1,2
ORDER BY moonboard_version, grade ASC