SQL guides
Clear, practical guides written for learners.
Basic SQL queries: SELECT, WHERE, ORDER BY, GROUP BY and JOIN for beginners
SQL for beginners: basic SQL queries step by step with examples on a flights database, from SELECT to JOIN, plus the mistakes everyone makes at first.
Data analyst roadmap: what to learn, in what order, and how long it takes
A realistic data analyst roadmap: SQL first, then spreadsheets, statistics, BI, Python later, a portfolio and interview prep, with what to skip and timeframes.
ROW_NUMBER vs RANK vs DENSE_RANK in SQL: the difference on ties
ROW_NUMBER, RANK and DENSE_RANK differ only on ties. One table shows all three, then which to use for top N per group, deduplication and leaderboards.
SQL aggregate functions: COUNT, SUM, AVG, MIN, MAX with examples
SQL aggregate functions: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT), SUM, AVG, MIN, MAX, NULL handling, GROUP BY and FILTER, and traps that skew totals.
SQL CASE WHEN: conditional logic, categories and conditional aggregation
SQL CASE WHEN: simple and searched CASE, bucketing values into categories, custom sort order, NULL handling and conditional aggregation with SUM, COUNT, FILTER.
SQL cheat sheet for data analysts: every clause and function with a runnable example
A compact SQL cheat sheet for analysts: SELECT to ORDER BY, filters, aggregates, joins, CASE, NULL, subqueries, CTEs, window functions, dates and strings.
SQL CTE (WITH clause): write queries in readable steps
How to use a CTE (the SQL WITH clause): syntax, chaining several steps, reusing a result, recursive CTEs, CTE vs subquery vs temp table, and common mistakes.
SQL data types explained: numbers, text, dates, BOOLEAN and JSON
SQL data types for analysts: INTEGER vs DECIMAL vs FLOAT, VARCHAR vs TEXT, DATE, TIMESTAMP, BOOLEAN, JSON, CAST, integer division and type errors in reports.
SQL date functions: extract, truncate, add intervals and count days between dates
SQL date functions analysts use every day: EXTRACT, date_trunc, intervals and date differences, with real examples and PostgreSQL vs DuckDB differences.
How to find and delete duplicates in SQL, with examples
Find duplicates with GROUP BY and HAVING COUNT(*) > 1, keep the first copy with ROW_NUMBER or DISTINCT ON, and delete the rest in PostgreSQL, MySQL, SQL Server.
SQL for beginners: how to learn SQL step by step (with a 4-week plan)
Learn SQL from zero: your first SELECT, then filtering, sorting, aggregates and joins on a real database, a 2–4 week practice plan and common beginner mistakes.
SQL indexes explained for analysts: B-tree, EXPLAIN, when they help
SQL indexes for analysts: how a B-tree finds rows, when indexes speed up WHERE, JOIN and ORDER BY and when not, composite column order, EXPLAIN and zone maps.
SQL LAG and LEAD functions: previous and next row, with examples
LAG returns a value from the previous row, LEAD from the next. Offset and default, change vs the previous period, time between events, PARTITION BY.
SQL LIKE operator: % and _ wildcards, ILIKE, NOT LIKE and escaping
How SQL LIKE matches text: the % and _ wildcards, case-insensitive ILIKE, NOT LIKE and NULL, escaping a literal % or _, LIKE vs =, and when it can use an index.
NULL in SQL: IS NULL, COALESCE, NULL in joins, counts and comparisons
How NULL behaves in SQL: why = NULL finds nothing, IS NULL and IS DISTINCT FROM, NULLs in COUNT and AVG, COALESCE, NULLIF, LEFT JOINs and the NOT IN trap.
SQL ORDER BY: ASC, DESC, multiple columns, NULLS LAST and top N
How SQL ORDER BY sorts: ascending and descending, several columns, NULLS FIRST and LAST, sorting by alias or CASE, top N with LIMIT and ties, random order.
SQL pivot: turn rows into columns (and unpivot), with examples
Pivot rows to columns in SQL with CASE or FILTER, the PIVOT and UNPIVOT syntax in DuckDB and SQL Server, PostgreSQL crosstab, and why dynamic columns are hard.
SQL self join explained, with examples: pairs, connections, history
A self join joins a table to itself under two aliases. Unique pairs with a < b, connecting flights, return trips, history rows, and when LAG or LEAD is better.
SQL string functions: CONCAT, SUBSTRING, REPLACE, UPPER, TRIM and more
The SQL string functions analysts use to clean and reshape text: CONCAT and ||, SUBSTRING, SPLIT_PART, REPLACE, UPPER, TRIM, LPAD and regex, with real examples.
SQL subquery: subqueries in WHERE, SELECT and FROM, with examples
How SQL subqueries work in WHERE, SELECT and FROM: scalar and list subqueries, IN, EXISTS, correlated subqueries, derived tables and the NOT IN trap with NULLs.
Top N per group in SQL: ROW_NUMBER, RANK, QUALIFY and LATERAL
Top N rows per group in SQL (greatest-n-per-group), the Nth highest value, ties with ROW_NUMBER, RANK or DENSE_RANK, and QUALIFY or LATERAL.
SQL vs Excel for data analysis: what each is good at and how to switch
SQL vs Excel for data analysis: where each tool wins, and how VLOOKUP, pivot tables, filters, IF and SUMIFS translate into JOIN, GROUP BY, WHERE and CASE.
UNION vs UNION ALL in SQL (plus INTERSECT and EXCEPT), with examples
UNION removes duplicate rows, UNION ALL keeps them. When to use each, how INTERSECT and EXCEPT work, rules for combining queries and traps that skew totals.
GROUP BY vs HAVING (and WHERE vs HAVING) in SQL, with examples
What GROUP BY and HAVING each do, how HAVING differs from WHERE, the order SQL runs a query in, and the mistakes that break or silently skew grouped results.
Slowly changing dimension type 2 in SQL: as-of queries and checks
SCD type 2 in SQL: valid_from and valid_to, the value at a point in time, as-of joins to events, and queries that find overlaps and gaps in a history table.
Cohort retention analysis in SQL: day-N, weekly matrix, rolling
Build a cohort retention analysis in SQL: define cohorts, compute D1 and D7 retention, a weekly retention matrix with maturity rules, and repeat purchases.
Fill missing dates in SQL: a date spine with generate_series
How to fill missing dates in SQL: build a date spine with generate_series, LEFT JOIN your daily metrics to it, zero-fill gaps, build grids and fix averages.
Gaps and islands in SQL: streaks, consecutive days and pauses
Solve gaps and islands problems in SQL: find streaks of consecutive days with the row_number trick, list the gaps with LAG, and group periods by a condition.
SQL interview questions for data analysts: 20 with answers
20 SQL interview questions for data analyst roles: joins, NULLs, GROUP BY, window functions, Nth highest, medians and retention, with model answers.
SQL joins explained: INNER, LEFT, FULL, self and anti joins
How each SQL join works on real tables: INNER, LEFT, FULL OUTER, self and anti joins, why joins duplicate rows (fan-out) and how to avoid double counting.
SQL practice problems with answers: 15 tasks from easy to hard
15 SQL practice problems on one real airline database, from SELECT and WHERE to joins, HAVING, subqueries and window functions, each with a solution and output.
Running total in SQL: cumulative sum, by group, resets and frames
How to calculate a running total in SQL with SUM() OVER: cumulative sums by group, month-to-date resets, ties, ROWS vs RANGE frames and real query output.
SQL window functions: ROW_NUMBER, RANK, LAG and running totals
SQL window functions: OVER and PARTITION BY, ROW_NUMBER vs RANK vs DENSE_RANK, top N per group, running totals, moving averages, LAG/LEAD and frames.