Daily trainer strike rates in Smartform
By Nick Franks on Saturday, May 5th, 2018There are many ways to skin a cat in Smartform. One of the simplest is to stick with SQL and stay in the database, without resorting to programming or exporting the data.
So here is a breakdown of how to generate one of the most commonly used “derived” statistics in horseracing – trainer strike rates. We hear that trainers whose horses are currently in good form are worth backing – but how can we tell?
The following single SQL produces Trainer strike rates for all daily Flat races in the database, calculated over a period of 14 days. Copy and paste the code at your MySQL command prompt:
Select a.*,
ROUND(Winners/Runners * 100,0) AS WinPct,
ROUND(Placers/Runners * 100,0) AS PlacePct
from(
SELECT hru.trainer_name as Trainer, hru.trainer_id,
COUNT(*) AS Runners,
SUM(CASE WHEN hru.finish_position = 1 THEN 1 ELSE 0 END) AS Winners,
sum(case when hra.num_runners < 8 then case when hru.finish_position in ( 2) then 1 else 0 end
else
case when hra.num_runners < 16 then case when hru.finish_position in ( 2,3) then 1 else 0 end
else
case when hra.handicap = 1 then case when hru.finish_position in (2,3,4) then 1 else 0 end
else
case when hru.finish_position in ( 1,2,3) then 1 else 0 end
end
end
end )as Placers,
ROUND(((SUM(CASE WHEN hru.finish_position = 1 THEN (hru.starting_price_decimal -1) ELSE -1 END))),2) AS WinProfit
FROM historic_runners hru
JOIN historic_races hra USING (race_id)
WHERE hra.meeting_date >= ADDDATE(CURDATE(), INTERVAL -14 DAY)
and hra.race_type_id in( 15, 12)
and hru.in_race_comment <> 'Withdrawn'
and hru.starting_price_decimal IS NOT NULL
GROUP BY trainer_name, trainer_id
) a
Where runners >= 5 or winners >= 3
ORDER BY WinProfit desc
LIMIT 20;
The approach is to generate a query within a query – to allow the use of the counts done in the main query to be used to create the strike rate percentages.
The main part joins the historic_runners and historic_races tables by race_id.
The WHERE clause defines the criteria
Firstly, define the period over which you want the analysis, here we set it to analyze races in the last 14 days
hra.meeting_date >= ADDDATE(CURDATE(), INTERVAL -14 DAY)
Secondly we define the race types for all Flat races (12 for Turf and 15 for All Weather)
All of these can be found running the SQL statement:
select distinct race_type, race_type_id from historic_races; )
The final criteria helps to eliminate horses who have not actually participated that are in the database, non-runners, withdrawn etc. There are a number of ways to do this and one can find their own criteria, but I use this:
and hru.in_race_comment <> 'Withdrawn'
and hru.starting_price_decimal IS NOT NULL
The outer section of the SQL selects all the data from the inner part and displays it and calculates the strike rates. The )a afterwards is required by SQL.
Finally in Green are the sorting instructions and some other criteria.
Also note that here I am choosing to only display trainers who have had at least 5 runners or at least 3 winners.
Where runners >= 5
or winners >= 3
I am ordering them in order of greatest win profit
ORDER BY WinProfit desc
And limiting the number of trainers displayed to 20
LIMIT 20
So on the day of the 2000 Guineas, Brian Meehan is currently the most profitable trainer to follow, with £47 retuns, but P W Hiatt has the highest strike rate, with 60% – albeit with only 5 runners in the last few days.
If you’re looking for a trainer with a high strike rate from at leasy double digit runners in the last 14 days, then Mark Usher, with a 33% win strike and 25% place strike, is impressive.
Adapt the SQL above to generate daily trainer strike rates so you can judge for yourself.