Computing batting & bowling stats from ball-by-ball data

The core idea: everything is a GROUP BY on deliveries

A cricket stat is just an aggregation over balls. If you have one row per delivery — with the batter, bowler, runs and any wicket — then every classic metric falls out of a GROUP BY. CricketLogic ships two views that do exactly this so you never re-derive them.

How is batting strike rate computed?

Strike rate is runs scored per 100 balls faced. The batting_performance view groups deliveries by batter (and team, match_type, competition, gender) and computes:

SELECT
  batsman,
  team,
  COUNT(*)                              AS balls_faced,
  SUM(runs_batsman)                     AS runs_scored,
  (SUM(runs_batsman) * 100.0) / COUNT(*) AS strike_rate
FROM deliveries d
JOIN innings i ON d.inning_id = i.inning_id
JOIN matches m ON i.match_id = m.match_id
GROUP BY batsman, team;

Boundaries and dismissals come from the same scan:

• fours / sixes — counted from runs_total.
• dismissals — a wicket that is not a run out (run outs are not credited to the batter’s dismissal count for average purposes).
• batting average — runs divided by dismissals (falling back to runs when a batter was never dismissed).

You query the finished view directly:

SELECT batsman, team, runs_scored, strike_rate, sixes
FROM batting_performance
WHERE match_type = 'T20' AND balls_faced >= 300
ORDER BY strike_rate DESC
LIMIT 10;

How is bowling economy computed?

Economy rate is runs conceded per over (six balls). The bowling_performance view mirrors the batting one, keyed on the bowler:

SELECT
  bowler,
  COUNT(*)                          AS balls_bowled,
  SUM(runs_total)                   AS runs_conceded,
  (SUM(runs_total) * 6.0) / COUNT(*) AS economy_rate
FROM deliveries
GROUP BY bowler;

Wickets are counted only for dismissal kinds credited to the bowler — bowled, caught, lbw, stumped and hit wicket — which then drive:

• bowling average — runs conceded per wicket.
• bowling strike rate — balls bowled per wicket.

SELECT bowler, team, wickets_taken, economy_rate, bowling_strike_rate
FROM bowling_performance
WHERE match_type = 'T20' AND balls_bowled >= 300
ORDER BY economy_rate ASC
LIMIT 10;

Why always filter by match_type, competition and gender

Aggregating T20, ODI and Test balls together produces meaningless numbers — a Test strike rate and a T20 strike rate describe different games. The predefined views group by match_type, competition and gender, so a single batter appears as separate rows per format. Always filter on those dimensions in your WHERE clause.

Fantasy points reuse the same rows

The fantasy_points view is another aggregation over deliveries — runs, boundary bonuses and wicket credits — so your fantasy model stays consistent with the batting and bowling numbers. See use cases for a full example.

Takeaway

You don’t hand-write these aggregations; CricketLogic already encodes the correct definitions. But because they are plain SQL views, you can read exactly how each number is produced and fork the SQL when your definition differs.