Fantasy Sports and Player Ratings

Fantasy football (and other fantasy sports) require ranking players across multiple performance dimensions, weighting stats by position, and identifying undervalued picks. These are fundamentally data problems — and SQL handles them cleanly with window functions, ratio calculations, and multi-criteria ranking.

This lesson builds a complete fantasy sports analytics system: point calculations, price-to-value scoring, and fixture difficulty ratings.

How Fantasy Points Systems Work

Most fantasy sports leagues award points based on a weighted combination of performance stats. A typical football scoring system:

The SQL challenge is applying this formula across hundreds of player-match combinations efficiently, then aggregating to season totals.

Fantasy Point Calculation

This query applies the points formula to every player's match stats and aggregates to season totals. The CASE WHEN inside SUM() handles the minutes threshold elegantly:

WITH fantasy_points AS (
  SELECT
    ps.player_id,
    SUM(
      ps.goals * 6
      + ps.assists * 4
      - ps.yellow_cards * 1
      - ps.red_cards * 3
      + CASE WHEN ps.minutes_played >= 60 THEN 2
             WHEN ps.minutes_played >= 1 THEN 1
             ELSE 0 END
    ) AS total_points,
    COUNT(DISTINCT ps.match_id) AS games_played,
    SUM(ps.minutes_played) AS total_minutes,
    SUM(ps.goals) AS goals,
    SUM(ps.assists) AS assists
  FROM player_stats ps
  GROUP BY ps.player_id
),
ranked AS (
  SELECT
    p.name,
    p.position,
    t.name AS team,
    fp.total_points,
    fp.games_played,
    fp.goals,
    fp.assists,
    ROUND(fp.total_points::numeric / NULLIF(fp.games_played, 0), 2) AS pts_per_game,
    RANK() OVER (PARTITION BY p.position ORDER BY fp.total_points DESC) AS position_rank,
    RANK() OVER (ORDER BY fp.total_points DESC) AS overall_rank
  FROM fantasy_points fp
  JOIN players p ON p.player_id = fp.player_id
  JOIN teams t ON t.team_id = p.team_id
)
SELECT * FROM ranked ORDER BY overall_rank;

What This Returns

Two ranking columns give different perspectives:

• position_rank: How this player compares against others at the same position. A striker ranked #1 among forwards might be #5 overall — still excellent for their position.
• overall_rank: Cross-position comparison. Midfielders who score and assist frequently often outrank prolific strikers.

pts_per_game is more useful than total_points for players who missed games through injury — it shows their value per appearance, not just their accumulated total.