SQL for Data Analysis: 15 Queries Every Analyst Should Know

SQL for data analysis comes down to a small set of query patterns used over and over: filtering rows, aggregating with GROUP BY, joining tables, bucketing with CASE, and ranking or running totals with window functions. The fifteen queries below cover the questions analysts get asked most often, such as top products, monthly trends, repeat customers and month-over-month growth. Learn these patterns well and most new questions become a variation of one you already know.

I'm Gulab Chand Tejwani. I teach SQL and data analysis at TechNet Consultancy in Vallabh Vidyanagar, Anand, and online, and I'm a Microsoft Certified: Power BI Data Analyst Associate and Microsoft Certified Trainer. These are close to the exercises I give in class. Each one starts with the business question, because that's how the work arrives.

The sample tables

All queries use a small online store with four tables:

• customers (customer_id, name, city, signup_date)
• products (product_id, product_name, category, price)
• orders (order_id, customer_id, order_date, status)
• order_items (order_id, product_id, quantity, unit_price)

The syntax is standard SQL and runs on PostgreSQL. I've noted where MySQL or SQL Server differ. Both PostgreSQL and MySQL are free to install, so you can type these in yourself, which is the only way they stick.

1. Which orders were placed last month?

SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date >= DATE '2026-10-01'
  AND order_date <  DATE '2026-11-01'
ORDER BY order_date;

Use a half-open range (>= start, < next start) rather than BETWEEN. If order_date ever becomes a timestamp, BETWEEN '2026-10-01' AND '2026-10-31' silently drops everything after midnight on the 31st.

2. How many customers do we have in each city?

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC;

This is the SQL version of a PivotTable. Every column in the SELECT is either in the GROUP BY or inside an aggregate.

3. What is total revenue?

SELECT SUM(quantity * unit_price) AS revenue
FROM order_items;

Notice that revenue comes from order_items, not products.price. Prices change. The price on the order line is what the customer paid.

4. Which ten products earned the most revenue?

SELECT 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.product_name
ORDER BY revenue DESC
LIMIT 10;

In SQL Server, use SELECT TOP 10 instead of LIMIT 10.

5. What does revenue look like by month?

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 = 'completed'
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY month;

DATE_TRUNC is PostgreSQL. In MySQL, use DATE_FORMAT(o.order_date, '%Y-%m-01'). In SQL Server 2022, DATETRUNC(month, o.order_date).

Also notice the status filter. Ask early which orders count. Cancelled and refunded orders inflate revenue and somebody will notice in a meeting.

6. Which categories have more than ₹5 lakh in sales?

SELECT p.category,
       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
HAVING SUM(oi.quantity * oi.unit_price) > 500000;

WHERE filters rows before grouping. HAVING filters groups after. This difference is one of the most common fresher interview questions.

7. Which customers have never placed an order?

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

The LEFT JOIN keeps every customer, and the rows with no match have NULL in the order columns. Filtering for that NULL finds the gap. It is called an anti-join, and it is one of the most useful patterns in analysis.

8. What is the average order value?

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS order_value
  FROM order_items
  GROUP BY order_id
)
SELECT AVG(order_value) AS avg_order_value
FROM order_totals;

You can't just average unit_price. First total each order, then average the totals. The WITH clause (a CTE) makes the two steps readable.

9. How do we label customers as high, medium or low value?

WITH customer_spend AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS spend
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  GROUP BY o.customer_id
)
SELECT customer_id, spend,
       CASE
         WHEN spend >= 50000 THEN 'High'
         WHEN spend >= 10000 THEN 'Medium'
         ELSE 'Low'
       END AS segment
FROM customer_spend;

CASE is evaluated top to bottom, and the first match wins. Put the highest threshold first.

10. How many customers ordered more than once?

SELECT COUNT(*) AS repeat_customers
FROM (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  HAVING COUNT(*) > 1
) AS t;

Repeat rate is one of the first numbers a business owner asks for. Divide this by the count of customers who ordered at least once.

11. What are the top three products 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 *,
         ROW_NUMBER() OVER (PARTITION BY category
                            ORDER BY revenue DESC) AS rn
  FROM product_revenue
)
SELECT category, product_name, revenue
FROM ranked
WHERE rn <= 3;

"Top N per group" is the classic window function question. PARTITION BY restarts the numbering for each category. Use RANK() or DENSE_RANK() instead if ties should share a position.

	GROUP BY	Window function (OVER)
