Lions points by week, 2024

Level 2 · Challenge 6

Starter

Prompt

Return Lions 2024 regular-season points scored and points allowed, by week, plus the running cumulative point differential. (Cumulative diff = sum of (scored - allowed) through that week.)

Expected output

17 rows (Lions had a bye week — no game row in schedules):

week	opponent	scored	allowed	diff	cum_diff

Hint

You can compute the per-week values from schedules alone. For the running cumulative, this is technically a Level 3 window function (SUM(...) OVER (ORDER BY week)) — but you can also do it in pandas after pulling the per-week frame.

Solution

SQL with a window function (sneak peek of Level 3):

WITH lions_games AS (
    SELECT
        week,
        CASE WHEN home_team = 'DET' THEN away_team ELSE home_team END AS opponent,
        CASE WHEN home_team = 'DET' THEN home_score ELSE away_score END AS scored,
        CASE WHEN home_team = 'DET' THEN away_score ELSE home_score END AS allowed
    FROM schedules
    WHERE season = 2024
      AND game_type = 'REG'
      AND (home_team = 'DET' OR away_team = 'DET')
)
SELECT
    week,
    opponent,
    scored,
    allowed,
    scored - allowed AS diff,
    SUM(scored - allowed) OVER (ORDER BY week) AS cum_diff
FROM lions_games
ORDER BY week;

Or in pandas after pulling the per-week frame:

df['diff'] = df['scored'] - df['allowed']
df['cum_diff'] = df['diff'].cumsum()