SQL for Data Analysis
Beginner notes on filtering, grouping, joining, and checking relational data
These original notes are designed for study and skill development. They are not certificates, official records, or
copied course materials.
1. Query structure and filtering
SQL retrieves and transforms data stored in relational tables. A readable query usually selects only necessary
columns, uses clear aliases, and separates filtering, grouping, and ordering logically.
Basic query
SELECT order_id, order_date, customer_id, sales_amount
FROM orders
WHERE order_date >= '2026-01-01'
AND sales_amount > 0
ORDER BY order_date;
Logical processing idea
Clause Purpose
FROM / JOIN Choose and combine source tables
WHERE Filter individual rows before aggregation
GROUP BY Create groups for summary calculations
HAVING Filter groups after aggregation
SELECT Return chosen columns and expressions
ORDER BY Sort the final result
LIMIT Restrict the number of returned rows where supported
Useful conditions
- IN (...) checks membership in a list.
- BETWEEN checks an inclusive range.
- LIKE matches a text pattern.
- IS NULL identifies missing values.
- Parentheses make combinations of AND and OR unambiguous.
SQL for Data Analysis Page 1
2. Aggregation and joins
Summarising rows
SELECT product_category,
COUNT(*) AS order_lines,
SUM(sales_amount) AS total_sales,
AVG(sales_amount) AS average_sale
FROM order_lines
GROUP BY product_category
HAVING SUM(sales_amount) > 10000
ORDER BY total_sales DESC;
Common aggregate functions
Function Meaning Common caution
COUNT(*) Counts rows Includes rows containing NULL values
COUNT(column) Counts non-NULL values May be lower than row count
SUM(column) Adds numeric values Duplicates can inflate totals
AVG(column) Mean of non-NULL values Missing values are excluded
MIN / MAX Smallest / largest value Check whether the type is correct
Join types
- INNER JOIN: returns matching rows from both tables.
- LEFT JOIN: keeps every row from the left table and adds matches when available.
- FULL OUTER JOIN: keeps unmatched rows from both sides where supported.
- CROSS JOIN: creates every combination and should be used deliberately.
Join risk: If one customer has many orders, joining customers to orders creates multiple rows per customer.
Always know the expected grain before and after a join.
SQL for Data Analysis Page 2
3. Analytical patterns and validation
Conditional calculation
SELECT customer_id,
SUM(CASE WHEN status = 'Completed' THEN sales_amount ELSE 0 END) AS completed_sales,
SUM(CASE WHEN status = 'Returned' THEN sales_amount ELSE 0 END) AS returned_sales
FROM orders
GROUP BY customer_id;
Window-function concept
Window functions calculate across related rows while keeping the original row detail. They are useful for ranking,
running totals, moving averages, and comparisons with previous periods.
SELECT order_date, sales_amount,
SUM(sales_amount) OVER (ORDER BY order_date) AS running_sales
FROM daily_sales;
Validation checklist
- Confirm the expected row count before and after joins.
- Check whether join keys are unique on either side.
- Compare a small sample with the source system.
- Test NULL behaviour explicitly.
- Reconcile grouped totals with an overall total.
- Use clear aliases and comments for complex logic.
Practice questions
- Write a query returning monthly sales by region.
- Find customers with no orders using a LEFT JOIN and NULL check.
- Calculate return rate as returned orders divided by all orders.
- Rank products by sales within each category.
- Explain how duplicate product IDs could change a sales total.
Good SQL analysis is auditable: another analyst should be able to read the query, understand the grain, and
reproduce the result.
SQL for Data Analysis Page 3