SQL

Query order, joins, aggregation, window functions, CTEs, dates, NULL semantics, analytics patterns, and query safety.

01

Logical Query Order

FROM / JOIN
WHERE
GROUP BY
HAVING
SELECT + windows
DISTINCT
ORDER BY
LIMIT / OFFSET

Written order differs from evaluation order

WHERE vs HAVING: WHERE filters rows before grouping; HAVING filters groups after.
Aliases: SELECT aliases are not visible in WHERE, but most engines allow them in ORDER BY.
Window functions: Run after WHERE/GROUP BY/HAVING, so filter their result in an outer query.

02

SELECT and Filtering

SQL

SELECT user_id,
       revenue * 1.18 AS revenue_gross
FROM orders
WHERE status = 'paid'
  AND country IN ('IN', 'US', 'GB')
  AND revenue BETWEEN 100 AND 500
  AND email LIKE '%@example.com'
  AND (coupon IS NULL OR coupon <> 'TEST');

Parentheses: Always group mixed AND / OR conditions explicitly.
BETWEEN: Inclusive at both ends; prefer half-open ranges for timestamps.
DISTINCT: Deduplicates output rows; it can hide data-quality problems.

03

CASE Expressions

SQL

SELECT order_id,
       CASE
         WHEN revenue >= 1000 THEN 'high'
         WHEN revenue >= 100  THEN 'mid'
         ELSE 'low'
       END AS segment
FROM orders;

First match wins: Order WHEN branches from most to least specific.
Missing ELSE: Unmatched rows become NULL.

04

Aggregation

SQL

SELECT country,
       COUNT(*)                AS orders,
       COUNT(DISTINCT user_id) AS buyers,
       SUM(revenue)            AS revenue,
       AVG(revenue)            AS aov
FROM orders
GROUP BY country
HAVING COUNT(*) >= 100
ORDER BY revenue DESC;

COUNT(*) vs COUNT(col): COUNT(col) skips NULLs.
Conditional aggregation: SUM(CASE WHEN … THEN 1 ELSE 0 END) counts a subset per group.

05

Joins

Join	Returns
INNER	Rows with a match on both sides
LEFT	All left rows; NULLs where no match
FULL OUTER	All rows from both sides
CROSS	Every combination (Cartesian product)
Anti-join	Left rows with no match (`NOT EXISTS`)
Semi-join	Left rows with a match (`EXISTS`)

SQL

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;   -- customers with no orders

06

Join Cardinality

Most wrong totals come from joins that silently change the grain of the data.

One-to-one: Row count stays the same.
One-to-many: Row count grows to the "many" side.
Many-to-many: Can multiply rows and double-count sums.
Check grain: Know what one row means on each side before joining.

Filtering a LEFT JOIN in WHERE

A WHERE condition on the right table turns a LEFT JOIN into an INNER JOIN. Move it into the ON clause instead.

07

CTEs and Subqueries

SQL

WITH paid AS (
  SELECT user_id, SUM(revenue) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY user_id
)
SELECT u.country, AVG(p.revenue) AS avg_rev
FROM paid p
JOIN users u USING (user_id)
GROUP BY u.country;

CTE: Names an intermediate step; makes long queries readable and testable.
Recursive CTE: Walks hierarchies such as org charts or category trees.
EXISTS: Stops at the first match; NOT EXISTS is NULL-safe, unlike NOT IN.

08

Window Functions

SQL

SELECT user_id, order_date, revenue,
  ROW_NUMBER() OVER w AS rn,
  SUM(revenue) OVER (
    PARTITION BY user_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND CURRENT ROW) AS running_total,
  LAG(revenue) OVER w AS prev_revenue
FROM orders
WINDOW w AS (PARTITION BY user_id
             ORDER BY order_date);

Function	Ties
ROW_NUMBER	Unique 1, 2, 3, 4
RANK	Gaps: 1, 2, 2, 4
DENSE_RANK	No gaps: 1, 2, 2, 3

09

Top-N per Group and Dedup

SQL

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY updated_at DESC, id DESC) AS rn
  FROM events
)
SELECT * FROM ranked WHERE rn = 1;

Tie-breaker: Add a unique column to ORDER BY so the kept row is deterministic.
Top 3 per group: Same pattern with rn <= 3.

10

Dates and Time

SQL

WHERE event_time >= '2026-01-01'
  AND event_time <  '2026-02-01'   -- half-open

SELECT DATE_TRUNC('month', created_at) AS month,
       COUNT(*) AS signups
FROM users
GROUP BY 1;

Half-open ranges: >= start AND < end avoids missing the last day and double counting.
Time zones: Store UTC; convert once at reporting time.
Dialects: Date functions differ (DATE_TRUNC, DATEADD, INTERVAL); check your engine.

11

NULL Semantics

Three-valued logic: Comparisons with NULL return UNKNOWN, which WHERE treats as false.
Test with IS NULL: col = NULL is never true.
COALESCE: Returns the first non-NULL argument, for defaults.
NULLIF: x / NULLIF(y, 0) avoids divide-by-zero.
Aggregates: SUM, AVG, and COUNT(col) ignore NULLs; AVG is over non-NULL rows only.

NOT IN with NULLs

If the subquery returns any NULL, NOT IN returns no rows. Use NOT EXISTS instead.

12

Set Operations

UNION: Combines results and removes duplicates (costs a sort or hash).
UNION ALL: Keeps duplicates; faster and usually what you want.
INTERSECT: Rows present in both results.
EXCEPT / MINUS: Rows in the first result but not the second.

13

Analytics Patterns

SQL

-- conversion rate per variant
SELECT variant,
       AVG(CASE WHEN converted THEN 1.0
                ELSE 0.0 END) AS cvr
FROM experiment_users
GROUP BY variant;

Cohorts: Assign each user a first-event month, then count activity by months since.
Funnels: Count distinct users reaching each ordered step within a time window.
Sessionization: LAG(ts) gaps above a threshold start a new session; a running SUM gives the id.
Share of total: metric / SUM(metric) OVER () after aggregating to the intended grain.

14

Performance Basics

Filter early: Reduce rows before joins and aggregations.
Select needed columns: Avoid SELECT * in pipelines, especially on columnar stores.
Indexes: Help selective filters and join keys in OLTP databases; cost writes.
Partition pruning: Filter on the partition column so warehouses scan less data.
Read the plan: EXPLAIN shows scans, join strategies, and row estimates.

Sargable predicates

Keep the column bare: ts >= '2026-01-01' can use an index; DATE(ts) = '2026-01-01' usually cannot.

15

Safety Checklist

Write down what one row means after every CTE.
Check row counts before and after each join.
Use half-open date ranges with explicit time zones.
Decide how NULLs should behave in every filter and metric.
Run SELECT with the same WHERE before any UPDATE or DELETE.
Wrap data changes in a transaction you can roll back.