Trainers tell us a lot about their runners’ chances of winning through factors such as race selection and jockey bookings. For example, trainers will sometimes run horses on unsuitable going, or at an unsuitable course or over an unsuitable distance with the intention of obtaining a handicap advantage which they plan to cash in on with a future race. I see this happening a lot in May-July, presumably with a view to winning the larger prizes available later in the flat season.

Information about trainers’ intentions can also be inferred from their jockey bookings. Most trainers have a few favoured jockeys who do the majority of their winning, with the remainder necessarily below average. This shows up in the trainer/jockey combo stats which are easily computed using Smartform, as we will see shortly. For example, Luke Morris – legendary as one of the hardest working jockeys with 16,700 races ridden since 2010 – has ridden most races for Sir Mark Prescott (1,849) at a strike rate of 19%. While he has higher strike rates with a few other trainers, these carry far less statistical significance. But you’ll often see him riding outsiders for a wide range of other trainers. The take away is never lay the Prescott/Morris combo !

When I assess a race, I find myself asking “Why is this jockey here today?”. If you assess not the race but each jockey’s bookings on the day, it give an interesting alternative perspective, clearly differentiating between regular or preferred trainer bookings and “filler” bookings. This latter variety of booking is normally associated with lower strike rates and therefore can be used as a factor to consider when analyzing a race, particularly for lay candidates.

Before diving into how to do this analysis, let me give you an example from Redcar on 18th April 2022 as I write this. While analyzing the 14:49 race, I became interested in one particular runner as a lay candidate and asked the question “Why is this jockey here today?”. In the following table you can see each of the jockey’s engagements for that day with the trainer/jockey combo strike rate (for all races at all courses since 2010):

+---------------------+--------+----------------+-------------+-------------------+-----------+----------+-------+
| scheduled_time      | course | runner_name    | jockey_name | trainer_name      | RaceCount | WinCount | SR_TJ |
+---------------------+--------+----------------+-------------+-------------------+-----------+----------+-------+
| 2022-04-18 13:39:00 | Redcar | Mahanakhon     | D Allan     | T D Easterby      |      4225 |      499 |  11.8 |
| 2022-04-18 14:14:00 | Redcar | Mr Moonstorm   | D Allan     | T D Easterby      |      4225 |      499 |  11.8 |
| 2022-04-18 14:49:00 | Redcar | The Golden Cue | D Allan     | Steph Hollinshead |        14 |        1 |   7.1 |
| 2022-04-18 15:24:00 | Redcar | Ugo Gregory    | D Allan     | T D Easterby      |      4225 |      499 |  11.8 |
| 2022-04-18 15:59:00 | Redcar | Hyperfocus     | D Allan     | T D Easterby      |      4225 |      499 |  11.8 |
| 2022-04-18 16:34:00 | Redcar | The Grey Wolf  | D Allan     | T D Easterby      |      4225 |      499 |  11.8 |
| 2022-04-18 17:44:00 | Redcar | Eruption       | D Allan     | T D Easterby      |      4225 |      499 |  11.8 |
+---------------------+--------+----------------+-------------+-------------------+-----------+----------+-------+

The answer is clearly that D Allan was at Redcar to ride for Tim Easterby and had taken the booking for Steph Hollinshead as a “filler”. “The Golden Cue” subsequently finished 17th out of 20 runners.

Performing this analysis on a daily basis is a two stage process. First, we need to calculate trainer/jockey combo strike rates. Then we use that data in combination with the daily races data to list each of a specific jockey’s bookings for the day. Let’s start with the last part to warm up our SQL mojo:

SELECT
    scheduled_time,
    course,
    name as runner_name,
    jockey_name,
    trainer_name
FROM
    daily_races_beta
JOIN
    daily_runners_beta
USING (race_id)
WHERE
    meeting_date = "2022-04-18" AND
    jockey_name = "D Allan"
ORDER BY scheduled_time;

This gets us the first 5 columns of the table above, without the strike rate data:

+---------------------+--------+----------------+-------------+-------------------+
| scheduled_time      | course | runner_name    | jockey_name | trainer_name      |
+---------------------+--------+----------------+-------------+-------------------+
| 2022-04-18 13:39:00 | Redcar | Mahanakhon     | D Allan     | T D Easterby      |
| 2022-04-18 14:14:00 | Redcar | Mr Moonstorm   | D Allan     | T D Easterby      |
| 2022-04-18 14:49:00 | Redcar | The Golden Cue | D Allan     | Steph Hollinshead |
| 2022-04-18 15:24:00 | Redcar | Ugo Gregory    | D Allan     | T D Easterby      |
| 2022-04-18 15:59:00 | Redcar | Hyperfocus     | D Allan     | T D Easterby      |
| 2022-04-18 16:34:00 | Redcar | The Grey Wolf  | D Allan     | T D Easterby      |
| 2022-04-18 17:44:00 | Redcar | Eruption       | D Allan     | T D Easterby      |
+---------------------+--------+----------------+-------------+-------------------+

There are two approaches to generating the trainer/jockey strike rate data – using a sub-query or creating a new table of all trainer/jockey combo strike rates. I normally take the latter approach because I use this type of data many times over during the day. I therefore have this setup as a stored procedure which is run daily after the database update. The following SQL is executed once to (re)define the stored procedure called refresh_trainer_jockeys:

