Customize this training

Get a certificate — $15$5

SQL for Data Analysis: From First Query to Window Functions

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

  1. Tables, keys and your first SELECT
  2. Filtering and sorting rows
  3. Aggregating: COUNT, SUM, AVG, GROUP BY and HAVING
  4. Joining tables without losing or doubling rows
  5. Subqueries, CTEs, CASE and set operators
  6. Window functions: ranking, running totals and comparisons
  7. 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.

OperatorExampleWatch out
= <> < > <= >=amount >= 500Text needs quotes: country = 'MA'
INcountry IN ('MA','FR','AE')Shorter and safer than many ORs
BETWEENamount BETWEEN 100 AND 200Both ends are included
LIKEemail LIKE '%@gmail.com'% = any characters, _ = exactly one
IS NULLphone IS NULLNever 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.

FunctionWhat it returnsWith NULLs
COUNT(*)number of rowscounts every row
COUNT(col)number of rows where col is not NULLskips NULLs
COUNT(DISTINCT col)number of different non-NULL valuesskips NULLs
SUM(col)totalignores NULLs; NULL if no rows
AVG(col)mean of non-NULL valuesNULLs are not counted as 0
MIN / MAXlowest / highestignore 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.

JoinKeepsTypical use
INNER JOIN (or JOIN)only rows that match on both sidesorders with their customer details
LEFT JOINevery row of the left table; NULLs where the right side has no matchall customers, with or without orders
RIGHT JOINthe mirror of LEFTrarely needed; swap the tables and use LEFT
FULL OUTER JOINevery row of both tablesreconciling two lists
CROSS JOINevery combinationcalendars, 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.

  1. Start from the table you want to keep — FROM customers AS c
  2. LEFT JOIN the other table — LEFT JOIN orders AS o ON o.customer_id = c.customer_id
  3. 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:

  1. WITH first_orders AS (…) — one row per customer with MIN(order_date) AS first_date
  2. , repeat AS (…) — join orders to first_orders and keep orders placed after first_date and within 30 days
  3. 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.
AmountsROW_NUMBERRANKDENSE_RANK
900111
700222
700322
500443

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.

ProblemSymptomFix
Integer divisionIn PostgreSQL and SQL Server, 7 / 2 returns 3 and a conversion rate shows 0Make one side decimal: 7 * 1.0 / 2, or CAST(x AS NUMERIC)
Division by zeroQuery fails when a group has no visitsx / NULLIF(visits, 0) returns NULL instead of failing
Numbers stored as text'100' sorts before '9'; SUM refuses or casts oddlyCAST 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 zonesOrders near midnight land on the wrong dayKnow whether timestamps are UTC; convert before grouping by day
Dirty text'Casablanca', 'casablanca ', 'CASABLANCA' are three citiesTRIM 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

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