1-- Pivot to wide format: one row per week, Kane and Haaland as separate columns
2-- League matches only for a clean apples-to-apples comparison
3WITH
4  weekly AS (
5    SELECT
6      DATE_TRUNC('week', m.date::date) AS week_start,
7      ps.player_name,
8      SUM(COALESCE(ps.goals_scored, 0)) AS goals_this_week
9    FROM
10      "@blt.ultimate_soccer_dataset.match_player_stats" ps
11      JOIN "@blt.ultimate_soccer_dataset.matches" m ON ps.match_id = m.match_id
12    WHERE
13      m.status = 'Match Finished'
14      AND m.season_year = 2025
15      AND ps.player_name IN ('Harry Kane', 'Erling Haaland')
16      AND m.competition_name IN ('Bundesliga', 'Premier League')
17    GROUP BY
18      DATE_TRUNC('week', m.date::date),
19      ps.player_name
20  ),
21  pivoted AS (
22    SELECT
23      week_start,
24      SUM(
25        CASE
26          WHEN player_name = 'Harry Kane' THEN goals_this_week
27          ELSE 0
28        END
29      ) AS kane_weekly,
30      SUM(
31        CASE
32          WHEN player_name = 'Erling Haaland' THEN goals_this_week
33          ELSE 0
34        END
35      ) AS haaland_weekly
36    FROM
37      weekly
38    GROUP BY
39      week_start
40  ),
41  cumulative AS (
42    SELECT
43      week_start,
44      kane_weekly,
45      haaland_weekly,
46      SUM(kane_weekly) OVER (
47        ORDER BY
48          week_start ASC
49      ) AS kane_cumulative,
50      SUM(haaland_weekly) OVER (
51        ORDER BY
52          week_start ASC
53      ) AS haaland_cumulative
54    FROM
55      pivoted
56  )
57SELECT
58  week_start,
59  kane_weekly,
60  haaland_weekly,
61  kane_cumulative,
62  haaland_cumulative
63FROM
64  cumulative
65ORDER BY
66  week_start ASC