SQL

SQL (Structured Query Language, pronounced "ess-cue-ell" or "sequel") is how analysts talk to the database where the company's real records live. No other skill gets you hired faster: almost every Data Analyst job expects at least intermediate SQL.

The good news: SQL is simple on purpose. You describe what rows you want, and the database figures out how to get them. If you mastered Excel's SUMIFS and lookups, you already know the concepts — you are just expressing them in a different language. This topic takes you from SELECT * to window functions with progressively harder, business-realistic queries.

Complete Basics (tables, rows, metrics, dimensions). Excel lookups/aggregates help a lot but are optional. You need a free SQL playground (e.g. SQLite/DB Browser, or a hosted sandbox) — no install required.

A relational database stores data in tables (spreadsheet-like grids of rows = records and columns = fields). Customers, Orders, Products and Payments are separate tables connected by keys.

• Primary key: the column (or combo) that uniquely identifies each row — e.g. customer_id
• Foreign key: a column in one table that points to a primary key in another — e.g. customer_id on Orders
• Relationships: one-to-many (one customer has many orders), many-to-many (products ↔ orders via an order_items table)

This design avoids duplication: you don't repeat the customer's name on every order — you store the id and join when needed.

Every query follows the same shape. This is the one syntax block you must memorize:

SELECT column1, column2
FROM   orders
WHERE  status = 'shipped'
ORDER BY order_date DESC
LIMIT  10;

• SELECT — which columns to return (* = all, fine for exploration)
• FROM — which table
• WHERE — filter rows (before aggregation!)
• ORDER BY — sort (ASC/DESC)
• LIMIT — cap the number of rows (tired hands: LIMIT before you run!)

DISTINCT returns unique values of a column: SELECT DISTINCT category FROM products; — the SQL cousin of Remove Duplicates.

Aggregates collapse many rows into one summary number: COUNT, SUM, AVG, MIN, MAX. Add GROUP BY to aggregate per category (this is a PivotTable in SQL):

SELECT   category,
         COUNT(*)          AS order_count,
         SUM(amount)       AS revenue,
         AVG(amount)       AS avg_order
FROM     orders
GROUP BY category
ORDER BY revenue DESC;

HAVING filters after aggregation (WHERE filters before):

SELECT category, SUM(amount) AS revenue
FROM   orders
GROUP  BY category
HAVING SUM(amount) > 10000;

JOIN attaches rows from a second table by a key — Excel's XLOOKUP, but for whole tables. Standard pattern:

SELECT    o.id, c.name, o.amount
FROM      orders o
JOIN      customers c ON c.id = o.customer_id;

Rarely in analyst work use RIGHT/FULL — INNER and LEFT cover ~95% of the job. Remember: JOIN BEFORE you filter, and never join on non-unique keys unless you understand why duplicates multiply.

CASE WHEN is the SQL version of Excel's IF/IFS — build new categories:

SELECT id, amount,
       CASE WHEN amount >= 100 THEN 'Large'
            WHEN amount >= 30  THEN 'Medium'
            ELSE 'Small' END AS order_size
FROM   orders;

NULL means missing/unknown, not zero. A NULL in a SUM is skipped; a NULL in a comparison is never equal. Handle carefully:

• WHERE col IS NULL (not col = NULL!)
• COALESCE(col, 0) — use a fallback value (COALESCE takes many args, first non-NULL)
• COUNT(col) skips NULLs; COUNT(*) counts all rows

When one query needs the result of another, nest it — subquery (inline) and CTE (named, using WITH). CTEs are the analyst favorite because they make queries read like a story:

WITH monthly AS (
  SELECT   DATE_TRUNC('month', order_date) AS month,
           SUM(amount) AS revenue
  FROM     orders
  GROUP BY 1
)
SELECT * FROM monthly
WHERE revenue < 10000;

Break a hard problem into small CTE steps (filter → aggregate → compare) and the query becomes both correct and explainable to reviewers.

Window functions calculate over "windows" of rows without collapsing them — the row stays visible while you add a rank, running total, or the previous period's value.

SELECT   customer_id, order_date, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY order_date)   AS order_seq,
         RANK()       OVER (ORDER BY amount DESC)  AS amount_rank,
         LAG(amount)  OVER (PARTITION BY customer_id
                            ORDER BY order_date)   AS prev_order
FROM     orders;

• PARTITION BY = reset the window per group (per customer)
• ORDER BY inside OVER = ordering within the window
• ROW_NUMBER = unique sequence (1,2,3)
• RANK allows ties with gaps (1,1,3); DENSE_RANK no gaps (1,1,2)
• LAG/LEAD = previous / next row (period-over-period change!)

Window functions power "top 3 per region", churn analysis, and month-over-month growth — all classic analyst asks.

Date hygiene: filter by range with >= and < (never = '2026-03' on a timestamp), group with DATE_TRUNC('month', …) or EXTRACT(YEAR FROM …), compute age/tenure with date differences.

String hygiene: LOWER()/UPPER() to normalize, TRIM(), LEFT()/RIGHT(), LIKE '%pattern%', SPLIT_PART().

Thinking like an analyst: every business question maps to a query shape — a rate is COUNT(DISTINCT …) over a base; a trend is a group-by-month: a comparison is two queries joined by a common dimension. Before writing SQL, ask: what is a row here? What is the grain? (order-level, customer-level, day-level). The grain — the meaning of one row — is the definition that makes or breaks the query.

Use any SQL sandbox with a tiny shop schema (customers, orders, order_items, products). Write and run:

1. List orders from last month with status, sorted newest first.
2. Revenue and order count per category, top 3 only (HAVING + ORDER BY + LIMIT).
3. Which customers have never ordered? (LEFT JOIN + IS NULL)
4. Monthly revenue with the previous month column (CTE + LAG).
5. Top 2 products per category by revenue (ROW_NUMBER + PARTITION BY inside a CTE).
6. Average order value per region with NULL-safe COALESCE and a CASE size bucket.

SQL is the interview gauntlet for analyst roles and the daily workhorse afterwards: scheduled reports, ad-hoc "can you tell me…" questions, and building the clean datasets that Power BI and Python consume. Analysts spend a large share of their week inside CTEs and GROUP BYs, so interviewers probe SQL specifically — expect round-trips of join-based, window-based and aggregation-based questions. The queries you learn here become the source of the dashboards you assemble later in this path.

Business problem: The e-commerce leadership wants a repeatable "Sales Health" query pack — not a one-off — covering revenue, customers and catalog performance.

Dataset: any public retail schema with customers/orders/order_items/products (samples are bundled with most SQL learning tools).

Objective: produce clean, reusable, commented queries ready to hand to a manager.