# SQL Coding Interview Patterns: Master These 10 Essential Techniques
## Introduction
SQL coding interviews often follow predictable patterns. By mastering these 10 essential techniques, you'll be prepared for 90% of SQL coding questions asked in technical interviews.
## Pattern 1: Ranking and Top-N Queries
### When to Use
- Finding top performers
- Ranking within groups
- Percentile calculations
### Example: Top 3 Salaries by Department
```sql
WITH ranked_salaries AS (
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
FROM employees
)
SELECT name, department, salary
FROM ranked_salaries
WHERE rn <= 3;
```
### Key Functions
- `ROW_NUMBER()`: Unique sequential numbers
- `RANK()`: Same rank for ties, gaps after
- `DENSE_RANK()`: Same rank for ties, no gaps
## Pattern 2: Running Totals and Moving Averages
### When to Use
- Cumulative calculations
- Trend analysis
- Financial reporting
### Example: Running Total of Sales
```sql
SELECT
date,
daily_sales,
SUM(daily_sales) OVER (ORDER BY date) as running_total,
AVG(daily_sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7_days
FROM sales_data;
```
## Pattern 3: Gap and Island Problems
### When to Use
- Finding consecutive sequences
- Identifying missing data
- Time series analysis
### Example: Find Consecutive Login Days
```sql
WITH login_groups AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) as rn,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) as group_date
FROM user_logins
)
SELECT
user_id,
MIN(login_date) as streak_start,
MAX(login_date) as streak_end,
COUNT(*) as consecutive_days
FROM login_groups
GROUP BY user_id, group_date
HAVING COUNT(*) >= 3;
```
## Pattern 4: Pivot and Unpivot Operations
### When to Use
- Converting rows to columns
- Creating cross-tabulations
- Reporting formats
### Example: Monthly Sales Pivot
```sql
SELECT
product_id,
SUM(CASE WHEN MONTH(order_date) = 1 THEN amount END) as jan_sales,
SUM(CASE WHEN MONTH(order_date) = 2 THEN amount END) as feb_sales,
SUM(CASE WHEN MONTH(order_date) = 3 THEN amount END) as mar_sales
FROM orders
GROUP BY product_id;
```
## Pattern 5: Self-Joins for Hierarchical Data
### When to Use
- Employee-manager relationships
- Category hierarchies
- Comparing records within same table
### Example: Find Employees Earning More Than Their Manager
```sql
SELECT
e.name as employee_name,
e.salary as employee_salary,
m.name as manager_name,
m.salary as manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;
```
## Pattern 6: Recursive CTEs
### When to Use
- Hierarchical data traversal
- Graph-like structures
- Multi-level relationships
### Example: Organization Hierarchy
```sql
WITH RECURSIVE org_hierarchy AS (
-- Anchor: Top-level managers
SELECT employee_id, name, manager_id, 0 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: Direct reports
SELECT e.employee_id, e.name, e.manager_id, oh.level + 1
FROM employees e
JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy ORDER BY level, name;
```
## Pattern 7: Conditional Aggregation
### When to Use
- Creating metrics with conditions
- Calculating ratios
- Business KPIs
### Example: Customer Segmentation Metrics
```sql
SELECT
customer_id,
COUNT(*) as total_orders,
COUNT(CASE WHEN order_amount > 100 THEN 1 END) as high_value_orders,
ROUND(
COUNT(CASE WHEN order_amount > 100 THEN 1 END) * 100.0 / COUNT(*), 2
) as high_value_percentage,
SUM(CASE WHEN YEAR(order_date) = 2024 THEN order_amount ELSE 0 END) as revenue_2024
FROM orders
GROUP BY customer_id;
```
## Pattern 8: Date and Time Calculations
### When to Use
- Time-based analysis
- Cohort studies
- Retention calculations
### Example: User Retention Analysis
```sql
WITH first_purchase AS (
SELECT
customer_id,
MIN(order_date) as first_order_date
FROM orders
GROUP BY customer_id
),
monthly_activity AS (
SELECT
fp.customer_id,
fp.first_order_date,
o.order_date,
DATEDIFF(MONTH, fp.first_order_date, o.order_date) as months_since_first
FROM first_purchase fp
JOIN orders o ON fp.customer_id = o.customer_id
)
SELECT
months_since_first,
COUNT(DISTINCT customer_id) as active_customers,
ROUND(
COUNT(DISTINCT customer_id) * 100.0 /
(SELECT COUNT(*) FROM first_purchase), 2
) as retention_rate
FROM monthly_activity
GROUP BY months_since_first
ORDER BY months_since_first;
```
## Pattern 9: Existence and Anti-Join Patterns
### When to Use
- Finding records that exist/don't exist
- Set operations
- Data validation
### Example: Customers Without Recent Orders
```sql
-- Using NOT EXISTS (usually more efficient)
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
);
-- Using LEFT JOIN
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
WHERE o.customer_id IS NULL;
```
## Pattern 10: Performance Optimization Patterns
### When to Use
- Large datasets
- Slow queries
- Production environments
### Key Techniques
#### 1. Index-Friendly Queries
```sql
-- Good: Uses index on order_date
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2024-02-01';
-- Bad: Function prevents index usage
SELECT * FROM orders
WHERE YEAR(order_date) = 2024
AND MONTH(order_date) = 1;
```
#### 2. Efficient Subqueries
```sql
-- Good: Using EXISTS
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
);
-- Less efficient: Using IN with subquery
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id FROM orders
);
```
## Interview Strategy: Pattern Recognition
### Step 1: Identify the Pattern
- Read the problem carefully
- Look for keywords: "top N", "running total", "consecutive", "hierarchy"
- Identify the core business logic
### Step 2: Choose the Right Tool
- Window functions for analytical queries
- CTEs for complex logic breakdown
- Self-joins for same-table relationships
- EXISTS/NOT EXISTS for set operations
### Step 3: Optimize for Performance
- Consider indexing implications
- Use appropriate join types
- Minimize data scanned
- Test with realistic data volumes
## Common Interview Questions by Pattern
### Ranking Pattern
- "Find the Nth highest salary"
- "Top 3 products by sales in each category"
- "Rank customers by total purchase amount"
### Running Calculations Pattern
- "Calculate running total of sales"
- "Moving average of stock prices"
- "Cumulative user registrations"
### Gap and Island Pattern
- "Find consecutive login days"
- "Identify missing sequence numbers"
- "Group consecutive dates"
### Hierarchical Pattern
- "Show organization chart"
- "Find all subordinates of a manager"
- "Calculate total team size"
## Practice Tips
1. **Start Simple**: Master basic patterns before complex combinations
2. **Use Sample Data**: Create test datasets to verify your solutions
3. **Time Yourself**: Practice under interview conditions
4. **Explain Your Approach**: Verbalize your thought process
5. **Consider Edge Cases**: Think about NULL values, empty results, duplicates
## Conclusion
These 10 SQL patterns form the foundation of most interview questions. By mastering them, you'll be able to:
- Quickly identify the appropriate approach
- Write efficient, readable queries
- Handle complex business requirements
- Optimize for performance
Ready to practice these patterns? [Start your SQL interview preparation →](/interview)