SQL for analysts

What you'll learn: how an online shop's data is laid out in tables, and how to read it with SELECT, ORDER BY and a row limit while knowing the order in which the database really runs your query.

Meet Leila and Fernleaf

Leila has just joined Fernleaf, a small online shop that sells houseplants, pots and plant care supplies, as its first analyst. She is quick with spreadsheets and has written the odd SELECT before, but she has never had a whole database to herself. Her manager, Tom, runs marketing. His questions will drive this course: who buys, what sells, which customers come back, and where shoppers give up.

Fernleaf's data lives in a relational database: a set of tables, each like a spreadsheet tab with fixed, named columns, linked to each other by shared id columns. Four tables matter to Leila.

The middle column is the most important thing on that table. Analysts call it the grain: what one row represents. An order with three different plants is one row in orders and three rows in order_items. Know the grain of every table you touch and you avoid half the classic SQL mistakes. It will matter a great deal when we reach joins.

Fernleaf's history starts in January 2025, when the current website launched. Right now the tables hold 4,812 customers, 12,640 orders, 29,350 order lines and 186 products.

Choosing columns with SELECT

The smallest useful query names some columns and a table:

SELECT name, country, signup_date FROM customers;

SELECT * returns every column. It is handy for a first look, but in queries you keep, list the columns you need. The result is easier to read, it will not change shape when someone adds a column, and on cloud warehouses that charge by data scanned (BigQuery's on-demand pricing works this way) it can cost noticeably less.

You can calculate new columns and name them with AS. Tom is wondering what a 10% price rise would look like:

SELECT name, price, price * 1.1 AS new_price FROM products;

DISTINCT removes duplicate rows from the result, which is a quick way to see which values a column holds:

SELECT DISTINCT country FROM customers;

Keywords are not case-sensitive, so select works as well as SELECT; writing them in capitals is just a common habit that makes queries easier to scan. A double hyphen starts a comment that runs to the end of the line, such as -- top orders for Tom.

Sorting and limiting

ORDER BY sorts the result, ascending by default; add DESC for largest first. Without ORDER BY, the database promises no order at all, even if the rows happen to come back sorted today.

Tom's first request is simple: "What were our ten biggest orders?" How you ask for only the first rows depends on the database:

Fernleaf runs on PostgreSQL, so Leila writes the query over several lines, one clause per line:

SELECT order_id, customer_id, order_date, total_amount

FROM orders

ORDER BY total_amount DESC

LIMIT 10;

The top result is a 412.50 order from an office that bought twenty large plants. Leila notices that two orders in the top ten have the status cancelled. That is a question for the next lesson.

Written order versus running order

Here is the idea that makes the rest of SQL easier. You write the clauses of a query in one fixed order, but the database works through them, logically, in a different one. Think of the address on an envelope: you write the person's name first and the country last, but the postal service reads it the other way round, country first, then town, then street, then name. SQL is similar. You write SELECT first, but it is one of the last things to happen.

The logical running order is:

1. FROM (and any joins): which table or tables to read.
2. WHERE: which rows to keep.
3. GROUP BY: how to bunch the remaining rows together.
4. HAVING: which groups to keep.
5. SELECT: which columns to compute and return.
6. ORDER BY: how to sort the result.
7. LIMIT (or TOP, or FETCH): how many rows to hand back.

Real databases are free to optimise how they do the work, but the result always behaves as if these steps ran in this order. That explains several puzzling errors. For example, this fails in PostgreSQL, MySQL, SQL Server and BigQuery:

SELECT name, price * 1.1 AS new_price FROM products WHERE new_price > 20;

WHERE runs before SELECT, so the alias new_price does not exist yet. Repeat the expression instead:

SELECT name, price * 1.1 AS new_price FROM products WHERE price * 1.1 > 20;

Snowflake is unusually lenient and accepts the alias in WHERE, but writing the expression out works everywhere. ORDER BY runs after SELECT, so ORDER BY new_price is fine in every database.

Recap

• A relational database stores data in tables linked by id columns; Fernleaf's four are customers, orders, order_items and products.
• Every table has a grain, what one row represents. Learn it before you query.
• List the columns you need instead of SELECT * in queries you keep, and use AS to name calculated columns.
• Without ORDER BY there is no guaranteed order. Row limits are LIMIT, TOP or FETCH FIRST, depending on the database.
• Queries run logically as FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT, which is why a SELECT alias cannot be used in WHERE in most databases.

What you'll learn: how to keep exactly the rows you mean with WHERE, combining conditions safely and matching lists, ranges and text patterns.

Back to the cancelled orders

Leila's top-ten list from the last lesson had a flaw: two of the orders were cancelled, so they never earned Fernleaf a penny. Tom only cares about orders that went through. That is a job for WHERE, which keeps the rows for which a condition is true and drops the rest.

SELECT order_id, order_date, total_amount

FROM orders

WHERE status = 'completed'

ORDER BY total_amount DESC

LIMIT 10;

Remember the running order: WHERE runs before ORDER BY and LIMIT, so the database removes the cancelled orders first and only then picks the top ten. The list now holds ten real sales.

Comparison operators

Put text in single quotes. In PostgreSQL, Snowflake and SQL Server double quotes mean a column or table name, so WHERE status = "completed" looks for a column called completed and fails. MySQL and BigQuery do accept double quotes around text, but single quotes work everywhere, so make them a habit.

Dates are compared the same way. The ISO format, year then month then day, is understood by all the major databases:

SELECT name, signup_date FROM customers WHERE signup_date >= '2026-01-01';

Most of them also accept the more explicit DATE '2026-01-01'; SQL Server is the exception and simply takes the quoted string.

AND, OR and the parentheses that save you

Tom's next request: "Which customers in the UK or Ireland signed up this year? I want to send them a welcome-back offer." Leila's first attempt:

SELECT name, country FROM customers

WHERE country = 'UK' OR country = 'IE' AND signup_date >= '2026-01-01';

It returns 2,731 rows, which feels too many for a shop with 4,812 customers in total. The cause is that AND binds more tightly than OR. It is just like multiplication before addition in school arithmetic: 2 + 3 × 4 is 14, not 20, because the multiplication happens first. Here, the database reads Leila's condition as "UK customers, of any date" OR "Irish customers who signed up in 2026". That is 2,640 UK customers plus 91 new Irish ones.

Parentheses fix the grouping, exactly as they do in arithmetic:

WHERE (country = 'UK' OR country = 'IE') AND signup_date >= '2026-01-01';

Now the result is 403 customers: 312 new in the UK and 91 new in Ireland. The rule worth adopting is simple: whenever a condition mixes AND and OR, add parentheses, even when you think you don't need them. The next person to read the query, often you in six months, will thank you.

NOT reverses a condition, as in WHERE NOT (country = 'UK'). It also binds tightly, so wrap what it applies to in parentheses too.

Lists, ranges and patterns

Three shortcuts make filters shorter and easier to read.

IN tests against a list, and is a tidier way to write several ORs:

WHERE country IN ('UK', 'IE')

BETWEEN tests a range and includes both ends, so this keeps orders of exactly 50 and exactly 100:

WHERE total_amount BETWEEN 50 AND 100

LIKE matches text patterns. % stands for any run of characters (including none) and _ for exactly one character. Tom wants every product with "monstera" in its name:

SELECT name, price FROM products WHERE name LIKE '%monstera%';

In PostgreSQL this returns nothing, even though the shop sells three monsteras. Text comparison there is case-sensitive, and the products are named "Monstera Deliciosa" and so on, with a capital M. Case rules differ by database:

1. PostgreSQL, Snowflake and BigQuery compare text case-sensitively by default.
2. MySQL and SQL Server usually compare case-insensitively, because of their default collations (the rules for comparing and sorting text), though a database can be set up otherwise.
3. PostgreSQL and Snowflake offer ILIKE for a case-insensitive match.
4. Lowering both sides works everywhere: WHERE LOWER(name) LIKE '%monstera%'.

Leila uses the portable version and gets her three products: Monstera Deliciosa at 24.00, a large Monstera Deliciosa at 45.00 and Monstera Adansonii at 18.50.

A quick habit: check the count

Before handing a filtered list to anyone, Leila runs the same filter with COUNT(*) in place of the column list:

SELECT COUNT(*) FROM customers WHERE (country = 'UK' OR country = 'IE') AND signup_date >= '2026-01-01';

A number that is wildly bigger or smaller than she expected is the cheapest warning sign there is. The 2,731 from her first attempt would have caught her eye straight away. We will build on this habit throughout the course.

One more surprise is waiting. When Tom asks how many customers live outside the UK, Leila's WHERE country <> 'UK' gives a number that doesn't add up. The reason is missing values, the subject of the next lesson.

Recap

• WHERE keeps rows for which the condition is true, and runs before sorting and limiting.
• Put text and dates in single quotes; write dates as year, month, day.
• AND binds before OR, like multiplication before addition. Use parentheses whenever you mix them.
• IN checks a list, BETWEEN checks an inclusive range, LIKE matches patterns with % and _.
• Case sensitivity of text matching depends on the database; LOWER() on both sides is the portable fix.
• Run a COUNT(*) of your filter to sanity-check it before you share results.