SQL for Data Analysis: The Queries Analysts Actually Use, with Examples

This guide is for students, freshers and Excel users moving into data analyst roles. Every query below runs on one example database, so you can type them into MySQL or PostgreSQL and check the results yourself. Where the two databases differ, the difference is noted.

The example database

Imagine an online store. Four tables hold everything we need:

Knowing what one row means in each table is the most important habit in SQL. Most wrong answers come from forgetting it, as you will see in the joins section.

1. Filter and sort: SELECT, WHERE, ORDER BY

Question: Which orders from Bangalore customers were cancelled in October 2026?

SELECT o.order_id, o.order_date, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.city = 'Bangalore'
  AND o.status = 'cancelled'
  AND o.order_date >= '2026-10-01'
  AND o.order_date <  '2026-11-01'
ORDER BY o.order_date;

Use a half-open date range (>= the first day, < the next month’s first day). It works whether order_date is a date or a date-time, unlike BETWEEN '2026-10-01' AND '2026-10-31', which misses orders placed during the day on 31 October if the column stores time.

2. Summarise: GROUP BY and HAVING

Question: What was delivered revenue by product category, showing only categories above ₹1,00,000?

SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS revenue,
       COUNT(DISTINCT o.order_id)       AS orders
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
WHERE o.status = 'delivered'
GROUP BY p.category
HAVING SUM(oi.quantity * oi.unit_price) > 100000
ORDER BY revenue DESC;

WHERE filters rows before grouping; HAVING filters groups after. Note COUNT(DISTINCT o.order_id): because the query joins to order_items, one order appears once per product line, so a plain COUNT(*) would count lines, not orders.

3. Combine tables: INNER JOIN vs LEFT JOIN

Question: Which customers signed up but have never placed an order?

SELECT c.customer_id, c.name, c.signup_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

An INNER JOIN keeps only rows that match in both tables, so it would silently drop these customers. A LEFT JOIN keeps every customer and fills order columns with NULL where there is no match, which is what this question needs.

The join trap: joining orders to order_items multiplies rows. If you then sum an order-level column (such as a delivery fee stored on orders), every fee is counted once per product line. Aggregate at the right level first, or join to a pre-summarised table.

4. Create categories: CASE WHEN

Question: How many orders fall into small, medium and large value bands?

WITH order_value AS (
  SELECT order_id, SUM(quantity * unit_price) AS value
  FROM order_items
  GROUP BY order_id
)
SELECT CASE
         WHEN value < 1000 THEN 'Small (under ₹1,000)'
         WHEN value < 5000 THEN 'Medium (₹1,000–4,999)'
         ELSE 'Large (₹5,000+)'
       END AS band,
       COUNT(*) AS orders
FROM order_value
GROUP BY 1
ORDER BY MIN(value);

This query also introduces a common table expression (the WITH block): a named, temporary result you can query like a table (PostgreSQL: WITH queries). CTEs make multi-step analysis readable, and interviewers notice when you use them.

5. Work with dates: monthly trends

Question: What was delivered revenue each month in 2026?

-- PostgreSQL
SELECT DATE_TRUNC('month', o.order_date) AS month,
       SUM(oi.quantity * oi.unit_price)  AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'delivered'
  AND o.order_date >= '2026-01-01' AND o.order_date < '2027-01-01'
GROUP BY 1
ORDER BY 1;
-- MySQL: replace the first line with
-- SELECT DATE_FORMAT(o.order_date, '%Y-%m-01') AS month,

Date functions are where MySQL and PostgreSQL differ most. Learn the one your target employers use, and know that the other exists.

6. Window functions: rank, running totals and growth

Window functions calculate across a set of rows related to the current row without collapsing them into one row the way GROUP BY does (PostgreSQL: Window functions). MySQL supports them from version 8.0.

Question: What are the top three products by revenue in each category?

WITH product_revenue AS (
  SELECT p.category, p.product_name,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.category, p.product_name
),
ranked AS (
  SELECT *, RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM product_revenue
)
SELECT category, product_name, revenue
FROM ranked
WHERE rnk <= 3;

Question: How did revenue change month over month?

WITH monthly AS (
  SELECT DATE_TRUNC('month', o.order_date) AS month,
         SUM(oi.quantity * oi.unit_price)  AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'delivered'
  GROUP BY 1
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly
ORDER BY month;

Multiply by 100.0, not 100: in some databases dividing two whole numbers gives a whole number, so a 4.7% growth would show as 4 or 0.

Practise in this order

1. One table. SELECT, WHERE, ORDER BY, LIMIT. Ready when you can filter by dates and text patterns without looking anything up.
2. Aggregation. GROUP BY, HAVING, COUNT DISTINCT. Ready when you can answer “revenue by category” correctly first time.
3. Joins. INNER and LEFT, and checking row counts after every join. Ready when you can explain why a join changed the number of rows.
4. CASE and CTEs. Ready when you can break a three-step question into named steps.
5. Window functions. Ready when you can write top-N-per-group and month-over-month growth from memory.
6. A portfolio case study. Ten business questions on one public dataset, with each query and its result. This is the project to show in interviews.

Common mistakes analysts make in SQL

• Counting lines instead of orders after a join. Use COUNT(DISTINCT ...) when you mean unique things.
• Using = NULL. Nothing equals NULL; use IS NULL or IS NOT NULL.
• Filtering a LEFT JOIN’s right table in WHERE. WHERE o.status = 'delivered' after a LEFT JOIN turns it back into an inner join. Put that condition in the ON clause if you want to keep unmatched rows.
• BETWEEN on date-times, which misses the last day. Use a half-open range.
• Not checking results. Compare your total with a simple known figure before presenting any number.

Learning SQL at ASTI

SQL takes two full modules in ASTI’s Data Analytics course: Module 4 covers queries, joins, subqueries and aggregation, and Module 5 covers multiple tables, transformation, large datasets and time-based analysis. The course also covers statistics, Excel, Tableau, Power BI and Python, over about four months in the classroom at BTM 1st Stage, Bangalore, or live online, with placement and career support. Placement assistance does not guarantee a job. See the full data analytics syllabus.

Next, prepare with data analyst interview questions, or go back to how to become a data analyst.

Frequently asked questions

How much SQL does a data analyst need?

Enough to answer business questions on several joined tables without help: filtering, grouping, joins, CASE, date functions, and for many roles, CTEs and window functions. Database administration and stored procedures are rarely needed.

Should I learn MySQL or PostgreSQL?

Either. Most analysis SQL is the same in both; the main differences are in date and text functions. Check which one appears more in the job posts you are targeting.

How long does it take to learn SQL for data analysis?

With daily practice, most learners are comfortable with the basics in a few weeks. Window functions and writing clean multi-step queries take longer, and come mainly from working through real questions.

Is SQL harder than Excel?

It feels different rather than harder. Excel shows you the data as you work; SQL asks you to describe the result you want. Once you think in terms of “what does one row mean”, SQL becomes faster than Excel for large data.

Do Power BI and Tableau replace SQL?

No. Dashboard tools are where results are shown; SQL is often how the data gets prepared for them. Most analyst interviews include a SQL round.

Sources

• Window functions, PostgreSQL documentation
• WITH queries (common table expressions), PostgreSQL documentation
• Window functions, MySQL 8.4 reference manual
• Course details: the Data Analytics course page, checked 3 Nov 2026