🚗 Which car makes offend most? (normalised)

Offences per 100,000 registered vehicles of each make. Raw offence counts are misleading because Ford has 5.2 million vehicles on UK roads while Porsche has 238,000. This query normalises against DVLA registration data to show which makes are disproportionately reported.

Custom SQL query returning 25 rows (hide) 

select r.vehicle_make as Make, count(*) as Offences, printf('%,d', vr.licensed_vehicles) as [UK registered vehicles], round(100000.0 * count(*) / vr.licensed_vehicles, 1) as [Offences per 100k], round(100.0 * sum(case when r.disposal not in ('NFA', 'Duplicate Submission') then 1 else 0 end) / count(*), 0) as [Action rate %] from reports r join vehicle_registrations vr on r.vehicle_make = vr.make where r.vehicle_make is not null group by r.vehicle_make order by [Offences per 100k] desc

Edit SQL

This data as json, CSV

MakeOffencesUK registered vehiclesOffences per 100kAction rate %
Seat 957 649,262 147.4 71.0
Mercedes-Benz 2855 2,221,253 128.5 69.0
Toyota 2395 1,985,262 120.6 73.0
BMW 2661 2,225,222 119.6 66.0
Audi 2438 2,066,396 118.0 68.0
Volkswagen 2920 3,719,958 78.5 68.0
Porsche 185 238,169 77.7 61.0
Tesla 186 265,955 69.9 65.0
Ford 3333 5,247,116 63.5 66.0
Land Rover 695 1,096,310 63.4 65.0
Nissan 1190 1,893,494 62.8 73.0
Vauxhall 1874 3,114,601 60.2 68.0
Skoda 562 1,002,599 56.1 65.0
Jaguar 207 371,478 55.7 66.0
Peugeot 936 1,696,282 55.2 69.0
Hyundai 652 1,183,485 55.1 71.0
Mg 214 407,361 52.5 67.0
Honda 654 1,249,889 52.3 69.0
Renault 634 1,215,728 52.1 65.0
Volvo 399 826,216 48.3 62.0
Citroen 537 1,152,313 46.6 70.0
Kia 508 1,283,484 39.6 69.0
Mazda 212 593,773 35.7 71.0
Fiat 286 831,805 34.4 69.0
Mini 242 908,231 26.6 64.0