SQL Problem Solving: Writing Queries

Five Query-Writing Patterns

Picture the result rows you want, then pick the clauses that build them. For each pattern, name what one output row represents before writing the query.

Each pattern uses its own small illustrative schema.

1. The Funnel: find drop-offs (listened, but never shared).

2. The Comparison: individual vs. group metrics (artist vs. genre average).

3. The Ladder: rank items within groups (top songs per genre).

4. The Timeline: track cumulative behavior (running total of listens).

5. The Exception (optional): spot outliers (viral surges in daily listening).

Pattern 1: The Funnel

Keep users who appear in the listens set and exclude those in the shares set. As in the earlier NOT IN trap, this comparison assumes the shares user IDs contain no NULLs.

SELECT user_id, name
FROM users
-- Keep users who have listened.
WHERE user_id IN
  (SELECT user_id FROM listens)
-- Exclude users who have shared.
-- Assumes shares.user_id contains no NULLs.
  AND user_id NOT IN
  (SELECT user_id FROM shares)

Pattern 2: The Comparison

The window computes a genre average without collapsing the artist rows. Subtract that average from each artist’s plays: positive means above average, negative means below.

SELECT artist_name, genre, plays,
  -- Subtract this genre's average.
  plays - AVG(plays) OVER (
    PARTITION BY genre
  ) AS vs_avg
  -- Positive: above average; negative: below.
FROM artist_stats

Pattern 3: The Ladder

PARTITION BY restarts numbering for each genre; ORDER BY plays DESC puts the most-played songs first. The outer query then keeps row numbers 1 through 3.

SELECT * FROM (
  SELECT song, genre, plays,
    ROW_NUMBER() OVER (
      -- Number songs separately in each genre.
      PARTITION BY genre
      -- Most plays first.
      ORDER BY plays DESC
    ) AS rn
  FROM songs
) ranked
-- Keep up to three songs per genre.
WHERE rn <= 3

Pattern 4: The Timeline

An ordered SUM keeps each daily row and adds the hours up through that date. This example has one row per day: 3, then 3 + 5 = 8, then 10, then 14 cumulative hours.

SELECT date, hours,
  SUM(hours) OVER (
    -- Add hours from earlier dates and today.
    ORDER BY date
  ) AS cumulative_hours
FROM daily_listening

Pattern 5: The Exception (Optional)

LAG(listens) supplies the previous row’s listen count within each song, ordered by date. The first row has no predecessor, so its value is NULL. Compare the current count to that prior count, then filter the percentage change; NULLIF in the query avoids division by zero.

WITH changes AS (
  SELECT song_id, date, listens,
    -- Previous count for this song.
    LAG(listens) OVER (
      PARTITION BY song_id ORDER BY date
    ) AS prev_listens
  FROM daily_listens
),
with_pct AS (
  SELECT *,
    -- Percentage change; NULL for a zero baseline.
    (listens - prev_listens) * 100.0
      / NULLIF(prev_listens, 0) AS pct_change
  FROM changes
)
SELECT * FROM with_pct
-- Keep increases over 50%.
WHERE pct_change > 50

Example 1: Breakout Artists (Optional)

Problem: identify breakout artists with over 50% month-over-month growth in the last 3 months. Patterns used: Timeline (LAG for the previous month) and Comparison (the percent change).

Step 1: Count monthly plays. Establish a baseline: count plays for each artist per month.

WITH monthly_plays AS (
  SELECT
    artist_id,
    DATE_TRUNC('month', play_date) AS month,   -- round to the month
    COUNT(*) AS play_count
  FROM listens
  GROUP BY artist_id, month                     -- one row per artist-month
)
-- artist_id | month   | play_count
-- 1234      | 2024-09 | 1000        (Sept baseline)
-- 1234      | 2024-10 | 1600        (Oct growth)
-- 1234      | 2024-11 | 2500        (Nov spike)

Step 2: Calculate the growth rate. Compare each month to the previous one with LAG().

, artist_growth AS (
  SELECT
    artist_id, month, play_count,
    LAG(play_count) OVER (
      PARTITION BY artist_id        -- reset per artist
      ORDER BY month                -- in time order
    ) AS prev_month_plays,
    ROUND(
      (play_count - LAG(play_count) OVER (PARTITION BY artist_id ORDER BY month))
      * 100.0 / NULLIF(LAG(play_count) OVER (PARTITION BY artist_id ORDER BY month), 0),
      1
    ) AS growth_pct
  FROM monthly_plays
)
-- artist_id | month   | play_count | prev | growth_pct
-- 1234      | 2024-09 | 1000       | NULL | NULL      (no prior month)
-- 1234      | 2024-10 | 1600       | 1000 | 60.0      (+60%)
-- 1234      | 2024-11 | 2500       | 1600 | 56.3      (+56%)

Step 3: Find the breakout artists. Filter to over 50% growth in the last 3 months.

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                                        -- the breakout filter
  AND g.month >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months'
ORDER BY g.growth_pct DESC, g.month DESC

-- artist_name | month   | play_count | growth_pct
-- Dua Lipa    | 2024-10 | 1600       | 60.0        (+60% on Sept, artist 1234 above)
-- Dua Lipa    | 2024-11 | 2500       | 56.3        (+56% on Oct)

Takeaway

Start with the desired output, then build the intermediate results needed to produce it.