Top 20 SQL Queries Every Data Analyst Must Know in 2026 | Data Analyst Interview
🗄️ SQL Reference

Top 20 SQL Queries Every Data Analyst Must Know in 2026

The 20 SQL patterns that show up in 80% of data analyst interviews at Google, Amazon, Flipkart and more — explained clearly with real examples.

Book Free Session ← SQL Interview
The 20 Patterns
Grouped by topic — master these and you handle any SQL round

Every pattern below has appeared in real data analyst interviews in 2025–2026. Learn the pattern, not just the syntax.

Easy

1. GROUP BY with HAVING — filter aggregated results

Topic: Aggregation · SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*) > 5
Easy

2. INNER JOIN — combine rows from two tables on a key

Topic: Joins · SELECT o.order_id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id
Easy

3. LEFT JOIN — keep all rows from left table, NULLs where no match

Topic: Joins · Find customers who have never placed an order
Medium

4. Subquery in WHERE — filter using a derived result

Topic: Subqueries · SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders)
Medium

5. CTE (WITH clause) — readable multi-step queries

Topic: CTEs · WITH ranked AS (…) SELECT * FROM ranked WHERE rn = 1
Medium

6. RANK() / ROW_NUMBER() — rank rows within a partition

Topic: Window Functions · RANK() OVER(PARTITION BY dept ORDER BY salary DESC)
Medium

7. LAG() / LEAD() — access the previous or next row’s value

Topic: Window Functions · LAG(revenue) OVER(ORDER BY month) for MoM comparison
Medium

8. SUM() OVER() — running total without GROUP BY

Topic: Window Functions · SUM(amount) OVER(PARTITION BY user_id ORDER BY order_date)
Medium

9. DATE_TRUNC / DATE_FORMAT — group by week, month, quarter

Topic: Date Functions · DATE_TRUNC(‘month’, order_date) AS order_month
Medium

10. CASE WHEN — conditional columns and category bucketing

Topic: Conditionals · CASE WHEN amount > 1000 THEN ‘High’ WHEN amount > 500 THEN ‘Medium’ ELSE ‘Low’ END
Medium

11. COALESCE / NULLIF — handle NULL values safely

Topic: NULL Handling · COALESCE(discount, 0), NULLIF(denominator, 0) to avoid divide-by-zero
Medium

12. Self JOIN — compare a table to itself (consecutive events, hierarchy)

Topic: Joins · Find employees who earn more than their manager
Hard

13. Second highest value without LIMIT — using subquery or DENSE_RANK

Topic: Ranking · SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp)
Hard

14. Rolling N-day average — window frame with ROWS BETWEEN

Topic: Window Functions · AVG(dau) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
Hard

15. Month-over-month growth — LAG + percentage calculation

Topic: Business Metrics · (revenue – LAG(revenue)) / NULLIF(LAG(revenue), 0) * 100
Hard

16. Retention / Cohort query — self join on user and month offset

Topic: Product Analytics · LEFT JOIN on next month to find who returned vs churned
Hard

17. Deduplicate rows — ROW_NUMBER() = 1 pattern

Topic: Data Cleaning · WITH deduped AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY created_at DESC) rn) SELECT * WHERE rn=1
Hard

18. Pivot with CASE WHEN — turn rows into columns

Topic: Reshaping · SUM(CASE WHEN month=’Jan’ THEN revenue END) AS jan_revenue
Hard

19. Consecutive days / streak detection — date difference = 1 pattern

Topic: Date Logic · Find users who logged in 3+ consecutive days using LAG and GROUP BY island
Hard

20. Percentile / median — PERCENTILE_CONT or subquery approach

Topic: Statistics · PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) for median salary

Want to practise all 20 patterns?

Book a free SQL mock — we’ll run you through 5 real interview questions with live feedback on your approach and syntax.

Book Free SQL Mock
Subscribe
Subscribe