delimiter //
DROP PROCEDURE IF EXISTS refresh_trainer_jockeys//
CREATE
    DEFINER='smartform'@'localhost'
PROCEDURE refresh_trainer_jockeys()
    COMMENT 'Regenerates the x_trainer_jockeys table'
BEGIN
    DROP TABLE IF EXISTS x_trainer_jockeys;
    CREATE TABLE x_trainer_jockeys (
        trainer_name   varchar(80),
        trainer_id     int(11),
        jockey_name    varchar(80),
        jockey_id      int(11),
        RaceCount      int(4),
        WinCount       int(4),
        WinPct         decimal(4,1));
    INSERT INTO x_trainer_jockeys
        SELECT
            sub.trainer_name,
            sub.trainer_id,
            sub.jockey_name,
            sub.jockey_id,
            sub.RaceCount,
            sub.WinCount,
            percentage(sub.RaceCount, sub.WinCount, 20, 1) AS WinPct
        FROM (
            SELECT
                ANY_VALUE(trainer_name) AS trainer_name,
                trainer_id,
                ANY_VALUE(jockey_name) AS jockey_name,
                jockey_id,
                COUNT(*) AS RaceCount,
                COUNT(IF(finish_position = 1, 1, NULL)) AS WinCount
            FROM
                historic_races_beta
            JOIN
                historic_runners_beta
            USING (race_id)
            WHERE
                YEAR(meeting_date) >= 2010 AND
                trainer_id IS NOT NULL AND
                jockey_id IS NOT NULL AND
                NOT (unfinished <=> "Non-Runner")
            GROUP BY jockey_id, trainer_id) AS sub
        ORDER BY sub.trainer_name, sub.jockey_name;
END//
delimiter ;

When the stored procedure refresh_trainer_jockeys is run, a new table x_trainer_jockeys is created. This new table is very useful for queries such as:

SELECT * FROM x_trainer_jockeys WHERE trainer_name = "W J Haggas" ORDER BY WinPct DESC LIMIT 20;

which results in the strike rates for William Haggas’ top 20 jockeys (for all races at all courses since 2010):

+--------------+------------+---------------+-----------+-----------+----------+--------+
| trainer_name | trainer_id | jockey_name   | jockey_id | RaceCount | WinCount | WinPct |
+--------------+------------+---------------+-----------+-----------+----------+--------+
| W J Haggas   |       1804 | S Sanders     |      4355 |        70 |       25 |   35.7 |
| W J Haggas   |       1804 | B A Curtis    |     45085 |       109 |       35 |   32.1 |
| W J Haggas   |       1804 | L Dettori     |      2589 |        90 |       28 |   31.1 |
| W J Haggas   |       1804 | D Tudhope     |     27114 |       225 |       69 |   30.7 |
| W J Haggas   |       1804 | T P Queally   |     18614 |        49 |       15 |   30.6 |
| W J Haggas   |       1804 | G Gibbons     |     18303 |        80 |       24 |   30.0 |
| W J Haggas   |       1804 | A Kirby       |     31725 |        28 |        8 |   28.6 |
| W J Haggas   |       1804 | R Kingscote   |     31611 |        79 |       22 |   27.8 |
| W J Haggas   |       1804 | Jim Crowley   |     43145 |       270 |       74 |   27.4 |
| W J Haggas   |       1804 | S De Sousa    |     41905 |        84 |       23 |   27.4 |
| W J Haggas   |       1804 | R L Moore     |     18854 |       360 |       91 |   25.3 |
| W J Haggas   |       1804 | P Hanagan     |     16510 |       355 |       88 |   24.8 |
| W J Haggas   |       1804 | A J Farragher |   1165464 |        53 |       13 |   24.5 |
| W J Haggas   |       1804 | J Fanning     |      2616 |       128 |       31 |   24.2 |
| W J Haggas   |       1804 | J Doyle       |     31684 |       455 |      105 |   23.1 |
| W J Haggas   |       1804 | Oisin Murphy  |   1151765 |        66 |       15 |   22.7 |
| W J Haggas   |       1804 | M Harley      |     74041 |        76 |       17 |   22.4 |
| W J Haggas   |       1804 | Tom Marquand  |   1157192 |       705 |      157 |   22.3 |
| W J Haggas   |       1804 | R Hughes      |      1764 |        81 |       18 |   22.2 |
| W J Haggas   |       1804 | P Cosgrave    |     17562 |       564 |      121 |   21.5 |
+--------------+------------+---------------+-----------+-----------+----------+--------+

We’re now ready to use x_trainer_jockeys to complete the answer to our question “Why is this jockey here today?”:

SELECT
    scheduled_time,
    course,
    name as runner_name,
    daily_runners_beta.jockey_name,
    daily_runners_beta.trainer_name,
    coalesce(RaceCount, 0) as RaceCount,
    coalesce(WinCount, 0) as WinCount,
    coalesce(WinPct, 0.0) as SR_TJ
FROM
    daily_races_beta
JOIN
    daily_runners_beta
USING (race_id)
LEFT JOIN
    x_trainer_jockeys
USING (trainer_id,jockey_id)
WHERE
    meeting_date = "2022-04-18" AND
    daily_runners_beta.jockey_name = "D Allan"
ORDER BY scheduled_time;

Which comes full circle, giving us the table at the top of the blog !