Interview Tips

Think of SQL patterns like musical notes (Do-Re-Mi-Fa-So). Just five notes, but you can combine them into millions of melodies. Similarly, combine the 5 common SQL patterns to reason through your project needs and interview questions.

How Interviews Work

Round 1: Single Pattern Test

Interviewers start with one pattern to test your fundamentals:

• "Find the top 3 products by revenue" → Ladder pattern

• "Show user activity over time" → Timeline pattern

• "Find users who did X but not Y" → Exception Finder

Round 2: Pattern Composition

Once you pass, they test if you can combine patterns:

• "Top 3 products by revenue over time" → Ladder + Timeline

• "Users with increasing activity who never purchased" → Funnel + Exception

• "Peak usage hours by user segment" → Timeline + Ladder

The WITH Clause: Your Best Friend

-- Modular, readable, composable
WITH ladder_pattern AS (
    -- Pattern 1: Find top items
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS RANK
    FROM products
),
timeline_pattern AS (
    -- Pattern 2: Track over time  
    SELECT *, SUM(amount) OVER (ORDER BY date) AS running_total
    FROM ladder_pattern
    WHERE RANK <= 3
)
SELECT * FROM timeline_pattern;

Why interviewers like this:

• Shows you can decompose problems into sub-parts

• Each CTE solves one piece clearly

• Easy to debug and explain your thinking

Practice Strategy

In Your Project

1. Start with single patterns on your data

2. Combine 2 patterns, then 3

3. Use descriptive aliases (user_signup_rank not r)

Common Combinations to Practice

• Ladder + Timeline: Top performers over time

• Funnel + Exception: Drop-off analysis

• Timeline + Comparison: Growth rate changes

• Ladder + Funnel: Top conversion paths