Bucket Lions 2024 games by margin

Level 3 · Challenge 6

Starter

Prompt

Bucket every Lions 2024 regular-season game into one of four categories based on point margin:

• blowout W (15+ point win)
• close W (1-14 point win)
• close L (1-14 point loss)
• blowout L (15+ point loss)

Return one row per bucket with the count and the average margin in that bucket (margin is scored - allowed).

Expected output

bucket	n_games	avg_margin

Hint

CTE that adds det_pts, opp_pts, margin. Outer query uses a CASE to label the bucket, then GROUP BY bucket.

Solution

WITH lions AS (
    SELECT
        week,
        CASE WHEN home_team = 'DET' THEN home_score ELSE away_score END AS det_pts,
        CASE WHEN home_team = 'DET' THEN away_score ELSE home_score END AS opp_pts
    FROM schedules
    WHERE season = 2024 AND game_type = 'REG'
      AND (home_team = 'DET' OR away_team = 'DET')
),
labeled AS (
    SELECT
        week,
        det_pts - opp_pts AS margin,
        CASE
            WHEN det_pts - opp_pts >= 15  THEN 'blowout W'
            WHEN det_pts - opp_pts >= 1   THEN 'close W'
            WHEN det_pts - opp_pts >= -14 THEN 'close L'
            ELSE                                'blowout L'
        END AS bucket
    FROM lions
)
SELECT
    bucket,
    COUNT(*)                          AS n_games,
    ROUND(AVG(margin)::numeric, 1)    AS avg_margin
FROM labeled
GROUP BY bucket
ORDER BY avg_margin DESC;