SQL Problem Solving: Your Visual Playbook

1. Understand What You Have

• What tables exist?
• How do they connect?
• What's the grain of each table?

2. Define What You Want

• What columns in output?
• One row per what?
• What calculations needed?

3. Connect the Dots

• Which JOINs needed?
• What filters (WHERE)?
• Any grouping (GROUP BY)?

-- Layer 1: Calculate user statistics
WITH user_stats AS (
  SELECT 
    user_id,
    COUNT(DISTINCT song_id) as unique_songs,
    COUNT(DISTINCT genre) as unique_genres,
    SUM(play_duration) as total_minutes
  FROM listens l
  JOIN songs s ON l.song_id = s.song_id
  GROUP BY user_id
),
-- Layer 2: Identify power users
power_users AS (
  SELECT 
    user_id,
    unique_songs,
    unique_genres,
    total_minutes,
    NTILE(10) OVER (ORDER BY total_minutes DESC) as decile
  FROM user_stats
  WHERE unique_genres >= 3
)
-- Layer 3: Final selection
SELECT 
  u.name,
  p.unique_songs,
  p.unique_genres,
  ROUND(p.total_minutes / 60.0, 1) as total_hours
FROM power_users p
JOIN users u ON p.user_id = u.user_id
WHERE p.decile = 1
ORDER BY p.total_minutes DESC;

📍 When to use:

• Users who did A but never B
• Conversion funnel analysis
• Multi-step user journeys
• Drop-off analysis

🔧 SQL:

-- Find users who played songs but never shared
-- Step 1: Get all users who have listened
-- Step 2: Exclude users who have shared
SELECT user_id, name
FROM users
WHERE user_id IN (
  SELECT user_id FROM listens  -- Has listened
)
AND user_id NOT IN (
  SELECT user_id FROM shares   -- Never shared
)

📍 When to use:

• Top 3 items per category
• Rank within departments
• Find nth best performer
• Percentile rankings

🔧 SQL:

-- Top 3 songs per genre using ROW_NUMBER()
-- ROW_NUMBER assigns 1, 2, 3... within each genre
SELECT * FROM (
  SELECT song, genre, plays,
    ROW_NUMBER() OVER (
      PARTITION BY genre  -- Restart numbering for each genre
      ORDER BY plays DESC -- Most played gets rank 1
    ) as rank
  FROM songs
) ranked
WHERE rank <= 3  -- Keep only top 3

📍 When to use:

• Running total over time
• Moving averages
• Month-to-date calculations
• Cumulative growth metrics

🔧 SQL:

-- Calculate running total of listening hours
-- Each row shows total hours up to that date
SELECT 
  date,
  hours,  -- Daily hours
  SUM(hours) OVER (
    ORDER BY date  -- Sum all previous + current
  ) as cumulative_hours
FROM daily_listening

📍 When to use:

• Compare to department average
• Percentile within group
• Above/below benchmark
• Peer comparisons

🔧 SQL:

-- Compare each user to their tier's average
-- Shows how far above/below average they are
SELECT 
  user_id,
  tier,
  hours,
  hours - AVG(hours) OVER (
    PARTITION BY tier  -- Calculate avg per tier
  ) as vs_avg  -- Positive = above average
FROM user_stats

📍 When to use:

• Sudden drops in activity
• Unusual spikes
• Behavior changes
• Anomaly detection

🔧 SQL:

-- Find big changes (>50% increase or decrease)
-- LAG() gets the previous row's value when ordered by date
WITH changes AS (
  SELECT *,
    LAG(value) OVER (ORDER BY date) as prev_value,
    -- Calculate percent change from previous period
    -- NULLIF prevents division by zero
    ((value - LAG(value) OVER (ORDER BY date)) * 100.0 / 
     NULLIF(LAG(value) OVER (ORDER BY date), 0)) as pct_change
  FROM metrics
)
SELECT * FROM changes 
WHERE ABS(pct_change) > 50  -- Keep only big changes

"artists" → Analyze each artist separately 📍 Row 2: GROUP BY

"strong growth" → Compare this month vs last month 📍 Row 10: LAG()

"recent" → Last 3 months only 📍 Row 3: WHERE filter

">50%" → Filter high-growth artists 📍 Row 3: WHERE filter

Why: Need raw numbers before we can calculate growth
What: Count plays for each artist per month

WITH monthly_plays AS (  -- Row 6: Build step-by-step with CTEs
  SELECT 
    artist_id,
    DATE_TRUNC('month', play_date) as month,
    COUNT(*) as play_count  -- Row 1: COUNT() aggregation
  FROM listens
  GROUP BY artist_id, month  -- Row 2: GROUP BY each artist
)
-- Result: artist_id | month     | play_count
--         1234      | 2024-09   | 1000
--         1234      | 2024-10   | 1600
--         1234      | 2024-11   | 2500

Why: Need to know if artist is growing
What: Compare each month to previous using LAG()

, artist_growth AS (
  SELECT 
    artist_id,
    month,
    play_count,
    LAG(play_count, 1) OVER (  -- Row 10: Get previous month's value
      PARTITION BY artist_id    -- Separate LAG() per artist
      ORDER BY month
    ) as prev_month_plays,
    -- Calculate % growth: (current - previous) / previous * 100
    ROUND(
      (play_count - LAG(play_count, 1) OVER (PARTITION BY artist_id ORDER BY month)) 
      * 100.0 / NULLIF(LAG(play_count, 1) OVER (PARTITION BY artist_id ORDER BY month), 0),
      1
    ) as growth_pct
  FROM monthly_plays
)
-- Result: artist_id | month   | play_count | prev_month | growth_pct
--         1234      | 2024-09 | 1000       | NULL       | NULL
--         1234      | 2024-10 | 1600       | 1000       | 60.0
--         1234      | 2024-11 | 2500       | 1600       | 56.3

Why: Filter to only high-growth artists
What: Apply >50% filter + recency filter

SELECT 
  a.name as artist_name,
  g.month,
  g.play_count,
  g.growth_pct
FROM artist_growth g
JOIN artists a ON g.artist_id = a.artist_id
WHERE g.growth_pct > 50  -- Row 3: Filter high growth
  AND g.month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '3 months')  -- Recent only
ORDER BY g.growth_pct DESC, g.month DESC

-- Final Result:
-- artist_name   | month    | play_count | growth_pct
-- The Weeknd    | 2024-11  | 2500       | 56.3
-- The Weeknd    | 2024-10  | 1600       | 60.0
-- Dua Lipa      | 2024-11  | 3200       | 78.0
-- ...

✅ Do This

• Start simple, add complexity gradually
• Test with small data first
• Use CTEs for readability
• Name things clearly (not t1, t2)
• Check for NULLs explicitly

❌ Avoid This

• Deeply nested subqueries (>3 levels)
• SELECT * in production
• Forgetting GROUP BY with aggregates
• Not handling division by zero
• Ignoring index opportunities