SQL is the language data analysts use to ask a database a question. If you can write a query that filters, groups and joins tables, you can answer most business questions yourself instead of waiting for someone to send you a file. This guide takes you from the first five clauses to joins, grouping, CASE WHEN, NULL handling, dates, CTEs, window functions and indexes, and finishes with a reconciliation project and common interview questions.
The examples use simple tables such as orders and customers. Syntax is standard SQL, with notes where MySQL and PostgreSQL differ.
Contents
- 1. SQL basics: the five clauses to learn first
- 2. JOINs: INNER, LEFT, RIGHT and FULL
- 3. GROUP BY and HAVING
- 4. CASE WHEN: turning values into categories
- 5. NULL vs zero: why your averages change
- 6. Date functions for analysts
- 7. Subqueries vs CTEs
- 8. Window functions: ROW_NUMBER, RANK and LAG
- 9. Indexes and query speed
- 10. Using SQL from Python
- 11. Practice project: reconcile orders, refunds and net revenue
- 12. SQL and reporting interview questions
1. SQL basics: the five clauses to learn first
- SELECT: the columns you want back.
- FROM: the table to read.
- WHERE: the rows to keep, for example orders from the last 30 days.
- GROUP BY: the level to summarise at, such as month or region.
- ORDER BY: how to sort the result, usually biggest values first.
Suppose the orders table has one row per order, with an order date, a region and an amount. To find total sales per region for last month, filter the dates in WHERE, group by region and sum the amount:
SELECT region, SUM(amount) AS total_sales
FROM orders
WHERE order_date >= '2026-10-01' AND order_date < '2026-11-01'
GROUP BY region
ORDER BY total_sales DESC;The query reads almost like the question itself, which is why SQL is one of the easiest analyst skills to start with.
Common beginner mistakes
- Forgetting that NULL is not the same as zero, so totals look wrong (see section 5).
- Joining on a column that is not unique, which repeats rows and inflates totals.
- Filtering after grouping when the filter belongs in WHERE.
- Running a query on a huge table without LIMIT while you are still testing.
Practise with a free sample database, write one query a day and explain each one out loud. Once you can answer “how many customers ordered twice, by month?” without help, you are ready for a junior analyst interview.
2. JOINs: INNER, LEFT, RIGHT and FULL
Real data rarely lives in one table. Orders sit in one table, customers in another and products in a third, and a JOIN puts them together. Most beginner mistakes in reports come from choosing the wrong join.
Imagine a customers table (customer_id, name) and an orders table (order_id, customer_id, amount). Some customers have never ordered, and one order has a customer_id missing from the customers table, a data quality problem you will meet often.
INNER JOIN
SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;Returns only rows that match in both tables. Customers with no orders disappear, and so does the order with the unknown customer.
LEFT JOIN
Keeps every row from the left table (customers) and adds order details where they exist; customers with no orders show NULL for the amount. This is the join for questions like “which customers have never ordered?”: add WHERE o.order_id IS NULL. You can also write it with NOT EXISTS.
RIGHT JOIN
The mirror image, keeping every row from the right table. Most analysts rewrite it as a LEFT JOIN by swapping the table order, because it is easier to read.
FULL OUTER JOIN
Keeps all rows from both tables, matched where possible. It is useful for reconciliation, such as finding records that exist in one system but not the other. MySQL does not support FULL JOIN directly; combine a LEFT and a RIGHT join with UNION.
Three checks before you trust a join
- Count rows before and after. If the count grows unexpectedly, the join key is not unique and rows are being duplicated.
- Look for NULLs. Unexpected NULLs usually mean missing or mistyped keys.
- Check totals. Sum the amount before and after the join. If revenue changes, something is wrong.
3. GROUP BY and HAVING
GROUP BY combines rows that share a value, such as one row per city. HAVING then filters those groups, while WHERE filters individual rows before grouping. The order matters:
- WHERE removes rows you do not want.
- GROUP BY forms the groups.
- HAVING keeps only the groups that pass a test.
SELECT city, COUNT(*) AS orders
FROM orders
GROUP BY city
HAVING COUNT(*) > 5;Using WHERE for the same test fails, because WHERE cannot see the count. Remember too that COUNT(column) counts non-null values including repeats, while COUNT(DISTINCT column) counts unique values.
4. CASE WHEN: turning values into categories
CASE WHEN labels rows based on conditions, so you can turn raw amounts into bands such as small, medium and large without changing the source table.
SELECT order_id,
CASE WHEN amount < 1000 THEN 'Small'
WHEN amount < 5000 THEN 'Medium'
ELSE 'Large'
END AS order_size
FROM orders;Group by the CASE result to count each band. Two checks: conditions are tested in order, so put the most specific first; and always include ELSE, or unmatched values become NULL and drop out of your totals.
5. NULL vs zero: why your averages change
A missing value and a recorded zero answer different questions. Before building a dashboard, decide whether each number is an observation, an unknown value or something that does not apply. Take five fictional service requests, where response_minutes is the time to first response, 0 means an immediate response, and NULL means no duration was recorded:
WITH requests(id, response_minutes) AS (
VALUES (1, 10), (2, 20), (3, NULL), (4, 0), (5, 30)
)
SELECT COUNT(*) AS total_requests,
COUNT(response_minutes) AS recorded_durations,
AVG(response_minutes) AS average_recorded_minutes
FROM requests;The result is five requests, four recorded durations and an average of 15 minutes, because COUNT(*) counts rows while COUNT(column) and AVG(column) ignore NULLs. If you replace the NULL with zero, the same 60 minutes is divided by five and the average drops to 12. Both queries run, but the second silently assumes an immediate response that may never have happened.
Make the dashboard explain its denominator
- Label the metric “Average response time for recorded durations”.
- Show coverage alongside it: four of five requests have a duration (80%).
- Keep a separate count of missing durations for investigation.
- Ask the data owner what a missing value means before you fill it with
COALESCE, which returns the first non-null value in its list.
6. Date functions for analysts
“Sales this month”, “orders in the last 30 days”, “average days to deliver”: almost every business question involves dates. Examples below use MySQL, with PostgreSQL notes.
Extracting parts of a date
SELECT YEAR(order_date), MONTH(order_date), DAYNAME(order_date) FROM orders; In PostgreSQL use EXTRACT(YEAR FROM order_date) and TO_CHAR(order_date, 'Day'). Weekday analysis often reveals useful patterns, such as which days bring the most enquiries.
Grouping by month
Group by a month start date rather than a month number, so January 2025 and January 2026 do not merge:
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month, SUM(amount)
FROM orders
GROUP BY month
ORDER BY month;In PostgreSQL, use DATE_TRUNC('month', order_date).
Differences between dates
SELECT AVG(DATEDIFF(delivered_date, order_date)) AS avg_days_to_deliver FROM orders; In PostgreSQL, subtracting two dates gives the number of days directly.
Filtering date ranges correctly
For the last 30 days: WHERE order_date >= CURDATE() - INTERVAL 30 DAY. For a whole month, use a half-open range: WHERE order_date >= '2026-11-01' AND order_date < '2026-12-01'. This avoids missing orders from the last day when the column also stores a time, a common bug with BETWEEN. Avoid writing WHERE YEAR(order_date) = 2026, because wrapping the column in a function can stop the database using an index (see section 9).
Practice: monthly revenue for the last 12 months, orders per weekday, and customers whose last order was more than 90 days ago.
7. Subqueries vs CTEs
Many questions need more than one step, such as “find customers whose total spend is above the average”. SQL offers two ways to break a problem into steps.
Subqueries
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (SELECT AVG(amount) FROM orders);The inner query runs first and returns one number for the outer query to use. Subqueries suit short, single-step logic.
CTEs (common table expressions)
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total > (SELECT AVG(total) FROM customer_totals);The query now reads top to bottom like a recipe: first build customer totals, then filter them.
When to use which
- Use a subquery for a quick one-off value, such as a single average in a WHERE clause.
- Use a CTE when the query has several steps, reuses the same intermediate result, or will be read by someone else.
- Recursive CTEs can walk hierarchies, such as an employee and manager tree.
In most modern databases the two perform similarly, because the optimiser rewrites both. Choose for readability first, then check the query plan if something is slow.
8. Window functions: ROW_NUMBER, RANK and LAG
A window function calculates a value across related rows without collapsing them into one row the way GROUP BY does. That makes it ideal for rankings, running totals and comparing a row with the previous one.
- ROW_NUMBER(): numbers rows within each group, for example each customer’s orders by date. Every row gets a different number.
- RANK(): ranks rows; tied rows share a rank and the next rank is skipped.
- LAG(): returns a value from the previous row, useful for month-on-month change.
Each one uses OVER (PARTITION BY … ORDER BY …): the partition sets the group and the order sets the sequence inside it.
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS previous_month
FROM monthly;Practice task: extend this query to keep only months where revenue grew. Always run window functions on a small table first and check the first few rows by hand.
9. Indexes and query speed
A query on a table with a million rows can take seconds or milliseconds depending on whether the database can find rows through an index. Without one it reads every row, a full table scan, like searching a book page by page. An index works like the index at the back of the book.
CREATE INDEX idx_orders_customer ON orders(customer_id);When an index helps
- Columns used often in WHERE conditions, such as customer_id or order_date.
- Columns used in JOIN conditions, such as foreign keys.
- Columns used for ORDER BY on large results.
When it does not
- Small tables, where scanning is already fast.
- Columns with very few distinct values, such as a yes/no flag.
- Tables with heavy writes, because every insert and update must also update the index.
Patterns that stop an index being used
- Wrapping the column in a function, such as
WHERE YEAR(order_date) = 2026. Write a date range instead. - A leading wildcard, such as
LIKE '%sharma'. - Implicit type conversion, such as comparing a text column with a number.
Put EXPLAIN in front of a query to see whether the database plans to use an index (in PostgreSQL, EXPLAIN ANALYZE shows real timings). For queries filtering on two columns, one composite index on both often beats two separate ones; put the column filtered with equality first.
10. Using SQL from Python
Most applications and many analysis scripts read data from a database through Python. The steps are always the same: open a connection, create a cursor, run a query with parameters, fetch the results or commit the changes, and close the connection.
import sqlite3
conn = sqlite3.connect("shop.db")
cur = conn.cursor()
cur.execute("SELECT name FROM customers WHERE city = ?", ("Bangalore",))
rows = cur.fetchall()
conn.close()Always pass values as parameters, as above, and never build SQL by joining strings with user input; this prevents SQL injection, one of the most common security problems. SQLite is built into Python and needs no server, so it is ideal for practice, and the same ideas carry over to MySQL and PostgreSQL. Frameworks such as Django use an ORM that maps Python classes to tables; learn plain SQL first so you understand what the ORM does for you.
11. Practice project: reconcile orders, refunds and net revenue
This exercise uses SQL and Power BI to report net revenue without double-counting orders. Start with three orders and two refunds:
- Order 101: gross ₹1,200, refunds of ₹200 and ₹100, net ₹900.
- Order 102: gross ₹800, no refunds, net ₹800.
- Order 103: gross ₹500, no refunds, net ₹500.
The expected totals are ₹2,500 gross, ₹300 refunded and ₹2,200 net. Store gross amounts in an orders table (one row per order) and each refund in a separate returns table.
Group refunds before joining
WITH returns_by_order AS (
SELECT order_id, SUM(refund_amount) AS refund_total
FROM returns
GROUP BY order_id
)
SELECT o.order_id, o.gross_amount,
COALESCE(r.refund_total, 0) AS refund_total,
o.gross_amount - COALESCE(r.refund_total, 0) AS net_revenue
FROM orders AS o
LEFT JOIN returns_by_order AS r ON r.order_id = o.order_id;Joining directly to the two refund rows would repeat order 101 and double its gross amount. Grouping first keeps one row per order, and the LEFT JOIN keeps orders without refunds.
Find refunds without a matching order
SELECT r.order_id, COUNT(*) AS refund_rows,
SUM(r.refund_amount) AS unmatched_refund_total
FROM returns AS r
LEFT JOIN orders AS o ON o.order_id = r.order_id
WHERE o.order_id IS NULL
GROUP BY r.order_id;Add a test refund of ₹50 for an order 999 that does not exist. The exception query should return one row; the matched net stays ₹2,200 while total refunds in the source rise to ₹350. Show that difference separately until you can explain it.
Check before you chart, and choose the right date
Count the result rows (expect three), compare gross and refund totals with the source tables, then in Power BI show gross, refunds and net side by side and filter to order 101 to confirm ₹1,200, ₹300 and ₹900. Use the refund date for a report about refunds processed in a period, and the original order date when comparing purchase cohorts. For your portfolio, include the sample data, the query, the expected totals and one screenshot.
12. SQL and reporting interview questions
- What is the difference between WHERE and HAVING? WHERE filters rows before grouping; HAVING filters grouped results.
- What does COALESCE do? It returns the first non-null expression, useful as a fallback when a missing value has a defined replacement.
- How do you find customers with no orders? Use a LEFT JOIN and check for a missing order key, or NOT EXISTS.
- What is the difference between RANK and ROW_NUMBER? ROW_NUMBER gives every row a different number; RANK gives ties the same rank and leaves gaps after them.
- Why is my percentage total wrong in a dashboard? Averaging row percentages gives the wrong answer when denominators differ. Divide the overall numerator by the overall denominator.
- How do you check that data loaded completely? Compare row counts, date ranges and control totals with the source, broken down by date or batch to locate gaps.
- What is a cohort? A group defined by a shared starting event, such as customers who first purchased in June.
- How do you show a month with incomplete data? Label it as partial, state the cutoff date, and compare it only with the equivalent portion of an earlier period.
- When should you use a bar chart instead of a pie chart? When readers need to compare similar values or many categories; a shared baseline makes differences easier to see.
For a complete beginner project, follow SQL to Power BI: a small sales dashboard project. If you are still building spreadsheet skills, start with our Excel for data analysts guide.
Learn SQL with ScholarLogic
SQL is taught with real datasets in the 5-month Data Analytics course in Bangalore at ScholarLogic, BTM Layout, alongside Excel, Power BI and Python. Book a free demo class and a counsellor will share the fee and the next batch dates. To book, call 78923 93639.
