🚗 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 797 649,262 122.8 70.0
Mercedes-Benz 2405 2,221,253 108.3 68.0
Toyota 2037 1,985,262 102.6 72.0
BMW 2254 2,225,222 101.3 66.0
Audi 2093 2,066,396 101.3 68.0
Volkswagen 2540 3,719,958 68.3 68.0
Porsche 154 238,169 64.7 60.0
Tesla 160 265,955 60.2 64.0
Land Rover 591 1,096,310 53.9 64.0
Ford 2827 5,247,116 53.9 66.0
Nissan 1004 1,893,494 53.0 72.0
Vauxhall 1593 3,114,601 51.1 68.0
Skoda 486 1,002,599 48.5 63.0
Peugeot 817 1,696,282 48.2 69.0
Hyundai 553 1,183,485 46.7 70.0
Jaguar 171 371,478 46.0 66.0
Honda 565 1,249,889 45.2 67.0
Renault 547 1,215,728 45.0 64.0
Mg 178 407,361 43.7 66.0
Volvo 336 826,216 40.7 62.0
Citroen 467 1,152,313 40.5 70.0
Kia 441 1,283,484 34.4 68.0
Fiat 246 831,805 29.6 69.0
Mazda 167 593,773 28.1 69.0
Mini 209 908,231 23.0 64.0