Need a shorter reference? SQL cheatsheet
If this repo helped you, please consider giving it a ⭐. It helps me showcase my work :)
| Task | SQL | SQL Dialect (default=PostgreSQL) | Excel |
|---|---|---|---|
| Moving average (e.g. 5-day) | AVG(price) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) | =AVERAGE(A2:A6) → drag down | |
| Latest record | ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) → WHERE rn = 1 | Manual sort → keep first record | |
| Percentage share of total | ROUND(sales / SUM(sales) OVER () *100, 2) | =[@Sales]/SUM(Table1[Sales]) → click % button | |
| Conditional aggregation | SUM(CASE WHEN retention = TRUE THEN 1 ELSE 0 END) | COUNT(*) FILTER (WHERE retention = TRUE) | =COUNTIF(A:A, TRUE) |
| String concatenation - Horizontal | CONCAT(A, ' - ', B) | A || ' - ' || B | =A2 & " - " & B2 |
| String concatenation - Vertical | STRING_AGG(col, ', ') | GROUP_CONCAT (MySQL only) | TEXTJOIN |
| Convert timestamp to text/string | DATE_FORMAT(date, '%Y-%m') | TO_CHAR(date, 'YYYY-MM') | =TEXT(A2,"yyyy-mm-dd") |
| Conditional Logic | CASE WHEN A>5 THEN 'High' ELSE 'Low' END | =IF(A2>5, "High", "Low") | |
| Find values missing from another table | WHERE NOT EXISTS (anti-join*) | Manual filtering | |
| Look up across tables | JOIN (or subquery) |
XLOOKUP/VLOOKUP | |
| Wildcard (any number of characters) | WHERE Singer LIKE 'S%' | =COUNTIF(Singer,"S*") | |
| Wildcard (exactly one character) | WHERE Singer LIKE '_____' | =COUNTIF(Singer,"?????") | |
| Substring | SUBSTR(A, 4, 2) | =MID(A2, 4, 2) | |
| Common ratio calculation | ROUND(col_A *1.0 / NULLIF(col_B, 0), 2) | =A2/B2 → IFERROR | |
| Average | AVG(col) | =AVERAGE(A:A) | |
| Rounding (same) | ROUND(col, 2) | =ROUND(A2, 2) | |
| Remove duplicates | DISTINCT | Data ribbon → Remove Duplicates | |
| Sorting | ORDER BY | Sort A→Z | |
| Pivot table | SELECT Region, SUM(Revenue) GROUP BY Region | "Sum Revenue by Region" |
* Unlike Excel blanks, SQL NULLs can affect comparison logic. Anti-joins avoid these issues and are robust to NULLs.
| SQL JOIN | Excel | |
|---|---|---|
| Behavior | Unexpected row multiplication after JOIN | Lookup functions assume single, unique match |
| Visual Clues (use the query below to isolate problematic keys) | Nearly identical rows clustered right next to each other | |
| Fix | Match table granularity. Composite-key tables (e.g. transaction records) are especially vulnerable | |
| Failure Mode | No error, valid-looking wrong answer | Errors such as #SPILL! |
| Habit for Success | Checking visually not sustainable. Need to understand table logic | Often working visually, shape mismatch immediately visible |
-- Practical fanout debugging
SELECT join_key, COUNT(*) AS row_ct
FROM joined_table
GROUP BY join_key
HAVING COUNT(*) > 10
ORDER BY row_ct DESC;
- Inspect the most duplicated keys first
- Repeated clusters → strong clue that JOIN may have fanned out
- Confirm by checking table grain and key uniqueness
Excel users are familiar with #SPILL! errors: a formula returns more results than expected, and Excel forces you to resolve the shape mismatch. SQL behaves differently. JOINs can silently expand rows because returning multiple matches is a perfectly valid relational operation. Subqueries are not immune, although they make it harder to fanout...
Ultimately an analyst's responsibility shifts from fixing errors to understanding table relationships.
Intuitively, Excel's Lookup functions are actually closer in feeling to a SQL subquery. The underlying logic is almost identical. A formula such as
=VLOOKUP(H18, price_lookup, 2, False)
maps directly to a query with nested structure:
But I think the most fitting translation for a lookup is JOIN because although the approaches differ, they ultimately perform the same pillar task in practice. After all, in a typical JOIN query, you'd select only the columns that are needed anyway. For this task, JOINs scale more naturally and remain readable even as the number of tables grows.
Speaking of powerful, SQL users will appreciate how a properly designed JOIN can comfortably bring together tables with millions of rows! As essential as Excel Lookups are, cross-referencing on the same scale is too memory-intensive and slow. Even Python's equivalent, pandas.merge(), has limitations because it also operates in memory.