Baselight
SELECT 
    TRIM(REGEXP_replace(method, '\s+', ' ')) AS method,
    COUNT(*) as number_of_breaches,
    SUM(
        CASE 
            WHEN records_lost LIKE '%M' THEN CAST(REPLACE(REPLACE(records_lost, ',', ''), 'M', '') AS INTEGER) * 1000000
            WHEN records_lost SIMILAR TO '^[0-9,]+$' THEN CAST(REPLACE(records_lost, ',', '') AS INTEGER)
            ELSE 0
        END
    ) AS total_records_lost,
    AVG(
        CASE 
            WHEN data_sensitivity IN ('1', '2', '3', '4', '5') THEN CAST(data_sensitivity AS INTEGER)
            ELSE NULL
        END
    )*10 AS average_sensitivity
FROM 
    @kaggle.joebeachcapital_worlds_biggest_data_breaches_and_hacks.balloon_race_data_breaches_latest_breaches
GROUP BY 
    TRIM(REGEXP_replace(method, '\s+', ' '))
ORDER BY 
    total_records_lost DESC
LIMIT 5;
methodnumber_of_breachestotal_records_lostaverage_sensitivity
hacked274927376985222.2992700729927
poor security53405321748324.038461538461537
oops!2243685465821.363636363636363
inside job2036031950528.5
lost device4821531971327.708333333333336

Share link

Anyone who has the link will be able to view this.