1WITH breaches_per_year AS (
2    SELECT 
3        year,
4        COUNT(*) as number_of_breaches
5    FROM 
6        @kaggle.joebeachcapital_worlds_biggest_data_breaches_and_hacks.balloon_race_data_breaches_latest_breaches
7    GROUP BY 
8        year
9),
10total_records_per_year AS (
11    SELECT 
12        year,
13        SUM(
14            CASE 
15                WHEN records_lost LIKE '%M' THEN CAST(REPLACE(REPLACE(records_lost, ',', ''), 'M', '') AS INTEGER) * 1000000
16                WHEN records_lost SIMILAR TO '^[0-9,]+$' THEN CAST(REPLACE(records_lost, ',', '') AS INTEGER)
17                ELSE 0
18            END
19        ) AS total_records_lost
20    FROM 
21        @kaggle.joebeachcapital_worlds_biggest_data_breaches_and_hacks.balloon_race_data_breaches_latest_breaches
22    GROUP BY 
23        year
24)
25
26SELECT 
27    b.year,
28    b.number_of_breaches,
29    r.total_records_lost
30FROM 
31    breaches_per_year b
32INNER JOIN total_records_per_year r ON b.year = r.year
33ORDER BY 
34    b.year ASC
35LIMIT 19;