Master SQL for data analysts with real business queries, visual guides, and interview prep. From SELECT to window functions, the skills that get you hired.
Jump into the SQLNoir case files and put these tips to work.
SQL is the single most requested skill in data analyst job postings. This guide covers the exact SQL skills you need, with real queries you will write on the job.
Excel handles a few thousand rows fine. Modern businesses generate millions of records daily. Excel crashes at 1 million rows. SQL handles billions without breaking a sweat.
The real difference: Excel downloads data to your computer. SQL queries the data where it lives, in production databases, data warehouses, and cloud systems. You get real-time answers without waiting for exports or crashing your laptop.
| Task | Excel | SQL |
|---|---|---|
| Handle 1M+ rows | ❌ Crashes | ✅ Billions, no problem |
| Combine data sources | ⚠️ VLOOKUP (fragile) | ✅ JOIN (robust) |
| Reproduce analysis | ⚠️ Manual steps | ✅ Re-run query |
| Automate reports | ⚠️ Macros (brittle) | ✅ Scheduled queries |
| Team collaboration | ⚠️ Email files around | ✅ Shared query repo |
| Real-time data | ❌ Static exports | ✅ Live database queries |
SQL vs Excel for Data Analysis Tasks
Every major BI tool (Tableau, Power BI, Looker, Metabase) runs on SQL under the hood. When you write "calculated fields" or "custom queries" in these tools, you're writing SQL. Master SQL, and you master all of them.
SELECT
product_category,
COUNT(*) as total_orders,
SUM(revenue) as total_revenue,
AVG(revenue) as avg_order_value
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY product_category
ORDER BY total_revenue DESC;This single query answers "What are our top product categories this year?" Something that would take multiple pivot tables and VLOOKUP formulas in Excel.
If you want to practice writing aggregation queries hands-on, SQLNoir's detective cases let you write real queries to solve mysteries. Perfect for building muscle memory with GROUP BY, SUM, and COUNT patterns that show up in every dashboard.
The most common SQL confusion: the order you write a query is not the order the database runs it. You write SELECT first, but the database processes FROM first. Understanding this eliminates a whole class of errors.
Pick the table(s)
Filter individual rows
Create groups
Filter groups
Pick columns & compute
Sort results
Cap row count
SQL Query Execution Order (not the same as writing order)
This is why you can't reference a column alias from SELECT inside a WHERE clause. WHERE runs before SELECT even exists. Here's a practical example with execution steps annotated:
-- You WRITE it in this order:
SELECT
region, -- Step 5: Pick columns
COUNT(*) as total_orders, -- Step 5: Compute aggregates
SUM(revenue) as total_revenue -- Step 5: Compute aggregates
FROM orders -- Step 1: Start here
WHERE order_date >= '2025-01-01' -- Step 2: Filter rows
GROUP BY region -- Step 3: Create groups
HAVING SUM(revenue) > 10000 -- Step 4: Filter groups
ORDER BY total_revenue DESC -- Step 6: Sort
LIMIT 10; -- Step 7: Cap resultsThe database starts at FROM, not SELECT. This is why "total_revenue" works in ORDER BY (step 6, after SELECT) but not in WHERE (step 2, before SELECT).
You don't need to memorize 200 SQL commands. These seven skills cover 95% of data analyst work. Master them, and interviewers will notice.
Every SQL query starts with SELECT. It picks which columns to retrieve. WHERE filters which rows to include. Think of it as Excel's filter dropdown, but infinitely more powerful.
SELECT customer_name, email, total_purchases FROM customers WHERE signup_date >= '2024-01-01' AND total_purchases > 500 AND country = 'USA';
Find high-value US customers who signed up this year. Filters combine with AND, OR, IN, BETWEEN, and LIKE for pattern matching.
This is where data analysis actually happens. Aggregate functions summarize data: count rows, sum values, calculate averages. GROUP BY breaks data into segments by region, by month, by product category.
SELECT
DATE_TRUNC('month', order_date) as month,
COUNT(DISTINCT customer_id) as unique_customers,
SUM(revenue) as monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;Monthly revenue and customer count trend. A classic analyst report you will write weekly.
Real data lives across multiple tables. Customers in one table, orders in another, products in a third. JOINs connect them. This skill separates basic SQL users from actual data analysts. For a deep dive, see our complete guide to SQL join types.
SELECT
c.customer_name,
COUNT(o.order_id) as total_orders,
SUM(o.amount) as lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.amount) > 1000;Customer lifetime value analysis. LEFT JOIN keeps all customers, even those with zero orders.
INNER JOIN: Only matching rows from both tables.
LEFT JOIN: All rows from the left table, matches from right (most common in analysis).
FULL OUTER JOIN: All rows from both tables, even without matches.
Sometimes you need two steps: "Find all customers whose spending is above average." First, calculate the average. Then, filter by it. Subqueries let you do both in one statement.
SELECT customer_name, total_spending
FROM customers
WHERE total_spending > (
SELECT AVG(total_spending)
FROM customers
);The inner query calculates the average; the outer query filters by it. One statement, two logical steps.
Window functions are the power tools of SQL analytics. They calculate running totals, rankings, and comparisons without collapsing your rows. This is the skill that impresses interviewers and enables sophisticated analysis.
SELECT
salesperson,
month,
revenue,
SUM(revenue) OVER (
PARTITION BY salesperson ORDER BY month
) as running_total,
RANK() OVER (
PARTITION BY month ORDER BY revenue DESC
) as monthly_rank
FROM sales;Running totals and rankings per salesperson. Two window functions, one query, zero row collapse.
CASE WHEN is SQL's if/else. Use it to categorize data, create buckets, or build conditional aggregations. Data analysts use this daily for segmentation and reporting.
SELECT
customer_id,
total_spent,
CASE
WHEN total_spent >= 5000 THEN 'VIP'
WHEN total_spent >= 1000 THEN 'Regular'
ELSE 'Occasional'
END as customer_tier,
CASE
WHEN last_order_date >= CURRENT_DATE - INTERVAL '30 days'
THEN 'Active'
ELSE 'Inactive'
END as status
FROM customers;Two CASE expressions in one query: segment by spending tier and flag activity status. Your marketing team will request this weekly.
Common Table Expressions (CTEs) let you break complex queries into named, readable steps. Instead of nesting subqueries three levels deep, you write each step as a named block. Every senior analyst uses these.
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', order_date) as month,
SUM(revenue) as revenue
FROM orders
GROUP BY 1
),
revenue_with_growth AS (
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) as prev_month
FROM monthly_revenue
)
SELECT
month,
revenue,
prev_month,
ROUND(100.0 * (revenue - prev_month) / prev_month, 1) as growth_pct
FROM revenue_with_growth
WHERE prev_month IS NOT NULL
ORDER BY month;Month-over-month revenue growth in three clean steps. Each CTE builds on the last. Compare this readability to nested subqueries.
Practice These SQL Patterns Hands-On
The multi-table JOINs, aggregations, and window functions you just learned work the same way in detective cases. SQLNoir lets you query suspects, interviews, and evidence tables to crack mysteries.
Theory is nice. Here are three queries you will actually write as a data analyst, taken from real business scenarios.
Business Question: "Segment our customers by purchase frequency and identify our VIPs."
SELECT
customer_id,
COUNT(*) as purchase_count,
SUM(amount) as total_spent,
CASE
WHEN COUNT(*) >= 10 AND SUM(amount) >= 5000 THEN 'VIP'
WHEN COUNT(*) >= 5 THEN 'Regular'
ELSE 'Occasional'
END as customer_segment
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY customer_id;Combines aggregation (COUNT, SUM), filtering (WHERE with dates), and conditional logic (CASE WHEN) in one query. The output feeds directly into a marketing dashboard.
Business Question: "Show me revenue growth compared to the previous month."
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', order_date) as month,
SUM(revenue) as revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 1
) as growth_pct
FROM monthly_revenue
ORDER BY month;Uses a CTE for the first aggregation step, then LAG() to compare each month against the previous one. Your CFO will ask for this report monthly.
Business Question: "How well do we retain customers from each signup month?"
WITH cohorts AS (
SELECT
customer_id,
DATE_TRUNC('month', signup_date) as cohort_month
FROM customers
)
SELECT
co.cohort_month,
DATE_PART('month', AGE(o.order_date, co.cohort_month))
as months_since_signup,
COUNT(DISTINCT co.customer_id) as active_customers
FROM cohorts co
JOIN orders o ON co.customer_id = o.customer_id
GROUP BY 1, 2
ORDER BY 1, 2;The foundation of a cohort retention table. Product and marketing teams use this to measure whether customers stick around after their first purchase.
Technical interviews for data analyst roles almost always include SQL. Here is what interviewers actually test and the patterns that repeat.
| Interview Pattern | SQL Skills Tested | Difficulty |
|---|---|---|
| Find the top N by metric | GROUP BY, ORDER BY, LIMIT or RANK() | ⭐⭐ |
| Compare time periods | DATE functions, LAG(), window frames | ⭐⭐⭐ |
| Users who did X but not Y | LEFT JOIN + IS NULL, NOT IN | ⭐⭐⭐ |
| Calculate rates or percentages | CASE WHEN, COUNT, division | ⭐⭐ |
| Cumulative / running totals | SUM() OVER(), window functions | ⭐⭐⭐⭐ |
Common Data Analyst SQL Interview Patterns
Here is a realistic timeline to go from zero to interview-ready. The key is consistent practice, not cramming. Curious about the overall difficulty? Read our honest assessment of how hard SQL is to learn.
Master SELECT, WHERE, ORDER BY, LIMIT. Learn AND, OR, IN, BETWEEN, LIKE. Write 20-30 basic queries until the syntax is automatic.
GROUP BY with COUNT, SUM, AVG, MIN, MAX. Understand HAVING for filtering groups. Calculate metrics by segment.
INNER JOIN, LEFT JOIN, RIGHT JOIN. Subqueries in WHERE and FROM clauses. Practice with multi-table databases.
Window functions (ROW_NUMBER, RANK, LAG), CTEs, CASE WHEN. Solve interview-style problems daily.
8-Week SQL Learning Roadmap for Data Analysts
30-45 minutes daily beats 4 hours on weekends. Consistency builds muscle memory. Aim for 2-3 practice problems per day during the learning phase, then 1 problem daily for maintenance.
Ready to Put These SQL Skills Into Practice?
SQLNoir's detective cases progress from beginner SELECT queries to advanced multi-table JOINs and complex analysis. The same SQL skills you need for dashboards and reports, applied to solving crimes.
Start Your Investigation →With focused daily practice, you can be job-ready in 6-8 weeks. Basic competency (SELECT, WHERE, GROUP BY) comes in 2-3 weeks. Interview-level proficiency with window functions and CTEs takes the full 8 weeks. See the roadmap above for a week-by-week breakdown.
SQL has one of the gentlest learning curves in tech. The basics are intuitive and read almost like English. Advanced topics like window functions take more practice, but they are learnable by anyone who can think logically. For a deeper look, see our honest guide to SQL difficulty.
Start with PostgreSQL or MySQL. They are the most common and standards-compliant. The core concepts (SELECT, JOIN, GROUP BY) are identical across all dialects. Differences are minor syntax variations you will pick up quickly on the job.
SQL alone can land you an entry-level data analyst role. Combining SQL with Python (for advanced analysis, visualization, and automation) makes you significantly more marketable and opens doors to senior positions. Learn SQL first, then add Python.
AI tools can help write SQL faster, but they do not replace understanding. You still need to verify output, debug errors, optimize slow queries, and know when the AI got it wrong. Think of it like calculators: they did not replace the need to understand math. AI will not replace the need to understand SQL.
The job market rewards data analysts who can write clean, efficient queries. That skill comes from practice, not reading. Pick a resource from this guide, write your first query, and build from there. In eight weeks, you will be solving business problems that seem impossible today.