SQL for Data Analysis: From First Query to Window Functions
Write SQL queries that filter, group, join and rank data, avoid the classic traps (NULLs, duplicate joins, wrong totals) and check your numbers before you share them.
What you will learn
- Describe tables, rows, columns and keys, and write a basic SELECT that returns the columns you need.
- Filter and sort rows correctly with WHERE, IN, BETWEEN, LIKE, IS NULL and ORDER BY.
- Summarise data with aggregate functions, GROUP BY and HAVING, and handle NULLs in totals.
- Combine tables with INNER and LEFT joins and detect joins that drop or duplicate rows.
- Structure multi-step logic with subqueries, CTEs, CASE expressions and set operators.
- Rank, compare and accumulate values with window functions such as ROW_NUMBER, RANK, LAG and running SUM.
- Check data quality, dates and results before sharing, and query safely and responsibly.
Modules
- Tables, keys and your first SELECT
- Filtering and sorting rows
- Aggregating: COUNT, SUM, AVG, GROUP BY and HAVING
- Joining tables without losing or doubling rows
- Subqueries, CTEs, CASE and set operators
- Window functions: ranking, running totals and comparisons
- Data quality, checks and safe querying
Course facts
- Level
- Awareness
- Estimated study time
- about 45 minutes
- Final assessment
- 25 questions · Pass mark 80%
- Available in
- English, Français, العربية
- Price
- Free. Optional verifiable certificate: $5
- Last updated
- Published by
- AMAADOR ACADEMY
Who this course is for
Analysts, reporting and finance staff, marketers, engineers and students who work with spreadsheets and want to query databases directly, and anyone preparing for data certifications that assume SQL.
Prerequisites
None. Comfort with spreadsheets (rows, columns, filters) helps. Access to any SQL tool (PostgreSQL, SQLite, MySQL, SQL Server) is useful for practice but not required.
Course content
1. Tables, keys and your first SELECT
SQL (Structured Query Language) is the language used to ask questions of relational databases. The language is defined by the international standard ISO/IEC 9075, and every major product (PostgreSQL, SQL Server, MySQL, Oracle, SQLite, BigQuery, Snowflake) implements most of it plus its own extras. What you learn here works almost everywhere; the small differences are flagged.
A database stores data in tables. Each table describes one kind of thing: customers, orders, products. A row is one occurrence (one order). A column is one attribute with one data type (order date, amount). Unlike a spreadsheet, a column cannot mix text and numbers, and there are no merged cells or colour codes: the structure is strict, which is what makes analysis reliable.
The four building blocks you will use in every query.
A primary key is a column (or set of columns) whose value is unique and never empty for each row, for example order_id. A foreign key is a column that points to the primary key of another table, for example orders.customer_id pointing to customers.customer_id. Keys are how tables are linked, and they tell you the grain of a table: what one row represents.
2. Filtering and sorting rows
WHERE keeps only the rows for which a condition is true. It runs before grouping, so it decides which raw rows enter your analysis.
| Operator | Example | Watch out |
|---|---|---|
| = <> < > <= >= | amount >= 500 | Text needs quotes: country = 'MA' |
| IN | country IN ('MA','FR','AE') | Shorter and safer than many ORs |
| BETWEEN | amount BETWEEN 100 AND 200 | Both ends are included |
| LIKE | email LIKE '%@gmail.com' | % = any characters, _ = exactly one |
| IS NULL | phone IS NULL | Never write = NULL |
AND is evaluated before OR. The filter WHERE country = 'MA' OR country = 'FR' AND amount > 1000 means “all Moroccan orders, plus French orders above 1000”, which is rarely what was meant. Add brackets: WHERE (country = 'MA' OR country = 'FR') AND amount > 1000, or better, use IN.
Case sensitivity depends on the product. In PostgreSQL, LIKE is case-sensitive (ILIKE ignores case). In SQL Server and MySQL it usually depends on the column collation, which is often case-insensitive. If you are unsure, compare LOWER(email) with a lower-case pattern.
3. Aggregating: COUNT, SUM, AVG, GROUP BY and HAVING
Aggregate functions turn many rows into one number. The five you will use daily are COUNT, SUM, AVG, MIN and MAX. Their behaviour with NULL is where most reporting errors hide.
| Function | What it returns | With NULLs |
|---|---|---|
| COUNT(*) | number of rows | counts every row |
| COUNT(col) | number of rows where col is not NULL | skips NULLs |
| COUNT(DISTINCT col) | number of different non-NULL values | skips NULLs |
| SUM(col) | total | ignores NULLs; NULL if no rows |
| AVG(col) | mean of non-NULL values | NULLs are not counted as 0 |
| MIN / MAX | lowest / highest | ignore NULLs |
The PostgreSQL documentation is explicit: count(*) computes the number of input rows, while count(expression) counts only rows where the value is not null. It also warns that, except for count, these functions return NULL when no rows are selected: a SUM of no rows is NULL, not zero. Use COALESCE(SUM(amount), 0) when your report must show 0.
The AVG trap: if 4 of 10 sales reps have a NULL target, AVG(target) is the average of 6 people, not 10. That may be correct (unknown targets excluded) or wrong (NULL really meant 0). Decide, then write it: AVG(COALESCE(target, 0)) treats missing as zero.
4. Joining tables without losing or doubling rows
A join combines rows of two tables that match on a condition, usually a foreign key equal to a primary key: FROM orders AS o JOIN customers AS c ON o.customer_id = c.customer_id.
| Join | Keeps | Typical use |
|---|---|---|
| INNER JOIN (or JOIN) | only rows that match on both sides | orders with their customer details |
| LEFT JOIN | every row of the left table; NULLs where the right side has no match | all customers, with or without orders |
| RIGHT JOIN | the mirror of LEFT | rarely needed; swap the tables and use LEFT |
| FULL OUTER JOIN | every row of both tables | reconciling two lists |
| CROSS JOIN | every combination | calendars, scenario grids; dangerous by accident |
Choosing INNER or LEFT is a business decision. “Revenue by customer segment” with an INNER JOIN drops orders whose customer_id is missing from the customer table, and the total falls without warning. A LEFT JOIN from orders keeps them, with a NULL segment that you can label “unknown”.
Finding what is missing (an anti-join): customers who never ordered.
- Start from the table you want to keep — FROM customers AS c
- LEFT JOIN the other table — LEFT JOIN orders AS o ON o.customer_id = c.customer_id
- Keep the non-matches — WHERE o.order_id IS NULL
5. Subqueries, CTEs, CASE and set operators
Real questions take several steps: “customers whose first order was in 2026 and who ordered again within 30 days”. SQL lets you build the answer one step at a time.
A subquery is a query inside another. It can return one value (WHERE amount > (SELECT AVG(amount) FROM orders)), a list (WHERE customer_id IN (SELECT customer_id FROM complaints)) or a whole table used in FROM.
A common table expression (CTE) names each step with WITH, so the query reads top to bottom:
- WITH first_orders AS (…) — one row per customer with MIN(order_date) AS first_date
- , repeat AS (…) — join orders to first_orders and keep orders placed after first_date and within 30 days
- SELECT … — count customers in repeat and divide by customers in first_orders
CTEs do not make a query faster by themselves; they make it readable and checkable. You can run each step on its own, look at the row count and catch an error early. Most modern databases support WITH (PostgreSQL, SQL Server, MySQL 8 and later, SQLite, BigQuery, Snowflake).
6. Window functions: ranking, running totals and comparisons
A window function computes a value across a set of rows related to the current row, without collapsing them. The PostgreSQL tutorial puts it simply: unlike GROUP BY, the rows keep their separate identities. You get the detail and the summary side by side.
SELECT order_id, country, amount, SUM(amount) OVER (PARTITION BY country) AS country_total FROM orders; returns every order with its country total next to it, which makes “share of country” a simple division.
- OVER ( ) turns a function into a window function. Empty brackets mean the whole result.
- PARTITION BY restarts the calculation for each group (like GROUP BY, but rows are kept).
- ORDER BY inside OVER sets the order used for ranking and running calculations.
| Amounts | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 900 | 1 | 1 | 1 |
| 700 | 2 | 2 | 2 |
| 700 | 3 | 2 | 2 |
| 500 | 4 | 4 | 3 |
ROW_NUMBER always gives distinct numbers (ties are split arbitrarily unless you add a tie-breaker). RANK gives ties the same rank and leaves a gap. DENSE_RANK gives ties the same rank with no gap.
7. Data quality, checks and safe querying
Most wrong numbers come from the data, not the syntax. Before analysing a column, profile it: row count, NULL count, distinct values, minimum and maximum.
| Problem | Symptom | Fix |
|---|---|---|
| Integer division | In PostgreSQL and SQL Server, 7 / 2 returns 3 and a conversion rate shows 0 | Make one side decimal: 7 * 1.0 / 2, or CAST(x AS NUMERIC) |
| Division by zero | Query fails when a group has no visits | x / NULLIF(visits, 0) returns NULL instead of failing |
| Numbers stored as text | '100' sorts before '9'; SUM refuses or casts oddly | CAST to a numeric type once, in a CTE |
| Dates stored as text | '03/04/2026': March 4 or 3 April? | Convert with an explicit format; store ISO dates (YYYY-MM-DD) |
| Time zones | Orders near midnight land on the wrong day | Know whether timestamps are UTC; convert before grouping by day |
| Dirty text | 'Casablanca', 'casablanca ', 'CASABLANCA' are three cities | TRIM and LOWER (or UPPER) before grouping; fix at the source |
Duplicates. The same customer loaded twice, or an event sent twice by an app, inflates counts. Check the key you believe is unique: SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVING COUNT(*) > 1. To keep one copy per key, number the rows with ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) and keep rn = 1, choosing the rule (latest load, first load) on purpose.
The full lesson continues in the interactive course on this page.
Sources and references
- PostgreSQL documentation: Sorting rows (ORDER BY)
- PostgreSQL documentation: Aggregate functions
- PostgreSQL tutorial: Window functions
- SELECT (Transact-SQL): logical processing order
- PostgreSQL tutorial: Joins between tables
- PHE statement on delayed reporting of COVID-19 cases (October 2020)
- Herndon, Ash and Pollin (2013): Does high public debt consistently stifle economic growth?
Educational course. Examples use standard SQL and PostgreSQL behaviour; other databases (SQL Server, MySQL, Oracle, BigQuery, Snowflake) differ in some syntax and defaults, so check your product’s documentation. Not affiliated with any database vendor. Practise on copies or read-only accounts, never on production data you are not authorised to change.
All courses · Data Engineering & Analytics — Free certified training | AMAADOR ACADEMY