UK Railway Analytics

KPI Snapshot

Key Insights & Decisions

• Morning peak services exhibit the highest delay rates → targeted operational focus required
• Weekend services underperform due to maintenance schedules → opportunity for timetable optimisation
• First Class pricing is disproportionately higher than Standard → revenue sensitivity risk
• Online customers favour lower-priced tickets → digital pricing strategies can be optimised
• A small number of routes account for the majority of delays and revenue → prioritisation opportunity

SQL KPI Example

SELECT
  route,
  COUNT(*) AS journeys,
  AVG(delay_minutes) AS avg_delay,
  ROUND(
    SUM(CASE WHEN delay_minutes <= 5 THEN 1 ELSE 0 END)::DECIMAL
    / COUNT(*) * 100, 2
  ) AS on_time_pct
FROM railway_journeys
GROUP BY route
ORDER BY avg_delay DESC;

This query underpins route-level performance analysis and prioritisation of operational improvements.