1SELECT 
2    TRIM(REGEXP_replace(method, '\s+', ' ')) AS method,
3    COUNT(*) as number_of_breaches,
4    SUM(
5        CASE 
6            WHEN records_lost LIKE '%M' THEN CAST(REPLACE(REPLACE(records_lost, ',', ''), 'M', '') AS INTEGER) * 1000000
7            WHEN records_lost SIMILAR TO '^[0-9,]+$' THEN CAST(REPLACE(records_lost, ',', '') AS INTEGER)
8            ELSE 0
9        END
10    ) AS total_records_lost,
11    AVG(
12        CASE 
13            WHEN data_sensitivity IN ('1', '2', '3', '4', '5') THEN CAST(data_sensitivity AS INTEGER)
14            ELSE NULL
15        END
16    )*10 AS average_sensitivity
17FROM 
18    @kaggle.joebeachcapital_worlds_biggest_data_breaches_and_hacks.balloon_race_data_breaches_latest_breaches
19GROUP BY 
20    TRIM(REGEXP_replace(method, '\s+', ' '))
21ORDER BY 
22    total_records_lost DESC
23LIMIT 5;