Interview Tips: Pattern Recognition & Composition

The Pattern Approach to SQL Interviews

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, 5 common SQL patterns cover 80% of interview questions.

How Interviews Actually 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

-- 1. Sketch the data structure
products: product_id, name, revenue, category

-- 2. Write high-level approach
"Need to PARTITION BY category,
 ORDER BY revenue,
 take top 3 per group"

-- 3. Build incrementally
SELECT *, 
  ROW_NUMBER() OVER (
    PARTITION BY category 
    ORDER BY revenue DESC
  ) AS topcategories
FROM products

-- 4. Add filter
WHERE topategories <= 3

-- Actually run these queries:
SELECT * FROM products LIMIT 5;  
-- Check data structure

SELECT *, revenue 
FROM products 
ORDER BY revenue DESC 
LIMIT 10;  
-- Verify ordering works

SELECT *, ROW_NUMBER() OVER (...) AS topproducts 
FROM products;  
-- Test window function

-- Then build final query

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

• Demonstrates production-ready code style

What They're Really Testing

Interviewers don't care if you memorize solutions. They want to see if you can:

1. Decompose complex problems into manageable pieces

2. Name things clearly (avoid confusing aliases like 'rank')

3. Write readable code that others can maintain

4. Think systematically about edge cases

Practice Strategy

In Your Project

1. Start with single patterns on your data

2. Add WHERE, HAVING, and see what breaks

3. Combine 2 patterns, then 3

4. 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

Interview Day Tips

1. Start simple - get a working query first

2. Use CTEs liberally - one pattern per CTE

3. Name everything clearly - days_since_signup not d

4. Test edge cases - NULLs, ties, empty results

The Meta Pattern

-- The interview meta-pattern
WITH 
clean_data AS (
    -- Handle NULLs, filters
),
pattern_one AS (
    -- First analytical pattern
),
pattern_two AS (
    -- Second analytical pattern  
),
final_output AS (
    -- Combine and format
)
SELECT * FROM final_output;

Remember: You're not just writing SQL. You're demonstrating that you can think in components, build systematically, and communicate clearly - exactly what they need on their team.