Cricket query

Biggest difference in home and away bowling average

Players with the biggest difference between their home bowling average and their bowling batting average. Unsurprisingly, most players average less at home. Minimum 50 wickets and 10 away innings bowled.

SQL

WITH ground_counts AS (
  SELECT ground, team, COUNT(*) AS count FROM team_innings GROUP BY ground, team
),
ground_ranks AS (
  SELECT ground, team, row_number() OVER (PARTITION BY ground ORDER BY count DESC) AS rank FROM ground_counts
),
home_grounds AS (
  SELECT * FROM ground_ranks WHERE rank = 1
),
innings_with_home AS (
  SELECT *, home_grounds.team AS home_team
  FROM bowling_innings
  INNER JOIN home_grounds ON home_grounds.ground = bowling_innings.ground
  WHERE runs IS NOT NULL
),
pivot AS (
  SELECT
    player_id,
    player,
    SUM(CASE WHEN home_team = team THEN runs END) AS home_runs,
    SUM(CASE WHEN home_team = team THEN wickets END) AS home_wickets,
    SUM(CASE WHEN home_team != team THEN runs END) AS away_runs,
    SUM(CASE WHEN home_team != team THEN wickets END) AS away_wickets
  FROM innings_with_home
  GROUP BY player_id, player
  HAVING home_wickets > 0 AND away_wickets > 0 AND (SUM(CASE WHEN home_team != team THEN 1 ELSE 0 END) >= 10) AND (home_wickets + away_wickets) >= 50
)
SELECT
  player_id,
  player,
  home_wickets,
  CAST(home_runs AS real) / home_wickets AS home_average,
  away_wickets,
  CAST(away_runs AS real) / away_wickets AS away_average,
  (CAST(home_runs AS real) / home_wickets) - (CAST(away_runs AS real) / away_wickets) AS difference
FROM pivot
ORDER BY ABS(difference) DESC
LIMIT 20;

Format: Test ODI T20I

Gender: Men Women

(Help)

Men's Test ¶

player_id	player	home_wickets	home_average	away_wickets	away_average	difference
p559066	IS Sodhi	5	93.80	51	35.55	58.25
p38062	JDP Oram	41	18.76	19	63.89	-45.14
p290948	Mohammad Amir	3	68	112	28.86	39.14
p784375	CBRLS Kumara	15	69.13	92	31.39	37.74
p29299	ND Hirwani	39	13.56	27	50.63	-37.07
p596417	BA Carse	9	60.89	49	24.55	36.34
p4208	RJ Bright	8	68.62	45	36.24	32.38
p53238	FMM Worrell	21	60.76	48	29.08	31.68
p8142	TW Wall	37	25.54	19	56.05	-30.51
p55756	BC Strang	40	31.15	16	59.81	-28.66
p14220	GH Hirst	42	21.81	17	50.24	-28.43
p42661	Sikander Bakht	10	58.40	57	30.23	28.17
p38753	PJ Wiseman	18	66.89	43	39.51	27.38
p37730	DK Morrison	115	27.12	45	54	-26.88
p32685	IK Pathan	22	52.18	73	25.58	26.61
p38620	DR Tuffey	57	25.09	20	50.75	-25.66
p30923	Maninder Singh	58	28.26	26	53.46	-25.20
p49920	MDK Perera	108	29.91	42	55.05	-25.14
p47195	JH Sinclair	51	26.90	12	52	-25.10
p247235	CR Woakes	148	23.87	44	48.93	-25.06

194ms

Women's Test ¶

player_id	player	home_wickets	home_average	away_wickets	away_average	difference
p54082	S Kulkarni	32	21.06	24	38.54	-17.48
p54044	DF Edulji	34	20.38	27	32.63	-12.25
p53573	R Thompson	42	15.21	15	26.73	-11.52
p54410	J Lord	16	24.56	39	16.82	7.74
p53422	CL Fitzpatrick	21	24.10	39	16.44	7.66
p53731	E Bakewell	20	15.75	30	17.20	-1.45
p53885	ME Maclagan	27	15.11	33	15.97	-0.86
p53762	MB Duggan	48	13.40	29	13.66	-0.26
p53906	K Sciver-Brunt	28	21.57	23	21.48	0.09

13ms

Men's ODI ¶

player_id	player	home_wickets	home_average	away_wickets	away_average	difference
p51655	VC Drakes	8	60.88	43	18.74	42.13
p574178	A Dananjaya	48	23.46	11	62.09	-38.63
p37749	NL McCullum	12	76.67	51	39.92	36.75
p974175	Fazalhaq Farooqi	1	66	53	32.91	33.09
p30732	P Kumar	13	62.54	64	30.64	31.90
p14054	SJ Harmison	52	24.15	24	51.04	-26.89
p245490	Ahmed Raza	9	53	55	26.31	26.69
p230559	KA Pollard	35	29.66	20	56.15	-26.49
p28779	SC Ganguly	53	27.13	47	51.30	-24.17
p55848	GJ Whittall	43	27.28	45	51.29	-24.01
p670045	G Motie	38	26.45	14	50.21	-23.77
p48472	TM Dilshan	59	34.59	47	58.23	-23.64
p50244	A Ranatunga	30	33.90	49	55.92	-22.02
p49356	RS Kalpage	39	30.56	34	52.44	-21.88
p273439	SMSM Senanayake	22	22.91	31	44.19	-21.28
p46750	RJ Peterson	23	50.35	52	29.27	21.08
p47539	R Telemachus	45	23.84	11	44.73	-20.88
p8189	ME Waugh	47	25.38	38	45.92	-20.54
p348109	PA van Meekeren	32	18.22	29	37.69	-19.47
p48460	KSC de Silva	7	42	45	22.87	19.13

