SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE carstable (
  Timestamp TEXT, Email TEXT, Name TEXT, Year INTEGER, Make TEXT, Model TEXT, Car_ID INTEGER, Judge_ID TEXT, Judge_Name TEXT,
  Racer_Turbo INTEGER, Racer_Supercharged INTEGER, Racer_Performance INTEGER, Racer_Horsepower INTEGER,
  Car_Overall INTEGER,
  Engine_Modifications INTEGER, Engine_Performance INTEGER, Engine_Chrome INTEGER, Engine_Detailing INTEGER, Engine_Cleanliness INTEGER,
  Body_Frame_Undercarriage INTEGER, Body_Frame_Suspension INTEGER, Body_Frame_Chrome INTEGER, Body_Frame_Detailing INTEGER, Body_Frame_Cleanliness INTEGER,
  Mods_Paint INTEGER, Mods_Body INTEGER, Mods_Wrap INTEGER, Mods_Rims INTEGER, Mods_Interior INTEGER, Mods_Other INTEGER, Mods_ICE INTEGER, Mods_Aftermarket INTEGER, Mods_WIP INTEGER, Mods_Overall TEGER
);
INSERT INTO carstable (Timestamp, Email, Name, Year, Make, Model, Car_ID, Judge_ID, Judge_Name, Racer_Turbo, Racer_Supercharged, Racer_Performance, Racer_Horsepower, Car_Overall, Engine_Modifications, Engine_Performance, Engine_Chrome, Engine_Detailing, Engine_Cleanliness, Body_Frame_Undercarriage, Body_Frame_Suspension, Body_Frame_Chrome, Body_Frame_Detailing, Body_Frame_Cleanliness, Mods_Paint, Mods_Body, Mods_Wrap, Mods_Rims, Mods_Interior, Mods_Other, Mods_ICE, Mods_Aftermarket, Mods_WIP, Mods_Overall) VALUES
  ('8/5/2018 14:10', 'honoland13@japanpost.jp', 'Hernando', '2015', 'Acura', 'TLX', '48', 'J04', 'Bob', '0', '0', '2', '2', '4', '4', '0', '2', '4', '4', '2', '4', '2', '2', '2', '2', '2', '0', '4', '4', '4', '6', '2', '0', '4'),
  ('8/5/2018 15:11', 'nlighterness2q@umn.edu', 'Noel', '2015', 'Jeep', 'Wrangler', '124', 'J02', 'Carl', '0', '6', '4', '2', '4', '6', '6', '4', '4', '4', '6', '6', '6', '6', '6', '4', '6', '6', '6', '6', '6', '4', '6', '4', '6'),
  ('8/5/2018 17:10', 'eguest47@microsoft.com', 'Edan', '2015', 'Lexus', 'Is250', '222', 'J05', 'Adrian', '0', '0', '0', '0', '0', '0', '0', '0', '6', '6', '6', '0', '0', '6', '6', '6', '0', '0', '0', '0', '0', '0', '0', '0', '4'),
  ('8/5/2018 17:34', 'hchilley40@fema.gov', 'Hieronymus', '1993', 'Honda', 'Civic eG', '207', 'J06', 'Aaron', '0', '0', '2', '2', '2', '2', '2', '2', '0', '4', '2', '2', '2', '2', '2', '2', '4', '2', '2', '0', '0', '0', '2', '2', '0'),
  ('8/5/2018 14:30', 'nnowick3d@tuttocitta.it', 'Nickolas', '2016', 'Ford', 'Mystang', '167', 'J02', 'Carl', '0', '0', '2', '2', '0', '2', '2', '0', '0', '0', '0', '2', '0', '2', '2', '2', '0', '0', '2', '0', '0', '0', '0', '0', '2'),
  ('8/5/2018 16:12', 'mdearl39@amazon.co.uk', 'Martin', '2013', 'Hyundai', 'Gen coupe', '159', 'J04', 'Bob', '0', '0', '2', '0', '0', '0', '2', '0', '0', '0', '0', '2', '0', '2', '2', '0', '2', '0', '2', '0', '0', '0', '0', '0', '0'),
  ('8/5/2018 17:00', 'alynamg@blogtalkradio.com', 'Aldridge', '2009', 'Infiniti', 'G37', '20', 'J06', 'Aaron', '2', '0', '2', '2', '0', '0', '2', '0', '0', '2', '2', '2', '2', '2', '2', '2', '2', '2', '4', '2', '2', '0', '2', '0', '2'),
  ('8/5/2018 16:11', 'abowton3k@spiegel.de', 'Ambros', '2009', 'Honda', 'Oddesy', '178', 'J06', 'Aaron', '2', '0', '2', '2', '2', '2', '2', '0', '4', '4', '2', '2', '2', '4', '4', '4', '2', '2', '', '6', '4', '4', '6', '4', '6'),
  ('8/5/2018 17:29', 'qesterbrookn@bandcamp.com', 'Quincy', '2012', 'Hyundai', 'Celoster', '30', 'J04', 'Bob', '0', '0', '2', '2', '2', '2', '2', '4', '6', '6', '4', '2', '4', '4', '6', '6', '4', '0', '2', '0', '0', '0', '2', '2', '4');
WITH cte AS (
  SELECT *, 
       Racer_Turbo + Racer_Supercharged + Racer_Performance + Racer_Horsepower + 
       Car_Overall + 
       Engine_Modifications + Engine_Performance + Engine_Chrome + Engine_Detailing + Engine_Cleanliness + 
       Body_Frame_Undercarriage + Body_Frame_Suspension + Body_Frame_Chrome + Body_Frame_Detailing + Body_Frame_Cleanliness + 
       Mods_Paint + Mods_Body + Mods_Wrap + Mods_Rims + Mods_Interior + Mods_Other + Mods_ICE + Mods_Aftermarket + Mods_WIP + Mods_Overall Total
  FROM carstable
)
SELECT ROW_NUMBER() OVER (ORDER BY Make, Total DESC) Ranking,
       Car_ID, Year, Make, Model, Total
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Make ORDER BY Total) rn FROM cte) 
WHERE rn <= 3
ORDER BY Make, Total DESC;


Ranking
Car_ID
Year
Make
Model
Total


1
48
2015
Acura
TLX
62

2
167
2016
Ford
Mystang
20

3
178
2009
Honda
Oddesy
72

4
207
1993
Honda
Civic eG
40

5
30
2012
Hyundai
Celoster
66

6
159
2013
Hyundai
Gen coupe
14

7
20
2009
Infiniti
G37
38

8
124
2015
Jeep
Wrangler
124

9
222
2015
Lexus
Is250
40
Ranking	Car_ID	Year	Make	Model	Total
1	48	2015	Acura	TLX	62
2	167	2016	Ford	Mystang	20
3	178	2009	Honda	Oddesy	72
4	207	1993	Honda	Civic eG	40
5	30	2012	Hyundai	Celoster	66
6	159	2013	Hyundai	Gen coupe	14
7	20	2009	Infiniti	G37	38
8	124	2015	Jeep	Wrangler	124
9	222	2015	Lexus	Is250	40