Rows returned	One per group	Every input row
Typical use	Totals per city or month	Rank, running total, previous value
Can mix detail and totals	No, without a join back	Yes, in the same row
Filter on the result	HAVING	Wrap in a CTE, then WHERE
Example	SUM(amount) ... GROUP BY city	SUM(amount) OVER (PARTITION BY city)

12. What is the running total of revenue through the year?

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
  GROUP BY 1
)
SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly
ORDER BY month;

A window function adds a calculated column without collapsing rows, which is exactly what you want for running totals.

13. What is month-over-month growth?

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
  GROUP BY 1
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;

LAG looks at the previous row. NULLIF(..., 0) prevents a divide-by-zero error in the first month or any month with no sales. Multiplying by 100.0 rather than 100 avoids integer division in some databases.

14. When did each customer first order, and how many came back?

WITH first_orders AS (
  SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id
)
SELECT DATE_TRUNC('month', f.first_order_date) AS cohort_month,
       COUNT(DISTINCT f.customer_id) AS new_customers,
       COUNT(DISTINCT CASE WHEN o.order_date > f.first_order_date
                           THEN o.customer_id END) AS returned
FROM first_orders f
JOIN orders o ON o.customer_id = f.customer_id
GROUP BY 1
ORDER BY 1;

This is a simple cohort view. It answers "of the people we got in March, how many came back?", which is a better health check than total customers.

15. Are there duplicate rows in the data?

SELECT customer_id, order_date, COUNT(*) AS copies
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1;

Run checks like this before you trust any number. Duplicates from a bad import are common, and they make every total wrong in a way that looks believable.

How do I turn a business question into a query?

This is the skill that matters more than syntax, and it's the one I spend most class time on. Take a question a manager might actually send: "Which city's customers spend the most per order, and has that changed this year?"

1. Find the grain. "Per order" means I need one row per order first, so I'll total order_items by order_id.
2. Find the dimensions. City comes from customers, so I need orders joined to customers.
3. Find the time window. "This year" versus last year means I group by year as well as city.
4. Write it in steps with CTEs. One CTE for order totals, one join to add city and year, then the final GROUP BY.
5. Sanity-check. Does the number of orders match a simple COUNT on the orders table for the same period? If not, a join is duplicating or dropping rows.

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS order_value
  FROM order_items
  GROUP BY order_id
)
SELECT c.city,
       EXTRACT(YEAR FROM o.order_date) AS yr,
       COUNT(*)                        AS orders,
       ROUND(AVG(t.order_value), 0)    AS avg_order_value
FROM order_totals t
JOIN orders o    ON o.order_id = t.order_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.city, EXTRACT(YEAR FROM o.order_date)
ORDER BY c.city, yr;

EXTRACT(YEAR FROM ...) works in PostgreSQL and MySQL. In SQL Server use YEAR(o.order_date).

How should I practise these queries?

Type them out, don't copy them. Then change one thing each time: a different date range, a different grouping, a RANK instead of ROW_NUMBER. Ask yourself what the result should look like before you run it, and check that it does.

Good free practice databases include the PostgreSQL sample "dvdrental" database, Microsoft's AdventureWorks for SQL Server, and the Sakila sample for MySQL. For interview-style practice, the SQL interview preparation page has more.

SQL is stage two of the data analyst roadmap, right after Excel. If you'd like to work through these with feedback, the SQL training runs in our Vallabh Vidyanagar classroom and live online, and it's also part of the full data analysis course.

FAQ

Which SQL database should I learn for data analysis?

PostgreSQL or MySQL are both free and widely used, and SQL Server is common in companies that run Microsoft tools. The core syntax (SELECT, JOIN, GROUP BY, window functions) is almost the same, so learn one well and the others take days, not months.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before they are grouped. HAVING filters the groups after GROUP BY has run, so it can use aggregates like SUM or COUNT. You can use both in the same query.

Are window functions necessary for a data analyst?

Yes, for most analyst roles beyond the most junior. ROW_NUMBER, RANK, LAG and running SUMs come up in ranking, growth and cohort questions, and interviewers test them regularly.

How long does it take to learn SQL for analysis?

With regular practice of 8 to 10 hours a week, most learners can write the fifteen queries above confidently in five to seven weeks. Getting fast at turning business questions into queries takes ongoing practice.

Can I use SQL with Power BI and Excel?

Yes. Power BI and Excel's Power Query can both connect to SQL databases and run a query to pull exactly the data you need. Writing a good query first often makes your Power BI model simpler and faster.