Lions 40+ point games in 2024

Level 1 · Challenge 6

Starter

Prompt

Return every 2024 regular-season game where the Lions scored 40 or more points. Show week, opponent, Lions points, opponent points, and result. Order by week.

Expected output

week	opponent	det_points	opp_points	result
(int)	(team)	(int)	(int)	W / L

Hint

The Lions can be the home_team or the away_team for any given game. The cleanest approach is a CASE expression that picks the right side. result in the schedules table is the point differential from the home team’s view, so infer W/L from whichever side the Lions are on.

Solution

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 det_points,
    CASE WHEN home_team = 'DET' THEN away_score ELSE home_score END AS opp_points,
    CASE
        WHEN home_team = 'DET' AND home_score > away_score THEN 'W'
        WHEN away_team = 'DET' AND away_score > home_score THEN 'W'
        WHEN home_score = away_score THEN 'T'
        ELSE 'L'
    END AS result
FROM schedules
WHERE season = 2024
  AND game_type = 'REG'
  AND (
       (home_team = 'DET' AND home_score >= 40)
    OR (away_team = 'DET' AND away_score >= 40)
  )
ORDER BY week;

CASE is the SQL ternary. You’ll see it constantly — this is your warm-up.