Quick-reference cheat sheet

-- Top N per group ................................................ Ch.7
WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC) rn FROM t)
SELECT * FROM r WHERE rn <= 3;

-- Which X has no Y?  (anti-join) ................................. Ch.5
SELECT a.* FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.a_id IS NULL;
-- NULL-safe alternative:
SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id);

-- Find duplicates ................................................ Ch.4
SELECT k1, k2, COUNT(*) FROM t GROUP BY k1, k2 HAVING COUNT(*) > 1;

-- Pivot: rows to columns ......................................... Ch.8
SELECT g,
       SUM(CASE WHEN s = 'a' THEN 1 ELSE 0 END) AS a,
       SUM(CASE WHEN s = 'b' THEN 1 ELSE 0 END) AS b
FROM t GROUP BY g;

-- Percentage of a group matching a condition ..................... Ch.4
ROUND(100.0 * SUM(CASE WHEN cond THEN 1 ELSE 0 END) / COUNT(*), 1)

-- Each row's share of the total .................................. Ch.7
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1)

-- Month-over-month change ........................................ Ch.7
(n - LAG(n) OVER (ORDER BY m)) * 100.0 / LAG(n) OVER (ORDER BY m)

-- Second / Nth highest ........................................... Ch.13
WITH r AS (SELECT x, DENSE_RANK() OVER (ORDER BY x DESC) k FROM t)
SELECT DISTINCT x FROM r WHERE k = 2;

-- Median (no MEDIAN() in standard SQL) ........................... Ch.13
WITH o AS (SELECT x, ROW_NUMBER() OVER (ORDER BY x) rn, COUNT(*) OVER () n FROM t)
SELECT AVG(x) FROM o WHERE rn IN ((n+1)/2, (n+2)/2);

-- Safe date range (never BETWEEN on a timestamp) ................. Ch.3
WHERE d >= '2026-01-01' AND d < '2026-02-01';

-- Aggregate BEFORE joining, to avoid fan-out ..................... Ch.5
WITH per AS (SELECT fk, COUNT(*) n FROM child GROUP BY fk)
SELECT p.*, per.n FROM parent p LEFT JOIN per ON per.fk = p.id;

-- Safe update ritual ............................................. Ch.10
SELECT * FROM t WHERE cond;        -- 1. look
BEGIN TRANSACTION;                  -- 2. protect
UPDATE t SET c = v WHERE cond;      -- 3. same WHERE
SELECT * FROM t WHERE cond;         -- 4. check
COMMIT;                             -- or ROLLBACK

# the shape every seaborn call takes
sns.PLOT(data=df, x="col", y="col", hue="col", ...)

# relational .................................................. Ch.15
sns.scatterplot(data=d, x=, y=, hue=, size=, style=, alpha=)
sns.lineplot(data=d, x=, y=, hue=, errorbar=("ci", 95))
sns.relplot(data=d, x=, y=, col=, row=, kind="scatter"|"line", height=, aspect=)

# distribution ................................................ Ch.16
sns.histplot(data=d, x=, bins=|binwidth=, stat="count"|"density", kde=True,
             hue=, multiple="layer"|"dodge"|"stack"|"fill", element="bars"|"step")
sns.kdeplot(data=d, x=, y=, bw_adjust=1, fill=True, cut=0, common_norm=False)
sns.ecdfplot(data=d, x=, hue=)
sns.displot(data=d, x=, col=, kind="hist"|"kde"|"ecdf")

# categorical ................................................. Ch.17
sns.countplot(data=d, x=, hue=)                     # counts ROWS
sns.barplot(data=d, x=, y=, estimator="mean", errorbar=("ci",95))  # a MEAN
sns.boxplot(data=d, x=, y=, hue=, order=[...])
sns.violinplot(data=d, x=, y=, hue=, split=True, cut=0, inner="box")
sns.stripplot(data=d, x=, y=, jitter=.25, alpha=.6)  # small n
sns.swarmplot(data=d, x=, y=, size=3)                # small n only
sns.pointplot(data=d, x=, y=, hue=)
sns.catplot(data=d, x=, y=, col=, kind="box"|"bar"|"violin"|"strip"|"count")

# matrix (WIDE data) .......................................... Ch.18
sns.heatmap(matrix, annot=True, fmt=".2f", cmap="coolwarm",
            center=0, vmin=-1, vmax=1, mask=mask, linewidths=.5)
sns.clustermap(matrix, cmap="vlag", center=0)
# get a matrix first:
df.pivot(index=, columns=, values=) | df.pivot_table(...) | pd.crosstab(a, b)
df.corr(numeric_only=True)                           # pandas 2+ needs this

# regression .................................................. Ch.19
sns.regplot(data=d, x=, y=, order=2, lowess=True, robust=True, logistic=True, ci=95)
sns.residplot(data=d, x=, y=)
sns.lmplot(data=d, x=, y=, hue=, col=, height=, aspect=)

# grids ....................................................... Ch.20
sns.pairplot(d, hue=, vars=[...], diag_kind="kde", corner=True, kind="scatter")
sns.jointplot(data=d, x=, y=, kind="scatter"|"hex"|"kde"|"reg", height=)
g = sns.FacetGrid(d, col=, row=, col_wrap=3, height=, hue=)
g.map_dataframe(sns.scatterplot, x=, y=); g.add_legend()

# theme ....................................................... Ch.21
sns.set_theme(style="whitegrid", context="talk", palette="deep", font_scale=1.1)
# style:   darkgrid | whitegrid | dark | white | ticks
# context: paper | notebook | talk | poster   (scales ink, NOT figure size)
sns.despine()

# palettes — match the palette TYPE to the data type
qualitative : "deep" "muted" "colorblind" "Set2"    # unordered categories
sequential  : "viridis" "rocket" "mako" "Blues"     # low → high
diverging   : "coolwarm" "vlag" "RdBu_r"            # midpoint — use center=0

# finishing (matplotlib)
plt.figure(figsize=(9, 5))                # axes-level only
plt.title("...") ; plt.xlabel("...") ; plt.ylabel("...")
plt.xticks(rotation=45, ha="right")
plt.legend(title="", bbox_to_anchor=(1.02, 1), loc="upper left")
plt.axhline(0, ls="--", color="grey")
ax.bar_label(ax.containers[0], fmt="%.0f")
plt.tight_layout()

# save — ALWAYS before plt.show()
plt.savefig("f.png", dpi=300, bbox_inches="tight")   # 300 print, 100 screen
g.savefig("f.png", dpi=300, bbox_inches="tight")     # figure-level
plt.show()

import sqlite3, pandas as pd, numpy as np, seaborn as sns, matplotlib.pyplot as plt

conn = sqlite3.connect("school.db")
df = pd.read_sql("SELECT ... FROM ... WHERE ...", conn,
                 params=(value,),              # NEVER an f-string
                 parse_dates=["enrolled_at"])
conn.close()
sns.boxplot(data=df, x="course", y="grade")