Tezkor cheat sheet

-- Har bir group uchun Top N ...................................... 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;

-- Qaysi X da Y yo'q?  (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 muqobil:
SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id);

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

-- Pivot: row lardan column larga ................................. 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;

-- Group ning shartga mos qismi (foizda) .......................... Ch.4
ROUND(100.0 * SUM(CASE WHEN cond THEN 1 ELSE 0 END) / COUNT(*), 1)

-- Har bir row ning umumiydagi ulushi ............................. Ch.7
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1)

-- Oydan oyga o'zgarish ........................................... Ch.7
(n - LAG(n) OVER (ORDER BY m)) * 100.0 / LAG(n) OVER (ORDER BY m)

-- Ikkinchi / N-o'rindagi eng katta ............................... 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 (standart SQL da MEDIAN() yo'q) ......................... 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);

-- Xavfsiz sana oralig'i (timestamp ga BETWEEN ishlatmang) ........ Ch.3
WHERE d >= '2026-01-01' AND d < '2026-02-01';

-- Fan-out bo'lmasligi uchun JOIN dan oldin aggregate ............. 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;

-- Xavfsiz update marosimi ........................................ Ch.10
SELECT * FROM t WHERE cond;        -- 1. qara
BEGIN TRANSACTION;                  -- 2. himoya
UPDATE t SET c = v WHERE cond;      -- 3. bir xil WHERE
SELECT * FROM t WHERE cond;         -- 4. tekshir
COMMIT;                             -- yoki ROLLBACK

# har bir seaborn chaqiruvining shakli
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=)                     # ROW larni sanaydi
sns.barplot(data=d, x=, y=, estimator="mean", errorbar=("ci",95))  # bu 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)  # kichik n
sns.swarmplot(data=d, x=, y=, size=3)                # faqat kichik n
sns.pointplot(data=d, x=, y=, hue=)
sns.catplot(data=d, x=, y=, col=, kind="box"|"bar"|"violin"|"strip"|"count")

# matrix (WIDE ma'lumot) .................................. 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)
# avval matrix oling:
df.pivot(index=, columns=, values=) | df.pivot_table(...) | pd.crosstab(a, b)
df.corr(numeric_only=True)                           # pandas 2+ da shart

# 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   (siyohni masshtablaydi, figure o'lchamini EMAS)
sns.despine()

# palette lar — palette TURINI ma'lumot turiga moslang
qualitative : "deep" "muted" "colorblind" "Set2"    # tartibsiz category lar
sequential  : "viridis" "rocket" "mako" "Blues"     # past → yuqori
diverging   : "coolwarm" "vlag" "RdBu_r"            # o'rta nuqta — center=0 ishlating

# yakunlash (matplotlib)
plt.figure(figsize=(9, 5))                # faqat axes-level
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()

# saqlash — DOIMO plt.show() dan oldin
plt.savefig("f.png", dpi=300, bbox_inches="tight")   # 300 print, 100 ekran
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,),              # HECH QACHON f-string emas
                 parse_dates=["enrolled_at"])
conn.close()
sns.boxplot(data=df, x="course", y="grade")