Betwise news, analysis and automatic betting info

# Daily trainer strike rates in Smartform

By Nick Franks on Saturday, May 5th, 2018

There 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.