SELECT atr.id as id, count(*) as ticket_count
FROM cba_ticket t
JOIN cba_ticket_custsvc tcs ON t.id = tcs.ticket_id
JOIN cba_delivered_address da ON tcs.route_address_id = da.route_address_id
JOIN cba_assignment_truck_run atr ON da.assignment_truck_run_id = atr.id
JOIN cba_assignment a ON a.id = atr.assignment_id
WHERE atr.id IN (...)
AND t.created_at BETWEEN DATE_SUB(a.start_date, INTERVAL 28 DAY) AND DATE_ADD(a.start_date, INTERVAL 7 DAY)
AND atr.truck_run_type = 0
AND tcs.is_invalid = false
AND t.ticket_type = 1
GROUP BY atr.id;

+----+-------------+-------+--------+----------------------------------------------+-------------------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | type   | possible_keys                                | key                     | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+--------+----------------------------------------------+-------------------------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | atr   | range  | PRIMARY,assignment_id_idx,truck_run_type     | PRIMARY                 | 8       | NULL                    |  750 |    75.07 | Using where |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY                                      | PRIMARY                 | 8       | cba.atr.assignment_id   |    1 |   100.00 |             |
|  1 | SIMPLE      | da    | ref    | route_address_id_idx,assignment_truck_run_id | assignment_truck_run_id | 8       | cba.atr.id              |  400 |   100.00 |             |
|  1 | SIMPLE      | tcs   | ref    | ticket_id,route_address_id                   | route_address_id        | 5       | cba.da.route_address_id |    1 |   100.00 | Using where |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY,created_at                           | PRIMARY                 | 4       | cba.tcs.ticket_id       |    1 |   100.00 | Using where |
+----+-------------+-------+--------+----------------------------------------------+-------------------------+---------+-------------------------+------+----------+-------------+

+----+-------------+-------+--------+----------------------------------------------+----------------------+---------+--------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                | key                  | key_len | ref                            | rows | filtered | Extra                                        |
+----+-------------+-------+--------+----------------------------------------------+----------------------+---------+--------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | tcs   | ALL    | ticket_id,route_address_id                   | NULL                 | NULL    | NULL                           | 9358 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY,created_at                           | PRIMARY              | 4       | cba.tcs.ticket_id              |    1 |   100.00 | Using where                                  |
|  1 | SIMPLE      | da    | ref    | route_address_id_idx,assignment_truck_run_id | route_address_id_idx | 9       | cba.tcs.route_address_id       |   54 |   100.00 | Using where                                  |
|  1 | SIMPLE      | atr   | eq_ref | PRIMARY,assignment_id_idx,truck_run_type     | PRIMARY              | 8       | cba.da.assignment_truck_run_id |    1 |   100.00 | Using where                                  |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY                                      | PRIMARY              | 8       | cba.atr.assignment_id          |    1 |   100.00 | Using where                                  |
+----+-------------+-------+--------+----------------------------------------------+----------------------+---------+--------------------------------+------+----------+----------------------------------------------+