Skip to content
1PRIDE

Lions running scoring margin

Level 3 · Challenge 2
Rookie

Prompt

For each Lions 2024 regular-season game, return week, opponent, points scored, points allowed, this-game margin, and the cumulative season margin through that week. Order by week.

Expected output

17 rows (one bye week per season — no row in schedules for it):

weekoppscoredallowedmargincum_margin
Hint

CTE that captures per-game scored/allowed/opp. Outer query adds margin = scored - allowed and cum_margin = SUM(scored - allowed) OVER (ORDER BY week).

Solution 
WITH lions AS (
    SELECT
        week,
        CASE WHEN home_team = 'DET' THEN away_team ELSE home_team END AS opp,
        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,
    opp,
    scored,
    allowed,
    scored - allowed AS margin,
    SUM(scored - allowed) OVER (ORDER BY week) AS cum_margin
FROM lions
ORDER BY week;

The cum_margin column is the season’s point-differential story in a single column. Plot it and you see exactly when the team peaked.