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
p784375	CBRLS Kumara	12	72.08	92	31.35	40.74
p290948	Mohammad Amir	3	68	112	28.86	39.14
p29299	ND Hirwani	39	13.56	27	50.63	-37.07
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
p15385	R Illingworth	102	27.15	20	51.90	-24.75

183ms

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

11ms

Men's ODI ¶

player_id	player	home_wickets	home_average	away_wickets	away_average	difference
p1041679	NP Kenjige	6	72.50	61	26.48	46.02
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	49	33.90	32.10
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
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

212ms

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
p54836	SS Weerakkody	36	27.03	22	49.68	-22.65
p572638	Diana Baig	18	52.11	45	30.22	21.89
p54877	IMHC Joyce	34	39.85	32	20.47	19.38
p54504	NJ Browne	15	50.13	73	30.86	19.27
p1135028	C Murray	16	45	35	25.89	19.11
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	27	31.44	34	44.68	-13.23
p220665	Asmavia Iqbal	17	26.41	53	39.34	-12.93
p543550	HM Rowe	30	34.63	35	22	12.63
p597811	DB Sharma	72	22.26	77	33.51	-11.24
p324246	MJG Nielsen	18	34.06	35	23	11.06
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
p487007	S Luus	31	30.90	85	20.58	10.33
p939249	N de Klerk	18	34.94	46	24.98	9.97

69ms

Men's T20I ¶

player_id	player	home_wickets	home_average	away_wickets	away_average	difference
p731523	DM Nakrani	1	50	90	14.69	35.31
p1107651	C Kyewuta	1	50	49	16.55	33.45
p379145	T Shamsi	22	39.23	67	14.88	24.35
p414970	SM Sharif	10	43.20	75	21.08	22.12
p662973	SM Curran	8	45.38	46	23.89	21.48
p1271974	Ghulam Ahmadi	44	16.39	11	36.82	-20.43
p348059	BJ McCarthy	10	48	45	27.67	20.33
p1175831	D Jakiel	9	35.11	41	17.46	17.65
p402140	Syazrul Idrus	38	9.13	13	26.08	-16.95
p277912	TA Boult	33	31.48	50	14.80	16.68
p637526	IO Okpe	23	8.22	42	24.48	-16.26
p43265	Sohail Tanvir	23	17.74	31	33.74	-16.00
p391485	JO Holder	56	23.54	25	39.52	-15.98
p1112212	B Ward	2	30	57	14.40	15.60
p450860	AF Milne	42	19.19	23	34.65	-15.46
p538506	Taskin Ahmed	59	16.66	33	32.03	-15.37
p659081	MRJ Watt	15	33.27	74	18.66	14.60
p41434	Mohammad Hafeez	10	34.40	51	20.47	13.93
p1155526	P Aho	20	6.25	46	19.54	-13.29
p364343	CA Young	26	31	58	17.88	13.12

135ms

Women's T20I ¶

player_id	player	home_wickets	home_average	away_wickets	away_average	difference
p1080012	N Sibanda	6	37.50	45	12.04	25.46
p317139	P Marange	7	35.71	49	11.29	24.43
p960853	Renuka Singh	11	35.73	47	18.47	17.26
p587146	S Laomi	37	6.49	44	21.77	-15.29
p301738	S Tippoch	20	4.55	31	19.48	-14.93
p1144886	Fatima Sana	10	36.70	49	21.98	14.72
p297079	D Hazell	44	13.80	41	28.22	-14.42
p355349	DJS Dottin	10	30.30	61	17.10	13.20
p343649	Jahanara Alam	15	33.93	45	20.73	13.20
p364413	D van Niekerk	14	30.79	51	18.27	12.51
p474308	HK Matthews	64	9.94	67	21.91	-11.97
p54565	SW Bates	24	15.96	35	27.54	-11.58
p1126416	ER Oza	2	27	67	15.72	11.28
p627048	Fahima Khatun	20	25.60	98	14.83	10.77
p318853	AMCJK Athapaththu	9	34	54	23.39	10.61
p301800	N Boochatham	26	5	98	15.56	-10.56
p1172331	R Samson	18	9.89	33	19.91	-10.02
p54945	EAJ Richardson	19	27.95	41	18.27	9.68
p301637	WA Duraisingam	15	28.13	38	18.82	9.32
p592593	AN Kelly	27	24	36	14.78	9.22

88ms

By Sean McGivern