Feature: page to show most clinched segments/routes/connected routes

[jteresco]

In this AARoads post the top routes clinched by the most travelers was computed and listed. This could be a page where the global stats can be shown, then restricted by system, region, country, etc.

[jteresco]

Some queries used (thanks for the assists, ChatGPT) to get the tables posted in the AARoads thread.

SELECT route, COUNT(*) AS clinched_count FROM clinchedRoutes WHERE clinched = 1 GROUP BY route ORDER BY clinched_count DESC LIMIT 1;

SELECT cr.route, COUNT(*) AS clinched_count FROM clinchedRoutes cr JOIN listEntries le ON cr.traveler = le.traveler WHERE cr.clinched = 1 AND le.includeInRanks = 1 GROUP BY cr.route ORDER BY clinched_count DESC;

SELECT cr.route, COUNT(*) AS clinched_count, RANK() OVER (ORDER BY COUNT(*) DESC) AS rank FROM clinchedRoutes cr JOIN listEntries le ON cr.traveler = le.traveler JOIN routes r ON cr.route = r.root WHERE cr.clinched = 1 AND le.includeInRanks = 1 AND r.systemName != 'usai' GROUP BY cr.route ORDER BY clinched_count DESC;

SELECT cr.route, COUNT(*) AS clinched_count, RANK() OVER (ORDER BY COUNT(*) DESC) AS rank FROM clinchedRoutes cr JOIN listEntries le ON cr.traveler = le.traveler JOIN routes r ON cr.route = r.root JOIN regions reg ON r.region = reg.code WHERE cr.clinched = 1 AND le.includeInRanks = 1 AND r.systemName != 'usai' AND reg.country != 'USA' GROUP BY cr.route ORDER BY clinched_count DESC;