dfabulich
commented
3 years ago It would also be nice to emphasize the most important competitions. This query does an acceptable job:
SELECT series, sum(interest)
FROM competitions
JOIN compgames USING (compid)
JOIN (
SELECT gameid, count(reviews.id) as interest
FROM reviews
GROUP BY gameid
) interests USING (gameid)
WHERE series is not null
GROUP BY series
ORDER BY sum(interest) desc
LIMIT 5;+----------------------------------------+---------------+
| series | sum(interest) |
+----------------------------------------+---------------+
| XYZZY Awards | 76570 |
| Annual Interactive Fiction Competition | 25719 |
| Interactive Fiction Top 50 of All Time | 21275 |
| Spring Thing | 1624 |
| Ectocomp | 1227 |
+----------------------------------------+---------------+
When you click the "Browse Competitions" page, you see a list of individual competition events: French Comp 2021, IFComp 2020, Ectocomp 2020, XYZZY 2019, and so forth.
Each of the annual competitions are part of a "series," but there's no way to see a list of competition series, e.g. "IFComp" "XYZZY" "Spring Thing". (You can click on a "series" link on a given competition to see a list of competitions in that series, but there's still no way to see a list of series.)
IMO it would make sense to have a column on the "Browse Competitions" page that lists out a half dozen series, perhaps in chronological order.