223ms

Women's ODI ¶

player_id	player	home_wickets	home_average	away_wickets	away_average	difference
p55017	Shaiza Khan	46	15.87	17	45.82	-29.95
p1135028	C Murray	16	53.69	45	24.40	29.29
p54836	SS Weerakkody	36	27.03	22	49.68	-22.65
p54877	IMHC Joyce	33	40.45	33	20.45	20
p572638	Diana Baig	19	52.26	47	32.74	19.52
p54504	NJ Browne	15	50.13	73	30.86	19.27
p54374	KV Gunn	6	35.33	47	19.17	16.16
p54778	SP de Alwis	27	13.70	31	28.58	-14.88
p220670	Qanita Jalil	6	41.83	44	27.52	14.31
p263980	G Sultana	34	12.91	32	26.28	-13.37
p453370	CL Tryon	28	33.71	37	46.73	-13.02
p220665	Asmavia Iqbal	17	26.41	53	39.34	-12.93
p543550	HM Rowe	30	34.63	35	22	12.63
p324246	MJG Nielsen	18	34.06	35	23	11.06
p597811	DB Sharma	81	22.17	85	32.96	-10.79
p54331	HM Watson	16	16.31	38	27.05	-10.74
p54506	LE Milliken	11	32.55	48	21.90	10.65
p53726	IT Guha	57	18.60	44	29.20	-10.61
p53906	K Sciver-Brunt	105	20.45	65	29.75	-9.31
p301603	Salma Khatun	14	15.93	38	25.18	-9.26

68ms

Men's T20I ¶

player_id	player	home_wickets	home_average	away_wickets	away_average	difference
p547766	JA Duffy	40	12.28	22	34.77	-22.50
p414970	SM Sharif	10	43.20	77	21.97	21.23
p481979	A Nortje	6	40	50	18.94	21.06
p379145	T Shamsi	25	35.28	64	15.28	20.00
p1271974	Ghulam Ahmadi	46	16.02	13	34.92	-18.90
p402140	Syazrul Idrus	38	9.13	13	26.08	-16.95
p277912	TA Boult	33	31.48	50	14.80	16.68
p348059	BJ McCarthy	15	42.80	56	26.12	16.67
p43265	Sohail Tanvir	23	17.74	31	33.74	-16.00
p1112212	B Ward	2	30	64	14.28	15.72
p1197975	K Piet	21	7.48	35	23.17	-15.70
p637526	IO Okpe	40	11.70	44	27.32	-15.62
p450860	AF Milne	42	19.19	23	34.65	-15.46
p906783	OC McCoy	42	20.21	10	35.30	-15.09
p41434	Mohammad Hafeez	10	34.40	51	20.47	13.93
p348049	Nasum Ahmed	33	17.91	19	31.74	-13.83
p719715	Washington Sundar	20	32.95	31	19.13	13.82
p1155526	P Aho	26	8.38	45	22.18	-13.79
p538506	Taskin Ahmed	63	17.73	43	31.33	-13.60
p659081	MRJ Watt	15	33.27	79	19.85	13.42

181ms

Women's T20I ¶

player_id	player	home_wickets	home_average	away_wickets	away_average	difference
p1175151	Mariamma Hyder	1	71	49	18.41	52.59
p1080012	N Sibanda	6	65	50	15.80	49.20
p1022083	SNL Siegers	26	17.85	34	48.35	-30.51
p317139	P Marange	7	35.71	51	12.25	23.46
p478808	Ritu Moni	6	38.50	45	18.69	19.81
p500726	RC Belbashi	22	30.82	45	13.24	17.57
p301738	S Tippoch	20	4.55	31	19.48	-14.93
p297079	D Hazell	44	13.80	41	28.22	-14.42
p1093518	R Andrew	21	8.62	32	22.47	-13.85
p343649	Jahanara Alam	15	33.93	45	20.73	13.20
p1126416	ER Oza	2	27	99	13.96	13.04
p364413	D van Niekerk	14	30.79	51	18.27	12.51
p301800	N Boochatham	26	5	98	17.09	-12.09
p1150581	I Toua	9	5.11	42	16.76	-11.65
p474308	HK Matthews	94	9.54	78	20.90	-11.35
p1119577	SN Shihepo	18	22.50	37	11.24	11.26
p1171432	S Chaturongrattana	30	8.33	50	19.16	-10.83
p627048	Fahima Khatun	20	26.40	125	15.65	10.75
p587146	S Laomi	60	7.10	79	17.66	-10.56
p318853	AMCJK Athapaththu	16	31.62	76	21.14	10.48

138ms

By Sean McGivern