SQL Mastery Checklist — Data Analyst Roadmap
The complete SQL journey for aspiring data analysts. 32 priority-ranked topics, query patterns, a 30-60-90 day plan, 11 portfolio projects, real-world scenarios, and interview prep — everything you need to be job-ready.
Priority-Based Topic Checklist
Ordered by recommended learning sequence. Expand each for context; check off as you master them.
01.SELECT, FROM, WHERE — SQL Core Syntax
Most CriticalEasy 2–3 hrsOrder #1● Critical02.Filtering & Conditional Logic — AND, OR, IN, BETWEEN, LIKE, CASE WHEN
Must KnowEasy 3–4 hrsOrder #2● Critical03.Aggregate Functions — COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING
Must KnowEasy 3–4 hrsOrder #3● Critical04.SQL Joins — INNER, LEFT, RIGHT, FULL, SELF, CROSS
Most CriticalMedium 6–8 hrsOrder #4● Critical05.NULL Handling & Data Cleaning in SQL
Must KnowEasy 3–4 hrsOrder #5● Critical06.Subqueries & Correlated Subqueries
Must KnowMedium 4–5 hrsOrder #6● High07.CTEs (Common Table Expressions) — WITH Clause
Must KnowMedium 4–5 hrsOrder #7● Critical08.Window Functions — ROW_NUMBER, RANK, LEAD, LAG, Running Totals
Most CriticalHard 5–6 hrsOrder #8● Critical09.Date & Time Functions — DATE_TRUNC, DATEDIFF, EXTRACT, Time Series
Must KnowMedium 4–5 hrsOrder #9● Critical10.String Functions — CONCAT, SUBSTRING, TRIM, LOWER, REPLACE, REGEXP
Must KnowEasy 3–4 hrsOrder #10● High11.UNION, UNION ALL, INTERSECT, EXCEPT — Set Operations
Must KnowEasy 2–3 hrsOrder #11● High12.Views, Temp Tables & Materialized Views
Must KnowMedium 3–4 hrsOrder #12● High13.Cohort Analysis SQL — Retention & Lifetime Value
Must KnowHard 6–8 hrsOrder #13● Critical14.Funnel Analysis SQL — Conversion & Drop-off
Hard 5–6 hrsOrder #14● High15.Customer Segmentation SQL — RFM & Behavioral
Hard 5–6 hrsOrder #15● High16.Revenue & KPI Analytics SQL
Medium 5–6 hrsOrder #16● High17.Query Optimization & Execution Plans
Hard 6–8 hrsOrder #17● Critical18.Indexing Fundamentals for Analysts
Medium 3–4 hrsOrder #18● High19.Data Warehouse Concepts — Star Schema, Fact/Dim Tables, OLAP vs OLTP
Medium 4–5 hrsOrder #19● High20.Product Analytics SQL — DAU, MAU, Engagement Metrics
Hard 5–6 hrsOrder #20● High21.Finance Analytics SQL — P&L, Revenue, Cost Analysis
Hard 5–6 hrsOrder #21● High22.Marketing Analytics SQL — Campaign, Attribution & CAC
Medium 4–5 hrsOrder #22● High23.Logistics & Operations Analytics SQL
Medium 4–5 hrsOrder #23● High24.Advanced CASE WHEN — Pivoting & Conditional Aggregation
Medium 3–4 hrsOrder #24● High25.Advanced Window Frame Clauses — ROWS BETWEEN, RANGE BETWEEN
Hard 4–5 hrsOrder #25● High26.SQL for Dashboard Reporting — Reporting-Ready Queries
Medium 4–5 hrsOrder #26● High27.PIVOT / UNPIVOT & Dynamic SQL Concepts
Hard 4–5 hrsOrder #27● High28.Cloud SQL Platforms — BigQuery, Redshift, Snowflake, Databricks
Medium 5–6 hrsOrder #28● High29.ETL / ELT SQL Patterns — Data Pipeline Queries
Hard 5–6 hrsOrder #29● High30.Stored Procedures & User-Defined Functions
Hard 4–5 hrsOrder #30● High31.SQL for A/B Testing & Experimentation
Hard 5–6 hrsOrder #31● High32.Advanced Analytical Patterns — Sessionization, Gap-and-Island, Running Balance
Hard 5–6 hrsOrder #32● High
Must Know for Every Data Analyst
You will use all of these in the first week of any data analyst role.
Query Fundamentals
- ›SELECT with explicit columns (never SELECT *)
- ›WHERE with all filter operators
- ›GROUP BY + HAVING for aggregations
- ›ORDER BY with multiple columns
- ›LIMIT for sampling and testing
- ›Aliases for readability
Joins (Most Critical)
- ›INNER JOIN — matching rows only
- ›LEFT JOIN — preserve all left rows
- ›Anti-join pattern (LEFT JOIN + WHERE IS NULL)
- ›Multi-table joins (3+ tables)
- ›Validate row counts before/after every join
Aggregation
- ›COUNT(*) vs COUNT(col) vs COUNT(DISTINCT)
- ›SUM, AVG, MIN, MAX
- ›GROUP BY with correct column rules
- ›HAVING for post-aggregation filters
- ›Conditional aggregation with CASE WHEN + SUM
Date Operations
- ›DATE_TRUNC for period grouping
- ›DATEDIFF for interval calculation
- ›EXTRACT for year, month, day of week
- ›Filter on date range: > AND <
- ›CURRENT_DATE / NOW() for relative dates
Query Structure
- ›CTEs (WITH clause) for readability
- ›Subqueries in FROM and WHERE
- ›UNION ALL for combining datasets
- ›CASE WHEN for conditional logic
- ›NULL handling: IS NULL, COALESCE, NULLIF
Window Functions (Senior-Level)
- ›ROW_NUMBER() for deduplication and ranking
- ›RANK/DENSE_RANK for leaderboards
- ›LAG/LEAD for period-over-period
- ›SUM() OVER for running totals
- ›PARTITION BY vs GROUP BY distinction
Window Functions — Deep Dive Reference
The single most important advanced SQL skill. Master these patterns and you'll stand out in every interview.
Running & Rolling Calculations
- SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
- AVG(daily_revenue) OVER (ORDER BY report_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg
- ROUND((revenue - LAG(revenue) OVER(ORDER BY month)) / NULLIF(LAG(revenue) OVER(ORDER BY month), 0) * 100, 2) AS mom_growth_pct
- ROUND(revenue / SUM(revenue) OVER(PARTITION BY category) * 100, 2) AS pct_of_category
Ranking Functions
- ROW_NUMBER(): always unique — 1, 2, 3 even on ties
- RANK(): gaps on ties — 1, 1, 3
- DENSE_RANK(): no gaps on ties — 1, 1, 2
- NTILE(4): bucket rows into quartiles 1–4
- PERCENT_RANK(): 0 to 1 percentile rank
- CUME_DIST(): cumulative distribution 0–1
Navigation Functions
- LAG(col, 1): value from previous row (MoM comparison)
- LEAD(col, 1): value from next row (next-period preview)
- FIRST_VALUE(col): first value in window (first order date)
- LAST_VALUE(col) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
- NTH_VALUE(col, n): nth value in window
Common Window Patterns
- Deduplication: ROW_NUMBER() PARTITION BY key ORDER BY updated_at DESC — WHERE rn = 1
- Top-N per group: RANK() PARTITION BY category ORDER BY revenue DESC — WHERE rnk <= 3
- MoM growth: LAG(revenue) OVER (ORDER BY month) — calculate % change
- First-purchase flag: ROW_NUMBER() PARTITION BY customer ORDER BY order_date — WHERE rn = 1
- % of total: SUM(revenue) OVER () as denominator (no PARTITION)
Business Analytics SQL — Industry Patterns
The analytics patterns every product, growth, and business analyst writes regularly in industry.
E-Commerce Analytics
- ›Revenue by category, brand, channel
- ›Average basket size and average order value
- ›Repeat purchase rate: customers with 2+ orders / total
- ›Cart abandonment: added to cart but no purchase
- ›Product affinity: frequently bought together
- ›Discount impact on margin
SaaS/Subscription
- ›MRR: SUM of recurring revenue this month
- ›Churn rate: cancelled / start-of-period customers
- ›Expansion MRR: upgrades within period
- ›Net Revenue Retention (NRR)
- ›Trial-to-paid conversion rate
- ›Feature adoption by plan tier
Mobile/App Analytics
- ›DAU/MAU ratio (stickiness)
- ›Day-1/7/30 retention after install
- ›Session duration distribution
- ›Push notification click-through rate
- ›User path analysis (event sequences)
- ›Crash / error rate per app version
Finance Analytics
- ›Gross margin %: (Rev - COGS) / Rev
- ›Accounts receivable aging buckets
- ›Budget vs Actual variance
- ›Cash conversion cycle
- ›Revenue by cost centre and GL code
- ›YoY and MoM revenue trends
Logistics Analytics
- ›On-time delivery rate by carrier
- ›Average lead time by route/region
- ›SLA breach count and % by week
- ›Return rate by product category
- ›Inventory turnover and stockout flags
- ›Warehouse throughput by shift
Marketing Analytics
- ›Cost per acquisition by channel
- ›Campaign ROI: (Rev - Spend) / Spend
- ›Email conversion rate by segment
- ›Attribution modelling (first/last touch)
- ›ROAS by ad set and audience
- ›Multi-touch attribution with window functions
30-60-90 Day SQL Learning Plan
For a working analyst putting in 1–2 hrs/day. Weekends include 3–4 hr project sessions.
Foundation + Joins + Aggregations
Target: 80% of real-world SQL work covered
Window Functions + Business Analytics + Optimization
Target: senior-level query writing and business analytics
Advanced Patterns + Cloud SQL + Interview Prep
Target: production-ready SQL + interview confidence
4-Phase Interview Preparation Roadmap
Expect live SQL exercises — practice for speed and accuracy.
Foundation — Core SQL Cold
- ›INNER JOIN, LEFT JOIN, anti-join pattern
- ›GROUP BY + HAVING with aggregate filter
- ›CASE WHEN for segmentation
- ›CTE with 2+ steps
- ›ROW_NUMBER() deduplication
- ›LeetCode SQL — Medium difficulty, timed
- ›Write solution, then optimize it
- ›Narrate your thinking out loud
- ›stratascratch.com for real company questions
Window Functions Sprint
- ›ROW_NUMBER() deduplication
- ›RANK/DENSE_RANK for leaderboards and Top-N
- ›LAG for MoM/YoY comparison
- ›SUM OVER for running totals
- ›Rolling 7-day average with frame clause
- ›FIRST_VALUE for first-touch attribution
- ›Explain PARTITION BY vs GROUP BY when asked
- ›Mention ROW_NUMBER vs RANK difference proactively
- ›Discuss frame clause (ROWS BETWEEN) for rolling calcs
- ›Note that window functions can't be used in WHERE — use CTE
Business Analytics Problems
- ›Write a cohort retention query for monthly signups
- ›Find the 2nd highest salary in each department
- ›Calculate 7-day rolling average of daily revenue
- ›Find customers who placed an order in Jan but not Feb
- ›Write a funnel query for our sign-up flow
- ›Restate the question + clarify assumptions
- ›Describe your approach before coding
- ›Build step-by-step using CTEs
- ›Test with edge cases: NULLs, duplicates, empty groups
- ›Mention performance considerations at the end
Top 40 Interview Questions
- ›SQL query execution order? (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT)
- ›Difference between WHERE and HAVING?
- ›INNER JOIN vs LEFT JOIN — when does each produce different results?
- ›How to find rows in Table A with no match in Table B?
- ›A JOIN produces more rows than either input. Why and how to fix it?
- ›What is a self-join? Give a business example.
- ›COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)?
- ›Why can't you use WHERE col = NULL? What do you use instead?
- ›Write a cohort retention query showing Month-0 to Month-3 retention.
- ›How do you build a funnel analysis query?
- ›Find month-over-month revenue growth %.
- ›How do you find the second-highest salary per department?
- ›Find customers who ordered in January but not in February.
- ›How do you calculate DAU, WAU, and MAU?
- ›Write an RFM segmentation query using NTILE.
- ›How do you deduplicate records keeping only the most recent?
Top SQL Interview Questions
Common questions asked at data analyst interviews across tech, finance, and consulting.
CTEs & Subqueries
- What is a CTE and how is it different from a subquery?
- When would you use a temp table over a CTE?
- What is a correlated subquery? Why is it slow?
- Can you reference a CTE multiple times in the same query?
- What is a recursive CTE? Give a use case.
Window Functions
- Difference between ROW_NUMBER, RANK, and DENSE_RANK?
- What is PARTITION BY vs GROUP BY?
- How do you calculate a 7-day rolling average in SQL?
- Write a query to find the first order for each customer.
- How do you get the Top-3 products per category using window functions?
- Write a query to detect consecutive active days (streak).
Performance & Architecture
- A query that took 10s now takes 10 minutes. How do you debug it?
- What is an execution plan and what are hash joins vs nested loops?
- Why does applying a function to an indexed column hurt performance?
- What is the difference between a view and a materialized view?
- What is a star schema? Why is it preferred for analytics?
- What is the difference between OLTP and OLAP databases?
- What is partition pruning and how does it save query cost?
- When would you create an index on a table?
Hands-On Projects & Portfolio Ideas
Build these to prove real-world SQL mastery. Each covers multiple topic areas.
Sales Data Exploration
SELECT, WHERE, ORDER BY, LIMIT, simple aggregations, GROUP BY on a 10K-row sales dataset.
Customer Segmentation by Region
JOINs (customers + orders), CASE WHEN for segmenting, COUNT and SUM aggregations by region.
Monthly Revenue Report
DATE_TRUNC, GROUP BY month, SUM revenue, LAG for MoM change, COALESCE for NULL handling.
Cohort Retention Analysis
CTEs for cohort definition, DATEDIFF for cohort age, cohort retention matrix with COUNT DISTINCT.
Funnel Conversion Analysis
Sessionization CTE, event sequencing, funnel step conversion rates with LEAD/LAG.
RFM Customer Segmentation
NTILE(5) for recency, frequency, monetary scores; combined RFM segment assignment.
Product Sales Dashboard SQL
Window functions for running totals, RANK for top products, revenue per category.
User Retention & LTV Analysis
Cohort retention + LTV calculation using SUM OVER, cohort cumulative revenue.
Marketing Attribution Model
Multi-touch attribution with window functions, FIRST_VALUE for first touch, LAG for sequence.
Logistics Performance Analytics
On-time delivery, SLA breach analysis, carrier performance ranking (RANK), lead time distribution (NTILE).
Finance P&L Analytics
Revenue by cost centre, YoY/MoM trends, gross margin, budget vs actual with CASE WHEN.
Business Scenarios You'll Actually Face
Common asks from actual analyst roles. Know how to solve each end-to-end.
Scenario 1 — 'Revenue dropped 15% last week - find out why'
- ›Break down by: region, product, channel, customer tier
- ›Compare last week vs prior week using LAG or date filter
- ›Check new vs returning customer mix
- ›Identify if one segment drove entire drop
- ›GROUP BY region + week using DATE_TRUNC
- ›CASE WHEN to flag current vs prior week
- ›SUM + conditional aggregation for side-by-side
- ›Window LAG for % change per dimension
- ›Segment-level variance report
- ›% contribution to drop per segment
- ›Executive summary of findings
- ›Recommended next steps to investigate
Scenario 2 — 'How many users are still active 30/60/90 days after signing up?'
- ›CTE 1: first sign-up date per user
- ›CTE 2: JOIN to activity table
- ›Check if activity_date between signup and signup+30/60/90
- ›COUNT(DISTINCT user_id) per retention window
- ›MIN(event_date) OVER — first activity
- ›DATEDIFF / DATE_ADD for window boundaries
- ›CASE WHEN for day-30/60/90 flag
- ›COUNT(DISTINCT) for unique active users
- ›Month 0–3 retention matrix
- ›Retention curve visualization
- ›Churn flags by cohort
- ›Executive retention dashboard
Scenario 3 — 'Identify our top 20% of customers by revenue'
- ›SUM revenue per customer
- ›NTILE(5) to segment into quintiles
- ›Filter where quintile = 5 (top 20%)
- ›Join back to orders for behaviour detail
- ›Avg order frequency, AOV, preferred category
- ›Days since last order (recency)
- ›Geographic distribution
- ›Acquisition channel using first-touch attribution
- ›VIP customer list
- ›Behavioural profile of top customers
- ›Targeted marketing recommendations
- ›Retention strategy for VIPs
Scenario 4 — 'Find customers who bought in January but not in February'
- ›CTE jan_buyers: DISTINCT customers who ordered in Jan
- ›CTE feb_buyers: DISTINCT customers who ordered in Feb
- ›EXCEPT / LEFT JOIN anti-join to find jan_buyers not in feb_buyers
- ›Add profile info from customer table
- ›Add their Jan revenue (how much are we losing?)
- ›Categorize by last order value tier
- ›Filter to specific product categories
- ›This is a winback list for CRM campaigns
- ›Winback customer list
- ›Revenue at risk report
- ›Segment-specific offer recommendations
- ›CRM campaign targeting file
SQL Best Practices Used at Companies
Production-standard SQL habits followed by analytics teams at tech companies, consulting firms, and data-driven organisations.
Code Style & Readability
- ›Uppercase SQL keywords: SELECT, FROM, WHERE, JOIN
- ›One clause per line, consistent indentation
- ›Meaningful table aliases: cust not c, monthly_rev not t1
- ›CTEs for every multi-step query — no deeply nested subqueries
- ›Comment the WHY: -- Exclude test users (user_id < 8)
- ›Add a header comment: date, author, purpose, dependencies
Data Integrity Checks
- ›Row count before and after every JOIN
- ›Validate primary key uniqueness: COUNT(*) vs COUNT(DISTINCT id)
- ›Check for NULLs in key columns before aggregating
- ›Verify date ranges cover expected period
- ›Add ASSERT-style checks in data pipeline SQL
- ›Never assume data is clean — always profile first
Performance Standards
- ›Never SELECT * in production — name all columns
- ›Filter on partition/index columns in WHERE
- ›Push filters as early as possible (before joins)
- ›Use LIMIT 100 during development/testing
- ›UNION ALL over UNION unless deduplication is needed
- ›Run EXPLAIN/EXPLAIN ANALYZE before submitting production queries
Production SQL Standards
- ›Use views for reusable business logic
- ›Materialized views for dashboard-serving queries
- ›Parameterize dates — never hardcode '2024-01-01'
- ›Version control SQL files in Git (dbt, GitHub)
- ›Write idempotent queries (safe to re-run)
- ›Document table grain: 'one row per customer per day'
Debugging Approach
- ›Start simple — add complexity incrementally
- ›Test CTEs individually before combining
- ›Use SELECT * on intermediate CTEs to inspect results
- ›Validate join cardinality at each step
- ›Check that CASE WHEN ELSE handles all edge cases
- ›Use NULLIF to prevent division-by-zero errors
Anti-Patterns to Avoid
- ›Applying functions to indexed columns in WHERE
- ›Correlated subqueries that run per row (use CTE + JOIN)
- ›Using DISTINCT as a lazy fix for duplicate rows
- ›Non-deterministic ORDER BY (same-value ties)
- ›String concatenation for date filters
- ›Magic numbers without comments: WHERE status = 4 (what is 4?)