wca_medal_table.sql

% https://statistics.worldcubeassociation.org/database-query

SELECT Persons.wca_id, Persons.name, gold.total AS gold, silver.total AS silver, bronze.total AS bronze FROM Persons
LEFT JOIN (
  Select personId, count(*) total from Results where roundTypeId IN ('c', 'f') AND pos = 1 AND best > 0 AND countryId = 'Spain' GROUP BY personId
) AS gold ON Persons.wca_id = gold.personId
LEFT JOIN (
  Select personId, count(*) total from Results where roundTypeId IN ('c', 'f') AND pos = 2 AND best > 0 AND countryId = 'Spain' GROUP BY personId
) AS silver ON Persons.wca_id = silver.personId
LEFT JOIN (
  Select personId, count(*) total from Results where roundTypeId IN ('c', 'f') AND pos = 3 AND best > 0 AND countryId = 'Spain' GROUP BY personId
) AS bronze ON Persons.wca_id = bronze.personId
WHERE Persons.subid = 1 AND Persons.countryId = 'Spain' AND (gold.total > 0 OR silver.total > 0 OR bronze.total > 0)
ORDER BY gold.total DESC, silver.total DESC, bronze.total DESC

SELECT
  personId,
  personName,
  SUM(CASE pos WHEN 1 THEN 1 ELSE 0 END) gold,
  SUM(CASE pos WHEN 2 THEN 1 ELSE 0 END) silver,
  SUM(CASE pos WHEN 3 THEN 1 ELSE 0 END) bronze
FROM Results
WHERE countryId = 'Spain' AND pos < 4 AND best > 0 AND roundTypeId IN ('c', 'f')
GROUP BY personId
ORDER BY gold DESC, silver DESC, bronze DESC