29.10-dars: Intervyu: ML, SQL va case study

SAVOL:  "Bias-variance nima?"
JAVOB:  kutilgan xato = bias^2 + variance + kamaytirib bo'lmaydigan shovqin
        bias     - model o'rtacha qanchalik noto'g'ri (juda sodda model)
        variance - boshqa o'quv namunasida bashorat qanchalik o'zgaradi
                   (juda murakkab model)
        murakkablik oshsa: bias kamayadi, variance oshadi -> U shaklidagi xato
XATO:   "bias - bu ma'lumotdagi tarafkashlik" (boshqa tushuncha: 29.11)
TEKSHIRUV: 1-misol, 2-bo'lim - yoyilma test MSE bilan mos keladi

BELGISI: o'quvda yaxshi, validatsiyada yomon; o'rganish egri chiziqlarida
         katta bo'shliq 18.8-bob
CHORALAR:
  ko'proq ma'lumot            | regularizatsiya (L1, L2, dropout)
  soddaroq model / kam belgi  | erta to'xtatish (15.9, 20-qism)
  cross-validation bilan tanlash | ansambllar (bagging - variance ni kamaytiradi)
  augmentatsiya (22-qism)     | sizishni yo'qotish (ba'zan "overfitting" -
                              |   aslida sizish)
XATO: "overfitting bo'lsa, epochlarni ko'paytiraman" yoki faqat test
      to'plamiga qarab sozlash (18.11 - validatsiyaga overfitting)

L2 (Ridge):  loss + alfa * sum(w^2)   koeffitsientlarni NOLGA YAQINLASHTIRADI,
                                      lekin aynan nol qilmaydi; korrelyatsiyali
                                      belgilar orasida og'irlikni bo'ladi
L1 (Lasso):  loss + alfa * sum(|w|)   ko'p koeffitsientni AYNAN NOL qiladi ->
                                      belgi tanlash, siyrak model
NEGA: L1 cheklov sohasi romb - optimum ko'pincha burchakda (o'qda) bo'ladi;
      L2 sohasi aylana - burchak yo'q
ElasticNet - ikkalasi birga (korrelyatsiyali belgilarda L1 beqaror)
XATO: belgilarni masshtablamasdan regularizatsiya 17.4-bob - jarima
      birliklarga bog'liq bo'lib qoladi
TEKSHIRUV: 1-misol, 1-bo'lim - nol koeffitsientlar soni

SAVOL: "Firibgarlik 2%. Modelingiz accuracy 98% - yaxshimi?"
JAVOB: "hammasi manfiy" modeli ham ~98% oladi -> accuracy ma'nosiz
  METRIKA: PR-AUC (tasodifiy darajasi = musbat ulushi), recall/precision
           biznes chegarasida, ROC-AUC (tartiblash, lekin nomutanosiblikda
           optimistik ko'rinadi)
  CHEGARA: 0.5 emas - xatolar narxi bo'yicha tanlanadi 14.1-bob
  USULLAR: class_weight, chegara tanlash, kalibrlash 14.10-bob;
           oversampling/SMOTE - faqat o'quv qismida, CV ichida
XATO: resampling ni bo'lishdan OLDIN qilish (sizish); faqat ROC-AUC
TEKSHIRUV: 1-misol, 3-bo'lim

TA'RIF: bashorat vaqtida mavjud bo'lmaydigan ma'lumot o'qitishga kirib qoladi
TURLARI:
  maqsaddan sizish   - belgi maqsadning natijasi ("qaytarilgan_summa" ->
                       "kredit qaytmadi")
  bo'lishdan sizish  - masshtablash, belgi tanlash, imputatsiya, target
                       encoding BUTUN ma'lumotda, keyin CV
  vaqt sizishi       - kelajak ma'lumoti bilan o'qitish (28.1-28.3)
  guruh sizishi      - bir mijozning qatorlari ham train, ham test da
YECHIM: Pipeline (19-qism) - hamma o'rganiladigan qadam CV ichida;
        vaqt bo'yicha bo'lish; GroupKFold
BELGISI: "juda yaxshi" natija (AUC 0.99) - avval sizishni qidiring
TEKSHIRUV: 1-misol, 4-bo'lim - tasodifiy maqsadda accuracy 0.85

CHIZIQLI: signal ~ chiziqli, kam ma'lumot, talqin kerak, ekstrapolyatsiya,
          ko'p siyrak belgi (matn TF-IDF)
DARAXTLAR/BOOSTING: nochiziqlik, o'zaro ta'sirlar, zinapoyali bog'liqlik,
          aralash turdagi belgilar, masshtablash shart emas; jadval
          ma'lumotida ko'pincha eng kuchli
LEKIN: daraxt ekstrapolyatsiya qilmaydi (o'quv diapazonidan tashqarida
       konstanta); chiziqli signalni "zinapoya" bilan yaqinlashtiradi
QOIDA: ikkalasini ham sinang, CV da juftlashgan taqqoslang 18.10-bob
TEKSHIRUV: 1-misol, 6-bo'lim

1. boshlang'ich bashorat F0 = o'rtacha (MSE uchun)
2. har qadamda: qoldiqlar r = y - F (MSE ning manfiy gradienti)
   kichik daraxt h ni r ga o'qitish
   F <- F + eta * h   (eta - o'rganish tezligi, 0.05-0.1)
3. M ta qadam; erta to'xtatish validatsiya bo'yicha
BOSHQA LOSS: log-loss uchun "qoldiq" - y - p (gradient); daraxt
  barglaridagi qiymatlar Nyuton qadami bilan hisoblanadi
RANDOM FOREST DAN FARQI: RF - mustaqil chuqur daraxtlar, o'rtacha
  (variance ni kamaytiradi); boosting - ketma-ket sayoz daraxtlar,
  har biri oldingilar xatosini tuzatadi (bias ni kamaytiradi)
TEKSHIRUV: 1-misol, 5-bo'lim - 10 qatorli noldan yozilgan versiya
  sklearn bilan bir xil bashorat beradi

MANTIQIY TARTIB:  FROM / JOIN -> WHERE -> GROUP BY -> HAVING
                  -> SELECT (oyna funksiyalari shu yerda) -> DISTINCT
                  -> ORDER BY -> LIMIT
NATIJALAR:
  WHERE da agregat (SUM, COUNT) ishlatib bo'lmaydi -> HAVING
  WHERE da oyna funksiyasi natijasini ishlatib bo'lmaydi -> ichki so'rov/CTE
  SELECT dagi taxallusni WHERE da ishlatib bo'lmaydi (ko'p SQL dialektida)

mijozlar (300)        buyurtmalar (mijoz_id)
INNER JOIN   - faqat ikkala tomonda ham mos bo'lganlar
LEFT JOIN    - chapdagi HAMMA qator; mos kelmasa o'ng tomon NULL
               -> qatorlar soni ORTADI (bir mijoz - ko'p buyurtma)
ANTI-JOIN    - LEFT JOIN ... WHERE o'ng.id IS NULL
               ("hech narsa sotib olmaganlar")
FULL OUTER   - ikkala tomondagi hammasi (sqlite 3.39+ da bor)
CROSS JOIN   - har biri har biri bilan (dekart ko'paytma)
TUZOQ: LEFT JOIN dan keyin WHERE da o'ng jadval ustunini filtrlash
       LEFT ni INNER ga aylantiradi -> shartni ON ga qo'ying

SELECT m.shahar, COUNT(DISTINCT b.mijoz_id) AS xaridorlar, SUM(b.summa) AS daromad
FROM buyurtmalar b JOIN mijozlar m ON m.id = b.mijoz_id
WHERE b.sana >= '2025-01-01'              -- qatorlar filtri (guruhlashdan OLDIN)
GROUP BY m.shahar
HAVING COUNT(DISTINCT b.mijoz_id) >= 30   -- guruhlar filtri (guruhlashdan KEYIN)
ORDER BY daromad DESC;

-- har shaharda top-2 mijoz (top-N per group)
SELECT * FROM (
  SELECT shahar, mijoz_id, summa,
         ROW_NUMBER() OVER (PARTITION BY shahar ORDER BY summa DESC, mijoz_id) AS rn
  FROM jami) WHERE rn <= 2;

-- oyma-oy o'sish
SELECT oy, d, 1.0 * (d - LAG(d) OVER (ORDER BY oy)) / LAG(d) OVER (ORDER BY oy) AS osish
FROM oylik;

-- 7 kunlik sirg'aluvchi o'rtacha
SELECT sana, AVG(d) OVER (ORDER BY sana ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
FROM kunlik;

ROW_NUMBER  - 1, 2, 3, 4  (tenglikda ham turli raqam; tartibni to'liq bering!)
RANK        - 1, 2, 2, 4  (tenglikda bir xil, keyin sakrash)
DENSE_RANK  - 1, 2, 2, 3  (sakrashsiz)
"top-2" savolida: tenglik bo'lsa nima qilish kerak? - ANIQLASHTIRING
ROWS va RANGE: ROWS - qatorlar soni; RANGE - qiymat oralig'i; kunlar
  bo'shliqli bo'lsa "7 qator" != "7 kun" - avval kalendar jadval bilan to'ldiring

1. har mijozning kohorti = birinchi xarid oyi (MIN)
2. har xarid uchun oy_farq = xarid oyi - kohort oyi
3. kohort x oy_farq bo'yicha DISTINCT mijozlar soni / kohort hajmi
TUZOQ: oxirgi kohortlar uchun keyingi oylar hali KUZATILMAGAN - 0 emas,
  NULL (aks holda retention "tushib ketgandek" ko'rinadi)

-- topish
SELECT mijoz_id, sana, summa, COUNT(*) FROM buyurtmalar
GROUP BY mijoz_id, sana, summa HAVING COUNT(*) > 1;
-- bittasini qoldirib o'chirish
DELETE FROM buyurtmalar WHERE id IN (
  SELECT id FROM (SELECT id, ROW_NUMBER() OVER (
      PARTITION BY mijoz_id, sana, summa ORDER BY id) AS rn FROM buyurtmalar)
  WHERE rn > 1);

1. ANIQLASHTIRISH: kirish formati, chegaraviy holatlar (bo'sh ro'yxat,
   takrorlar, tenglik, NULL), natija formati
2. KICHIK MISOL: qo'lda 3-5 elementda natijani yozing
3. ODDIY VA TO'G'RI yechim - avval (hatto O(n^2) bo'lsa ham), ovoz chiqarib
4. MURAKKABLIK: vaqt va xotira; "yaxshilash mumkinmi?" - xesh, saralash,
   ikki ko'rsatkich, uyum (heap)
5. TEST: misol, chegaraviy holat, oddiy yechim bilan solishtirish

pandas MASALALARI (tez-tez):
  guruhlash + agregat, har guruhda top-N (sort + groupby.head),
  sessiyalarga ajratish (diff + cumsum), birlashtirish (merge) va uning
  qatorlar sonini tekshirish, pivot, sirg'aluvchi oyna (rolling)
ALGORITM MASALALARI (DS uchun odatda oddiy):
  ikki son yig'indisi (xesh), top-k (Counter + heap), takrorlarni topish,
  ikkilik qidiruv, satrlar bilan ishlash, oddiy dinamik dasturlash

1. ANIQLASHTIRUVCHI SAVOLLAR
   qaysi metrika? (daromad, buyurtmalar soni, konversiya?)
   qaysi davr va nimaga nisbatan? (o'tgan oy, o'tgan yil shu oy?)
   qachondan? keskinmi yoki asta-sekinmi?
   hammada yoki qaysidir segmentda?
   o'sha paytda nima o'zgardi? (reliz, narx, kampaniya, raqobatchi, bayram)
2. METRIKA DARAXTI
   daromad = sessiyalar * konversiya * o'rtacha chek
   sessiyalar = yangi + qaytgan; kanal bo'yicha ...
3. MA'LUMOT
   avval SIFAT: to'liqlik (qatorlar soni), yangi manba, formatlar,
   loglash o'zgarishi, vaqt zonasi; keyin kerakli jadvallar
4. YONDASHUV
   ichki: dekompozitsiya (qaysi komponent?), segmentlar (qaysi segment?),
     vaqt qatori (qachondan?)
   tashqi: mavsumiylik (o'tgan yil), raqobatchilar, bozor
5. BAHOLASH
   gipoteza -> tekshiruv: masalan "android reliz" -> android konversiyasi
   reliz sanasidan boshlab tushganmi? versiya bo'yicha farq bormi?
6. XATARLAR VA KEYINGI QADAMLAR
   noaniqliklar, qo'shimcha ma'lumot, tajriba (A/B), monitoring

1. ANIQLASHTIRISH:
   "sotuv" - daromadmi yoki buyurtmalar soni? -> daromad
   nimaga nisbatan? -> oldingi 4 hafta (hafta kunlari bir xil)
   tushish keskinmi? -> hisobotda ko'rinmaydi, kunlik qatorni ko'raman
2. MA'LUMOT SIFATI (birinchi!):
   kunlik qatorlar soni, har segment har kuni bormi, yangi manbalar,
   loglash/ETL o'zgarishi -> agar segment YO'QOLGAN bo'lsa, tushish
   haqiqiy emas, bu ma'lumot xatosi
3. DEKOMPOZITSIYA:
   daromad = sessiyalar * konversiya * o'rtacha chek
   qaysi komponent o'zgardi? (log-hissa)
4. SEGMENTLAR:
   platforma, hudud, kanal, yangi/qaytgan mijozlar, kategoriya
   qaysi segment tushishning qanchasini beradi? (hissa = segment o'zgarishi
   / umumiy oldingi daromad)
5. GIPOTEZA VA TEKSHIRUV:
   "android konversiyasi -22%" -> android reliz sanasi? versiya bo'yicha?
   ilova xatolari loglari? to'lov bosqichidagi voronka?
6. TAVSIYA VA XATARLAR:
   relizni orqaga qaytarish / tuzatish, monitoring 27.11-bob - segment
   darajasidagi ogohlantirish; keyingi relizlar uchun bosqichli chiqarish

S - SITUATION (vaziyat)   1-2 gap: kontekst, kim uchun, nega muhim
T - TASK (vazifa)          sizning vazifangiz, metrika, cheklovlar
A - ACTION (harakat)       SIZ nima qildingiz: ma'lumot, bazaviy model,
                           asosiy qarorlar va NEGA; nima ishlamadi
R - RESULT (natija)        raqam bazaga nisbatan; biznes ta'siri;
                           nimani boshqacha qilardingiz

2-3 daqiqa; keyin intervyuer chuqurlashadi - har raqamni tushuntira oling

S: Bankda o'z vaqtida to'lanmagan kreditlar ulushi oshayotgan edi; eslatmalar
   hammaga bir xil yuborilardi.
T: Keyingi oyda to'lovni kechiktirish ehtimolini bashorat qilish va eslatmalarni
   xavfli mijozlarga yo'naltirish; metrika - ROC-AUC va eslatmadan keyingi to'lov
   ulushi.
A: 18 oylik tranzaksiyalardan 40 belgi yig'dim, vaqt bo'yicha validatsiya
   qildim (tasodifiy bo'lish sizish berardi - kelajak ma'lumoti). Bazaviy
   logistik regressiya 0.71; gradient boosting 0.78 - 5 fold da juftlashgan
   farq 2*SE dan katta. Ehtimollarni kalibrladim, chunki bo'lim chegarani
   ehtimol bo'yicha tanlamoqchi edi. Ishlamagan narsa: "oxirgi to'lov
   kechikishi" belgisi - u keyinroq to'ldirilar ekan (sizish), olib tashladim.
R: A/B testda yo'naltirilgan eslatma o'z vaqtida to'lov ulushini 2.1 foiz
   punktga oshirdi (p = 0.01). Boshqacha qilganim: monitoringni birinchi
   kundan qurardim - 3 oydan keyin drift tufayli qayta o'qitish kerak bo'ldi.

TEZ-TEZ SO'RALADI:                      NIMA KUTILADI:
"eng qiyin loyihangiz"                  STAR, sizning hissangiz, saboq
"xato qilgan payt"                      halollik, nimani o'rgandingiz,
                                        qanday tuzatdingiz
"texnik bo'lmagan odamga tushuntirish"  29.4 - oddiy til, qaror uchun raqam
"rahbar bilan kelishmovchilik"          ma'lumotga tayanish, hurmat, murosaga
                                        kelish yoki eskalatsiya
"muddat tig'iz, ma'lumot yomon"         ustuvorlik, cheklovlarni ochiq aytish
"nega aynan bizda?"                     vakansiyani o'qiganingiz 29.8-bob

UMUMIY: 3-5 ta tayyor hikoya (STAR) - har biri bir necha savolga mos;
        "biz" emas "men"; muvaffaqiyatsizlik ham - saboq bilan;
        oxirida intervyuerga 2-3 savol (jamoa, ma'lumot infratuzilmasi,
        muvaffaqiyat o'lchovi)

import sqlite3
from contextlib import closing

import pandas as pd

# SQL ni pandas ichida sinash
with closing(sqlite3.connect(":memory:")) as con:
    df.to_sql("buyurtmalar", con, index=False)
    natija = pd.read_sql_query("SELECT ... FROM buyurtmalar ...", con)

# pandas: har guruhda top-N
top = (df.sort_values(["shahar", "summa"], ascending=[True, False])
         .groupby("shahar").head(2))

# pandas: sessiyalar (30 daqiqa)
df = df.sort_values(["user", "vaqt"])
farq = df.groupby("user")["vaqt"].diff()
df["sessiya"] = (farq.isna() | (farq > pd.Timedelta(minutes=30))).groupby(df["user"]).cumsum()

# case study: dekompozitsiya
r_ses = k_ses / o_ses
r_konv = (k_buy / k_ses) / (o_buy / o_ses)
r_chek = (k_dar / k_buy) / (o_dar / o_buy)   # r_ses * r_konv * r_chek = k_dar / o_dar

ML: ta'rif + nega + misol + kichik tajriba; accuracy nomutanosiblikda ma'nosiz
SQL: mantiqiy tartib (FROM-WHERE-GROUP-HAVING-SELECT-ORDER); JOIN dan keyin qatorlar sonini tekshir
jonli kod: aniqlashtir -> misol -> oddiy yechim -> murakkablik -> test
case: aniqlashtir -> ma'lumot sifati -> dekompozitsiya -> segment -> gipoteza -> xatarlar
STAR: vaziyat, vazifa, SIZNING harakatingiz, natija bazaga nisbatan

"""ML intervyu savollari kod bilan: L1/L2, bias-variance, nomutanosib sinflar, sizish, boosting."""

import numpy as np
from sklearn.datasets import make_regression
from sklearn.ensemble import GradientBoostingRegressor
from sklearn.feature_selection import SelectKBest, f_classif
from sklearn.linear_model import Lasso, LinearRegression, LogisticRegression, Ridge
from sklearn.metrics import average_precision_score, roc_auc_score
from sklearn.model_selection import cross_val_score, train_test_split
from sklearn.pipeline import make_pipeline
from sklearn.preprocessing import PolynomialFeatures
from sklearn.tree import DecisionTreeRegressor

YIG = "yig'indi"
YOQ = "farq yo'q"


def main() -> None:
    rng = np.random.default_rng(10)

    print("=== 1. L1 va L2: qaysi biri siyrak koeffitsient beradi? ===")
    X, y, haq_koef = make_regression(n_samples=200, n_features=50, n_informative=5,
                                     noise=10, coef=True, random_state=0)
    Xtr, Xte, ytr, yte = train_test_split(X, y, test_size=0.5, random_state=0)
    for nom, m in [("OLS", LinearRegression()), ("Ridge (L2)", Ridge(alpha=10)),
                   ("Lasso (L1)", Lasso(alpha=1.0, max_iter=10_000))]:
        m.fit(Xtr, ytr)
        nol = int(np.sum(np.abs(m.coef_) < 1e-8))
        print(f"  {nom:<11} nol koeffitsientlar {nol:>2}/50, test R^2 "
              f"{m.score(Xte, yte):.3f}")
    lasso = Lasso(alpha=1.0, max_iter=10_000).fit(Xtr, ytr)
    tanlangan = np.flatnonzero(np.abs(lasso.coef_) > 1e-8)
    inf = np.isin(np.flatnonzero(haq_koef != 0), tanlangan).sum()
    print(f"  Lasso tanlagan belgilar: {tanlangan.size} ta; informativlardan {inf}/5, "
          f"shovqin belgilari {tanlangan.size - inf}")

    print("\n=== 2. Bias-variance: polinom darajasi (n=30, 300 takror) ===")
    xg = np.linspace(0.05, 0.95, 50)
    haq = np.sin(2 * np.pi * xg)
    print(f"  {'daraja':>6} {'bias^2':>8} {'variance':>9} {'shovqin':>8} "
          f"{YIG:>9} {'test MSE':>9}")
    for d in [1, 3, 9]:
        bash, mse = [], []
        for _ in range(300):
            x = rng.random(30)
            yy = np.sin(2 * np.pi * x) + rng.normal(0, 0.3, 30)
            m = make_pipeline(PolynomialFeatures(d), LinearRegression()).fit(x[:, None], yy)
            b = m.predict(xg[:, None])
            bash.append(b)
            mse.append(np.mean((b - haq - rng.normal(0, 0.3, 50)) ** 2))
        b = np.array(bash)
        b2 = np.mean((b.mean(axis=0) - haq) ** 2)
        var = np.mean(b.var(axis=0))
        print(f"  {d:>6} {b2:>8.4f} {var:>9.4f} {0.09:>8.4f} {b2 + var + 0.09:>9.4f} "
              f"{np.mean(mse):>9.4f}")

    print("\n=== 3. Nomutanosib sinflar (musbat ~2%) ===")
    n = 20_000
    Xc = rng.normal(0, 1, (n, 5))
    logit = -4.6 + 1.2 * Xc[:, 0] + 0.8 * Xc[:, 1]
    yc = (rng.random(n) < 1 / (1 + np.exp(-logit))).astype(int)
    Xa, Xb, ya, yb = train_test_split(Xc, yc, test_size=0.5, random_state=0, stratify=yc)
    print(f"  musbat ulushi: {yb.mean():.4f}")
    print(f"  'hammasi manfiy' modeli: accuracy {1 - yb.mean():.4f}, recall 0.0")
    p = LogisticRegression().fit(Xa, ya).predict_proba(Xb)[:, 1]
    print(f"  logistik: ROC-AUC {roc_auc_score(yb, p):.3f}, PR-AUC "
          f"{average_precision_score(yb, p):.3f} (tasodifiy PR-AUC = {yb.mean():.3f})")
    for c in [0.5, 0.1]:
        t = p >= c
        tp = int((t & (yb == 1)).sum())
        print(f"  chegara {c}: precision {tp / max(t.sum(), 1):.3f}, "
              f"recall {tp / yb.sum():.3f}")

    print("\n=== 4. Sizish: belgi tanlash CV DAN OLDIN (y tasodifiy!) ===")
    xato, togri = [], []
    for _ in range(10):
        Xs = rng.normal(0, 1, (100, 2000))
        ys = rng.integers(0, 2, 100)
        top = SelectKBest(f_classif, k=20).fit(Xs, ys).get_support()
        xato.append(cross_val_score(LogisticRegression(max_iter=1000),
                                    Xs[:, top], ys, cv=5).mean())
        togri.append(cross_val_score(make_pipeline(SelectKBest(f_classif, k=20),
                                                   LogisticRegression(max_iter=1000)),
                                     Xs, ys, cv=5).mean())
    xato, togri = np.array(xato), np.array(togri)
    print("  10 ta tasodifiy ma'lumot (n=100, 2000 belgi, k=20), o'rtacha accuracy:")
    for nom, v in [("tanlash hamma ma'lumotda, keyin CV", xato),
                   ("tanlash Pipeline ichida (har fold)", togri)]:
        print(f"  {nom:<36} {v.mean():.3f} (SE {v.std(ddof=1) / np.sqrt(10):.3f})")
    print("  haqiqiy accuracy: 0.5 (belgilar va y bog'liq emas)")

    print("\n=== 5. Gradient boosting noldan (qoldiqlarga daraxt) va sklearn ===")
    Xg = rng.uniform(-2, 2, (1000, 3))
    yg = np.sin(Xg[:, 0]) * 2 + Xg[:, 1] ** 2 + rng.normal(0, 0.3, 1000)
    Xg1, Xg2, yg1, yg2 = Xg[:700], Xg[700:], yg[:700], yg[700:]
    f1, f2 = np.full(700, yg1.mean()), np.full(300, yg1.mean())
    for _ in range(100):
        qoldiq = yg1 - f1                          # MSE ning manfiy gradienti
        d = DecisionTreeRegressor(max_depth=2, random_state=0).fit(Xg1, qoldiq)
        f1 += 0.1 * d.predict(Xg1)
        f2 += 0.1 * d.predict(Xg2)
    sk = GradientBoostingRegressor(n_estimators=100, learning_rate=0.1, max_depth=2,
                                   random_state=0).fit(Xg1, yg1)
    mse_o = np.mean((f2 - yg2) ** 2)
    mse_s = np.mean((sk.predict(Xg2) - yg2) ** 2)
    print(f"  o'zimizniki test MSE {mse_o:.4f}, sklearn {mse_s:.4f}, "
          f"bashoratlar bir xil (maks farq < 1e-9): "
          f"{np.max(np.abs(f2 - sk.predict(Xg2))) < 1e-9}")
    print(f"  o'rtacha bilan bazaviy MSE: {np.mean((yg2 - yg1.mean()) ** 2):.4f}")

    print("\n=== 6. Daraxt yoki chiziqli model? (5-fold CV R^2) ===")
    Xd = rng.uniform(-2, 2, (600, 4))
    shakllar = {"chiziqli signal": Xd @ np.array([1.5, -2.0, 1.0, 0.5]),
                "zinapoya + o'zaro ta'sir": (3 * (Xd[:, 0] > 0) * (Xd[:, 1] > 0)
                                             + 2 * (Xd[:, 2] > 1))}
    for nom, signal in shakllar.items():
        yd = signal + rng.normal(0, 0.5, 600)
        r_lin = cross_val_score(LinearRegression(), Xd, yd, cv=5)
        r_gb = cross_val_score(GradientBoostingRegressor(random_state=0), Xd, yd, cv=5)
        d = r_gb - r_lin
        se = d.std(ddof=1) / np.sqrt(len(d))
        g = "boosting" if d.mean() > 2 * se else ("chiziqli" if d.mean() < -2 * se else YOQ)
        print(f"  {nom:<26} chiziqli {r_lin.mean():.3f}, boosting {r_gb.mean():.3f}, "
              f"farq {d.mean():+.3f} (SE {se:.3f}) -> {g}")

    print("\n=== 7. Xulosa ===")
    print(f"  L1: {50 - tanlangan.size} ta koeffitsient aynan nol, L2: 0 ta")
    print(f"  sizish: tasodifiy y da accuracy {xato.mean():.2f} "
          f"(to'g'ri CV da {togri.mean():.2f})")
    print("  ⭐ Har javobni kichik tajriba bilan tasdiqlash mumkin - intervyuda ayting")


if __name__ == "__main__":
    main()

=== 1. L1 va L2: qaysi biri siyrak koeffitsient beradi? ===
  OLS         nol koeffitsientlar  0/50, test R^2 0.976
  Ridge (L2)  nol koeffitsientlar  0/50, test R^2 0.949
  Lasso (L1)  nol koeffitsientlar 34/50, test R^2 0.987
  Lasso tanlagan belgilar: 16 ta; informativlardan 5/5, shovqin belgilari 11

=== 2. Bias-variance: polinom darajasi (n=30, 300 takror) ===
  daraja   bias^2  variance  shovqin  yig'indi  test MSE
       1   0.1565    0.0200   0.0900    0.2665    0.2653
       3   0.0029    0.0136   0.0900    0.1065    0.1082
       9   0.0004    0.1046   0.0900    0.1950    0.1979

=== 3. Nomutanosib sinflar (musbat ~2%) ===
  musbat ulushi: 0.0250
  'hammasi manfiy' modeli: accuracy 0.9750, recall 0.0
  logistik: ROC-AUC 0.840, PR-AUC 0.144 (tasodifiy PR-AUC = 0.025)
  chegara 0.5: precision 0.400, recall 0.008
  chegara 0.1: precision 0.164, recall 0.288

=== 4. Sizish: belgi tanlash CV DAN OLDIN (y tasodifiy!) ===
  10 ta tasodifiy ma'lumot (n=100, 2000 belgi, k=20), o'rtacha accuracy:
  tanlash hamma ma'lumotda, keyin CV   0.854 (SE 0.009)
  tanlash Pipeline ichida (har fold)   0.521 (SE 0.021)
  haqiqiy accuracy: 0.5 (belgilar va y bog'liq emas)

=== 5. Gradient boosting noldan (qoldiqlarga daraxt) va sklearn ===
  o'zimizniki test MSE 0.1333, sklearn 0.1333, bashoratlar bir xil (maks farq < 1e-9): True
  o'rtacha bilan bazaviy MSE: 4.1770

=== 6. Daraxt yoki chiziqli model? (5-fold CV R^2) ===
  chiziqli signal            chiziqli 0.974, boosting 0.952, farq -0.022 (SE 0.003) -> chiziqli
  zinapoya + o'zaro ta'sir   chiziqli 0.480, boosting 0.897, farq +0.417 (SE 0.026) -> boosting

=== 7. Xulosa ===
  L1: 34 ta koeffitsient aynan nol, L2: 0 ta
  sizish: tasodifiy y da accuracy 0.85 (to'g'ri CV da 0.52)
  ⭐ Har javobni kichik tajriba bilan tasdiqlash mumkin - intervyuda ayting

"""SQL intervyu savollari sqlite3 da: JOIN, GROUP BY/HAVING, oyna funksiyalari, retention, dublikatlar."""

import sqlite3
from contextlib import closing

import numpy as np
import pandas as pd

SHAHARLAR = ["Toshkent", "Samarqand", "Buxoro", "Namangan"]


def yarat(rng):
    """Mijozlar va buyurtmalar (2025-yil, 1-6 oy); ba'zi buyurtmalar takrorlangan."""
    n_m = 300
    mijoz = pd.DataFrame({
        "id": np.arange(1, n_m + 1),
        "shahar": rng.choice(SHAHARLAR, n_m, p=[0.5, 0.2, 0.15, 0.15]),
        "royxat": pd.to_datetime("2025-01-01")
        + pd.to_timedelta(rng.integers(0, 150, n_m), unit="D"),
    })
    qator = []
    for _, m in mijoz.iterrows():
        if rng.random() < 0.2:                     # 20% mijoz hech narsa sotib olmagan
            continue
        faollik = rng.uniform(0.01, 0.08)
        kun = m["royxat"]
        while kun < pd.Timestamp("2025-07-01"):
            if rng.random() < faollik:
                qator.append((int(m["id"]), kun, int(round(float(rng.lognormal(11.5, 0.6)), -3))))
            kun += pd.Timedelta(days=1)
            faollik *= 0.995                       # vaqt o'tishi bilan faollik pasayadi
    buy = pd.DataFrame(qator, columns=["mijoz_id", "sana", "summa"])
    takror = buy.sample(15, random_state=1)        # yuklashdagi xato: 15 ta takror qator
    buy = pd.concat([buy, takror]).sort_values(["sana", "mijoz_id"]).reset_index(drop=True)
    buy.insert(0, "id", np.arange(1, len(buy) + 1))
    for df in (mijoz, buy):
        for c in ("royxat", "sana"):
            if c in df:
                df[c] = df[c].dt.strftime("%Y-%m-%d")
    return mijoz, buy


def korsat(con, sarlavha, sql, n=None):
    df = pd.read_sql_query(sql, con)
    print(f"\n--- {sarlavha} ---")
    print(df.head(n).to_string(index=False) if n else df.to_string(index=False))
    return df


def main() -> None:
    rng = np.random.default_rng(2025)
    mijoz, buy = yarat(rng)
    with closing(sqlite3.connect(":memory:")) as con:
        mijoz.to_sql("mijozlar", con, index=False)
        buy.to_sql("buyurtmalar", con, index=False)
        print("=== 0. Jadvallar ===")
        print(f"  mijozlar: {len(mijoz)} qator, buyurtmalar: {len(buy)} qator")

        print("\n=== 1. JOIN turlari ===")
        korsat(con, "INNER va LEFT JOIN: qatorlar va mijozlar soni", """
            SELECT 'INNER' AS tur, COUNT(*) AS qatorlar, COUNT(DISTINCT m.id) AS mijozlar
            FROM mijozlar m JOIN buyurtmalar b ON b.mijoz_id = m.id
            UNION ALL
            SELECT 'LEFT', COUNT(*), COUNT(DISTINCT m.id)
            FROM mijozlar m LEFT JOIN buyurtmalar b ON b.mijoz_id = m.id""")
        korsat(con, "Anti-join: hech narsa sotib olmaganlar (shahar bo'yicha)", """
            SELECT m.shahar, COUNT(*) AS xaridsiz
            FROM mijozlar m LEFT JOIN buyurtmalar b ON b.mijoz_id = m.id
            WHERE b.id IS NULL
            GROUP BY m.shahar ORDER BY xaridsiz DESC, m.shahar""")

        print("\n=== 2. Dublikatlar: topish va o'chirish ===")
        korsat(con, "Takror guruhlar (mijoz, sana, summa)", """
            SELECT COUNT(*) AS takror_guruhlar, SUM(n - 1) AS ortiqcha_qatorlar
            FROM (SELECT mijoz_id, sana, summa, COUNT(*) AS n FROM buyurtmalar
                  GROUP BY mijoz_id, sana, summa HAVING COUNT(*) > 1)""")
        con.execute("""
            DELETE FROM buyurtmalar WHERE id IN (
              SELECT id FROM (
                SELECT id, ROW_NUMBER() OVER (PARTITION BY mijoz_id, sana, summa
                                              ORDER BY id) AS rn
                FROM buyurtmalar) WHERE rn > 1)""")
        qoldi = con.execute("SELECT COUNT(*) FROM buyurtmalar").fetchone()[0]
        print(f"  o'chirilgandan keyin: {qoldi} qator "
              f"(pandas drop_duplicates: {len(buy.drop_duplicates(['mijoz_id', 'sana', 'summa']))})")

        print("\n=== 3. GROUP BY va HAVING ===")
        korsat(con, "Shaharlar: kamida 30 xaridor bo'lganlari, daromad (mln so'm)", """
            SELECT m.shahar, COUNT(DISTINCT b.mijoz_id) AS xaridorlar,
                   COUNT(*) AS buyurtmalar, ROUND(SUM(b.summa) / 1e6, 1) AS daromad_mln
            FROM buyurtmalar b JOIN mijozlar m ON m.id = b.mijoz_id
            GROUP BY m.shahar HAVING COUNT(DISTINCT b.mijoz_id) >= 30
            ORDER BY daromad_mln DESC""")

        print("\n=== 4. Oyna funksiyalari ===")
        top = korsat(con, "Har shaharda top-2 mijoz (ROW_NUMBER)", """
            WITH jami AS (
              SELECT m.shahar, b.mijoz_id, SUM(b.summa) AS summa
              FROM buyurtmalar b JOIN mijozlar m ON m.id = b.mijoz_id
              GROUP BY m.shahar, b.mijoz_id)
            SELECT shahar, mijoz_id, summa FROM (
              SELECT *, ROW_NUMBER() OVER (PARTITION BY shahar
                                           ORDER BY summa DESC, mijoz_id) AS rn
              FROM jami) WHERE rn <= 2
            ORDER BY shahar, rn""")
        b2 = pd.read_sql_query("SELECT * FROM buyurtmalar", con).merge(
            mijoz[["id", "shahar"]], left_on="mijoz_id", right_on="id")
        pd_top = (b2.groupby(["shahar", "mijoz_id"], as_index=False)["summa"].sum()
                  .sort_values(["shahar", "summa", "mijoz_id"], ascending=[True, False, True])
                  .groupby("shahar").head(2))
        print(f"  pandas bilan mos: {pd_top['mijoz_id'].tolist() == top['mijoz_id'].tolist()}")
        korsat(con, "Oylik daromad va o'sish (LAG)", """
            WITH oy AS (SELECT substr(sana, 1, 7) AS oy, SUM(summa) AS d
                        FROM buyurtmalar GROUP BY 1)
            SELECT oy, ROUND(d / 1e6, 1) AS daromad_mln,
                   ROUND(100.0 * (d - LAG(d) OVER (ORDER BY oy))
                         / LAG(d) OVER (ORDER BY oy), 1) AS osish_foiz
            FROM oy ORDER BY oy""")
        korsat(con, "Kunlik daromadning 7 kunlik sirg'aluvchi o'rtachasi (oxirgi 5 kun)", """
            WITH kun AS (SELECT sana, SUM(summa) AS d FROM buyurtmalar GROUP BY sana)
            SELECT sana, ROUND(d / 1e3) AS kunlik_ming,
                   ROUND(AVG(d) OVER (ORDER BY sana ROWS BETWEEN 6 PRECEDING
                                                   AND CURRENT ROW) / 1e3) AS orta7_ming
            FROM kun ORDER BY sana DESC LIMIT 5""")

        print("\n=== 5. Retention: kohort (birinchi xarid oyi) va keyingi oylar ===")
        ret = korsat(con, "Kohortlar: faol mijozlar ulushi (NULL - hali kuzatilmagan oy)", """
            WITH oylar AS (
              SELECT mijoz_id,
                     CAST(substr(sana, 1, 4) AS INTEGER) * 12
                       + CAST(substr(sana, 6, 2) AS INTEGER) AS oy_n
              FROM buyurtmalar),
            birinchi AS (SELECT mijoz_id, MIN(oy_n) AS k_n FROM oylar GROUP BY mijoz_id),
            faol AS (
              SELECT DISTINCT o.mijoz_id, f.k_n, o.oy_n - f.k_n AS farq
              FROM oylar o JOIN birinchi f USING (mijoz_id)),
            jad AS (
              SELECT k_n, farq, COUNT(*) AS n FROM faol GROUP BY k_n, farq),
            mx AS (SELECT MAX(oy_n) AS mx FROM oylar)
            SELECT printf('%d-%02d', (j0.k_n - 1) / 12, (j0.k_n - 1) % 12 + 1) AS kohort,
                   j0.n AS hajm,
                   CASE WHEN j0.k_n + 1 <= mx THEN ROUND(1.0 * COALESCE(j1.n, 0) / j0.n, 3) END AS oy_1,
                   CASE WHEN j0.k_n + 2 <= mx THEN ROUND(1.0 * COALESCE(j2.n, 0) / j0.n, 3) END AS oy_2,
                   CASE WHEN j0.k_n + 3 <= mx THEN ROUND(1.0 * COALESCE(j3.n, 0) / j0.n, 3) END AS oy_3
            FROM jad j0 CROSS JOIN mx
            LEFT JOIN jad j1 ON j1.k_n = j0.k_n AND j1.farq = 1
            LEFT JOIN jad j2 ON j2.k_n = j0.k_n AND j2.farq = 2
            LEFT JOIN jad j3 ON j3.k_n = j0.k_n AND j3.farq = 3
            WHERE j0.farq = 0 ORDER BY j0.k_n""")
        b2["oy"] = pd.PeriodIndex(b2["sana"], freq="M")
        b2["kohort"] = b2.groupby("mijoz_id")["oy"].transform("min")
        b2["farq"] = (b2["oy"] - b2["kohort"]).apply(lambda x: x.n)
        jad = b2.groupby(["kohort", "farq"])["mijoz_id"].nunique().unstack(fill_value=0)
        pd_oy1 = (jad[1] / jad[0]).to_numpy()[:-1]
        sql_oy1 = ret["oy_1"].to_numpy()[:-1]
        print(f"  pandas bilan oy_1 mos (0.001 aniqlikda): "
              f"{bool(np.all(np.abs(pd_oy1 - sql_oy1) < 0.001))}")

        print("\n=== 6. Xulosa (natijadan hisoblangan) ===")
        o1 = ret["oy_1"].dropna()
        print(f"  1-oy retention: o'rtacha {o1.mean():.2f}, "
              f"min {o1.min():.2f}, maks {o1.max():.2f} (to'liq kuzatilgan kohortlar)")
        print("  ⭐ Har so'rovni kichik tekshiruv bilan tasdiqlang (pandas, qo'lda hisob)")


if __name__ == "__main__":
    main()

=== 0. Jadvallar ===
  mijozlar: 300 qator, buyurtmalar: 895 qator

=== 1. JOIN turlari ===

--- INNER va LEFT JOIN: qatorlar va mijozlar soni ---
  tur  qatorlar  mijozlar
INNER       895       209
 LEFT       986       300

--- Anti-join: hech narsa sotib olmaganlar (shahar bo'yicha) ---
   shahar  xaridsiz
 Toshkent        39
  ...

=== 2. Dublikatlar: topish va o'chirish ===

--- Takror guruhlar (mijoz, sana, summa) ---
 takror_guruhlar  ortiqcha_qatorlar
              15                 15
  o'chirilgandan keyin: 880 qator (pandas drop_duplicates: 880)

=== 3. GROUP BY va HAVING ===

--- Shaharlar: kamida 30 xaridor bo'lganlari, daromad (mln so'm) ---
   shahar  xaridorlar  buyurtmalar  daromad_mln
 Toshkent         114          485         55.4
Samarqand          42          161         17.9

=== 4. Oyna funksiyalari ===

--- Har shaharda top-2 mijoz (ROW_NUMBER) ---
   shahar  mijoz_id   summa
   Buxoro       172 1239000
   Buxoro        80 1064000
 Namangan        84 1773000
 Namangan        75 1197000
Samarqand       221 1576000
Samarqand       279 1049000
  ...

=== 5. Retention: kohort (birinchi xarid oyi) va keyingi oylar ===

--- Kohortlar: faol mijozlar ulushi (NULL - hali kuzatilmagan oy) ---
 kohort  hajm  oy_1  oy_2  oy_3
2025-01    16 0.625 0.625 0.438
2025-02    45 0.711 0.622 0.622
2025-03    50 0.660 0.620 0.600
2025-04    34 0.676 0.471   NaN
2025-05    48 0.583   NaN   NaN
2025-06    16   NaN   NaN   NaN
  ...

=== 6. Xulosa (natijadan hisoblangan) ===
  1-oy retention: o'rtacha 0.65, min 0.58, maks 0.71 (to'liq kuzatilgan kohortlar)
  ⭐ Har so'rovni kichik tekshiruv bilan tasdiqlang (pandas, qo'lda hisob)

"""Jonli kodlash masalalari: pandas bilan sessiyalar, ikki son yig'indisi, top-k - murakkablik bilan."""

import heapq
from collections import Counter

import numpy as np
import pandas as pd


def sessiyalar_pandas(df, bosh=30):
    """Har foydalanuvchida 30 daqiqadan katta tanaffus - yangi sessiya."""
    df = df.sort_values(["user", "vaqt"]).copy()
    farq = df.groupby("user")["vaqt"].diff()
    df["yangi"] = farq.isna() | (farq > pd.Timedelta(minutes=bosh))
    df["sessiya"] = df.groupby("user")["yangi"].cumsum()
    return df


def sessiyalar_sikl(hodisalar, bosh=30):
    """Tekshiruv uchun oddiy Python: {user: sessiyalar soni}."""
    natija, oxirgi = {}, {}
    for u, t in sorted(hodisalar):
        if u not in oxirgi or t - oxirgi[u] > pd.Timedelta(minutes=bosh):
            natija[u] = natija.get(u, 0) + 1
        oxirgi[u] = t
    return natija


def ikki_son_sodda(a, maqsad):
    """O(n^2): barcha juftlar; qaytaradi (indekslar, taqqoslashlar soni)."""
    q = 0
    for i in range(len(a)):
        for j in range(i + 1, len(a)):
            q += 1
            if a[i] + a[j] == maqsad:
                return (i, j), q
    return None, q


def ikki_son_xesh(a, maqsad):
    """O(n): lug'atda ko'rilgan qiymatlar; qaytaradi (indekslar, qadamlar soni)."""
    korilgan = {}
    for j, x in enumerate(a):
        if maqsad - x in korilgan:
            return (korilgan[maqsad - x], j), j + 1
        korilgan[x] = j
    return None, len(a)


def top_k(sozlar, k):
    """O(n + m log k): sanash + k o'lchamli uyum (heap)."""
    return heapq.nsmallest(k, Counter(sozlar).items(), key=lambda x: (-x[1], x[0]))


def main() -> None:
    rng = np.random.default_rng(3)

    print("=== 1. pandas: sessiyalarga ajratish (30 daqiqa tanaffus) ===")
    qator, haqiqiy = [], 0
    for u in range(1, 41):
        t = pd.Timestamp("2025-03-01 08:00") + pd.Timedelta(minutes=float(rng.uniform(0, 600)))
        for _ in range(int(rng.integers(3, 12))):        # sessiyalar
            haqiqiy += 1
            for _ in range(int(rng.integers(1, 25))):    # sessiya ichidagi hodisalar
                qator.append((u, t))
                t += pd.Timedelta(minutes=float(rng.exponential(2)))
            t += pd.Timedelta(minutes=float(30 + rng.exponential(240)))
    df = pd.DataFrame(qator, columns=["user", "vaqt"]).sample(frac=1, random_state=0)
    n = len(df)
    s = sessiyalar_pandas(df)
    soni = s.groupby("user")["sessiya"].max()
    uzun = s.groupby(["user", "sessiya"])["vaqt"].agg(lambda v: (v.max() - v.min()).total_seconds() / 60)
    print(f"  hodisalar {n}, foydalanuvchilar {df['user'].nunique()}, "
          f"sessiyalar {int(soni.sum())} (generatsiyada {haqiqiy})")
    print(f"  sessiya uzunligi (daqiqa): median {uzun.median():.1f}, "
          f"90-persentil {uzun.quantile(0.9):.1f}")
    tek = sessiyalar_sikl(list(zip(df["user"], df["vaqt"])))
    print(f"  oddiy sikl bilan mos: {all(tek[u] == soni[u] for u in soni.index)}")

    print("\n=== 2. Ikki son yig'indisi: O(n^2) va O(n) ===")
    print(f"  {'n':>6} {'sodda qadam':>12} {'xesh qadam':>11} {'nisbat':>7} {'javob mos':>10}")
    qs, qx = [], []
    for n in [500, 1000, 2000, 4000]:
        a = (2 * rng.permutation(10 * n)[:n]).tolist()   # hammasi juft son
        a[-1] += 1                                 # yagona toq son -> yagona javob juftligi
        maqsad = a[-1] + a[-2]                     # eng yomon holat: juft oxirida
        j1, q1 = ikki_son_sodda(a, maqsad)
        j2, q2 = ikki_son_xesh(a, maqsad)
        mos = a[j1[0]] + a[j1[1]] == maqsad and a[j2[0]] + a[j2[1]] == maqsad
        qs.append(q1)
        qx.append(q2)
        print(f"  {n:>6} {q1:>12} {q2:>11} {q1 / q2:>7.0f} {str(mos):>10}")
    osish_s = np.exp2(np.mean(np.diff(np.log2(qs))))     # geometrik o'rtacha o'sish
    osish_x = np.exp2(np.mean(np.diff(np.log2(qx))))
    print(f"  n x2 bo'lganda qadamlar: sodda x{osish_s:.1f}, xesh x{osish_x:.1f}")

    print("\n=== 3. Top-k eng ko'p uchraydigan so'z ===")
    lugat = [f"soz{i:03d}" for i in range(300)]
    ehtimol = 1 / np.arange(1, 301)
    sozlar = rng.choice(lugat, 50_000, p=ehtimol / ehtimol.sum()).tolist()
    t = top_k(sozlar, 5)
    ts = sorted(Counter(sozlar).items(), key=lambda x: (-x[1], x[0]))[:5]
    tp = pd.Series(sozlar).value_counts()
    tp = sorted(tp.items(), key=lambda x: (-x[1], x[0]))[:5]
    print("  " + ", ".join(f"{s}:{c}" for s, c in t))
    print(f"  heap = to'liq saralash = pandas: {t == ts == tp}")

    print("\n=== 4. pandas: har mijozning oxirgi buyurtmasi va buyurtmalar orasidagi kun ===")
    b = pd.DataFrame({"mijoz": rng.integers(1, 201, 3000),
                      "sana": pd.Timestamp("2025-01-01")
                      + pd.to_timedelta(rng.integers(0, 180, 3000), unit="D"),
                      "summa": rng.integers(20, 500, 3000) * 1000})
    b = b.sort_values(["mijoz", "sana"])
    oxirgi = b.groupby("mijoz").tail(1)
    orasi = b.groupby("mijoz")["sana"].diff().dt.days
    print(f"  oxirgi buyurtmalar: {len(oxirgi)} qator (mijozlar {b['mijoz'].nunique()})")
    print(f"  buyurtmalar orasidagi kun: median {orasi.median():.0f}, "
          f"o'rtacha {orasi.mean():.1f}")
    idx = b.groupby("mijoz")["sana"].idxmax()
    print(f"  tail(1) va idxmax bir xil sanalar: "
          f"{(oxirgi.set_index('mijoz')['sana'] == b.loc[idx].set_index('mijoz')['sana']).all()}")

    print("\n=== 5. Xulosa ===")
    print("  sessiyalar: O(n log n) (saralash) + O(n); ikki son: O(n) xotira evaziga;")
    print("  top-k: O(n + m log k); har yechim oddiy versiya bilan solishtirildi")
    print("  ⭐ Avval to'g'ri va oddiy, keyin tez; murakkablikni ovoz chiqarib ayting")


if __name__ == "__main__":
    main()

=== 1. pandas: sessiyalarga ajratish (30 daqiqa tanaffus) ===
  hodisalar 3271, foydalanuvchilar 40, sessiyalar 270 (generatsiyada 270)
  sessiya uzunligi (daqiqa): median 19.8, 90-persentil 44.6
  oddiy sikl bilan mos: True

=== 2. Ikki son yig'indisi: O(n^2) va O(n) ===
       n  sodda qadam  xesh qadam  nisbat  javob mos
     500       124750         500     250       True
    1000       499500        1000     500       True
    2000      1999000        2000    1000       True
    4000      7998000        4000    2000       True
  n x2 bo'lganda qadamlar: sodda x4.0, xesh x2.0

=== 3. Top-k eng ko'p uchraydigan so'z ===
  soz000:7970, soz001:3989, soz002:2587, soz003:1987, soz004:1551
  heap = to'liq saralash = pandas: True

=== 4. pandas: har mijozning oxirgi buyurtmasi va buyurtmalar orasidagi kun ===
  oxirgi buyurtmalar: 200 qator (mijozlar 200)
  buyurtmalar orasidagi kun: median 8, o'rtacha 11.1
  tail(1) va idxmax bir xil sanalar: True

=== 5. Xulosa ===
  sessiyalar: O(n log n) (saralash) + O(n); ikki son: O(n) xotira evaziga;
  top-k: O(n + m log k); har yechim oddiy versiya bilan solishtirildi
  ⭐ Avval to'g'ri va oddiy, keyin tez; murakkablikni ovoz chiqarib ayting

"""Case study: "sotuv 10% tushdi" - ma'lumot sifati, metrika dekompozitsiyasi va segmentlar."""

import numpy as np
import pandas as pd

PLATFORMA = {"android": 0.45, "ios": 0.20, "veb": 0.35}
HUDUD = {"Toshkent": 0.45, "Samarqand": 0.20, "Farg'ona": 0.20, "Xorazm": 0.15}
YOQ = "2*SE ichida, haqiqiy tushish yo'q"
KUNLAR = pd.date_range("2025-09-01", periods=56, freq="D")   # 4 hafta oldin + 4 hafta keyin


def yarat(rng, stsenariy):
    """Kunlik jadval: sana, platforma, hudud, sessiyalar, buyurtmalar, daromad."""
    qator = []
    for i, kun in enumerate(KUNLAR):
        hafta = 1.15 if kun.dayofweek >= 5 else 1.0          # dam olish kunlari ko'proq
        for p, pu in PLATFORMA.items():
            for h, hu in HUDUD.items():
                ses = rng.poisson(40_000 * pu * hu * hafta)
                konv = {"android": 0.030, "ios": 0.036, "veb": 0.025}[p]
                if stsenariy == "segment" and p == "android" and i >= 28:
                    konv *= 0.78                               # yangi versiyadagi xato
                buy = rng.binomial(ses, konv)
                chek = rng.normal(185_000, 4_000)
                qator.append((kun, p, h, ses, buy, buy * chek))
    df = pd.DataFrame(qator, columns=["sana", "platforma", "hudud", "sessiyalar",
                                      "buyurtmalar", "daromad"])
    if stsenariy == "malumot":                                  # yuklash quvuri buzildi
        yoq = (df["platforma"] == "veb") & (df["hudud"] == "Toshkent") & \
              (df["sana"] >= KUNLAR[38])
        df = df[~yoq]
    return df


def tahlil(df):
    davr = np.where(df["sana"] >= KUNLAR[28], "keyin", "oldin")
    df = df.assign(davr=davr)
    x = {}
    j = df.groupby("davr")[["sessiyalar", "buyurtmalar", "daromad"]].sum()
    tushish = j.loc["keyin", "daromad"] / j.loc["oldin", "daromad"] - 1
    print(f"  daromad o'zgarishi: {tushish:+.1%}")

    print("  [1] ma'lumot to'liqligi: kunlik qatorlar soni (kutilgan 12)")
    qatorlar = df.groupby("sana").size()
    kam = qatorlar[qatorlar < 12]
    x["toliq_emas"] = len(kam)
    if len(kam):
        print(f"      {len(kam)} kunda qatorlar kam: {kam.index.min():%m-%d} dan "
              f"{kam.index.max():%m-%d} gacha, {int(kam.min())} ta")
        yoq = (df[df["davr"] == "oldin"].groupby(["platforma", "hudud"]).size().index
               .difference(df[df["sana"] == kam.index.max()]
                           .set_index(["platforma", "hudud"]).index))
        print(f"      yo'qolgan segmentlar: {', '.join('/'.join(s) for s in yoq)}")
        ichida = ~df.set_index(["platforma", "hudud"]).index.isin(yoq)
        kunlik = df[ichida].groupby(["davr", "sana"])["daromad"].sum()
        a, b = kunlik["oldin"], kunlik["keyin"]
        x["solishtirma"] = b.mean() / a.mean() - 1
        x["se"] = np.sqrt(a.var() / len(a) + b.var() / len(b)) / a.mean()
        print(f"      yo'qolgan segmentlarsiz (like-for-like): {x['solishtirma']:+.1%} "
              f"(SE {x['se']:.1%})")
    else:
        print("      hamma kunlar to'liq")

    print("  [2] dekompozitsiya: daromad = sessiyalar * konversiya * o'rtacha chek")
    o, k = j.loc["oldin"], j.loc["keyin"]
    qism = {"sessiyalar": k["sessiyalar"] / o["sessiyalar"],
            "konversiya": (k["buyurtmalar"] / k["sessiyalar"]) / (o["buyurtmalar"] / o["sessiyalar"]),
            "o'rtacha chek": (k["daromad"] / k["buyurtmalar"]) / (o["daromad"] / o["buyurtmalar"])}
    jami_log = np.log(k["daromad"] / o["daromad"])
    for nom, r in qism.items():
        print(f"      {nom:<14} {r - 1:+.1%}  (log-hissa {np.log(r) / jami_log:.0%})")
    x["asosiy"] = max(qism, key=lambda q: abs(np.log(qism[q])))

    print("  [3] segmentlar: daromad o'zgarishiga hissa")
    for ust in ["platforma", "hudud"]:
        s = df.pivot_table(index=ust, columns="davr", values="daromad", aggfunc="sum")
        s["hissa"] = (s["keyin"] - s["oldin"]) / (o["daromad"])
        s["ozgarish"] = s["keyin"] / s["oldin"] - 1
        satr = ", ".join(f"{i} {r['ozgarish']:+.0%} (hissa {r['hissa']:+.1%})"
                         for i, r in s.sort_values("hissa").iterrows())
        print(f"      {ust:<9}: {satr}")
        x[ust] = s["hissa"].idxmin(), s["hissa"].min() / tushish
    return x


def main() -> None:
    rng = np.random.default_rng(10)
    for nom, st in [("A", "segment"), ("B", "malumot")]:
        print(f"=== Stsenariy {nom}: 'oxirgi 4 haftada sotuv tushdi' ===")
        x = tahlil(yarat(rng, st))
        print("  [4] xulosa (natijadan):")
        if x["toliq_emas"] > 0:
            print("      avval MA'LUMOT XATOSI: segment qatorlari yo'qolgan - yuklash")
            print("      quvurini tekshiring; biznes xulosasi keyin")
            print(f"      qolgan segmentlarda o'zgarish {x['solishtirma']:+.1%} - "
                  f"{YOQ if abs(x['solishtirma']) < 2 * x['se'] else 'tushish bor'}")
            print(f"      [1] bo'lmasa xato xulosa: '{x['asosiy']} tushdi, "
                  f"{x['hudud'][0]} {x['platforma'][0]}' - marketingga murojaat")
        else:
            seg, ulush = max([x["platforma"], x["hudud"]], key=lambda v: v[1])
            print(f"      ma'lumot to'liq; asosiy omil - {x['asosiy']}; tushishning "
                  f"{ulush:.0%} i '{seg}' segmentida")
        print()
    print("⭐ Tartib: ma'lumotni tekshir -> metrikani yoy -> segmentlarga bo'l -> gipoteza")


if __name__ == "__main__":
    main()

=== Stsenariy A: 'oxirgi 4 haftada sotuv tushdi' ===
  daromad o'zgarishi: -10.0%
  [1] ma'lumot to'liqligi: kunlik qatorlar soni (kutilgan 12)
      hamma kunlar to'liq
  [2] dekompozitsiya: daromad = sessiyalar * konversiya * o'rtacha chek
      sessiyalar     -0.1%  (log-hissa 1%)
      konversiya     -10.0%  (log-hissa 100%)
      o'rtacha chek  +0.1%  (log-hissa -1%)
  [3] segmentlar: daromad o'zgarishiga hissa
      platforma: android -22% (hissa -10.2%), veb -1% (hissa -0.2%), ios +2% (hissa +0.4%)
      hudud    : Toshkent -9% (hissa -4.0%), Samarqand -11% (hissa -2.2%), Farg'ona -10% (hissa -2.1%), Xorazm -11% (hissa -1.7%)
  [4] xulosa (natijadan):
      ma'lumot to'liq; asosiy omil - konversiya; tushishning 102% i 'android' segmentida

=== Stsenariy B: 'oxirgi 4 haftada sotuv tushdi' ===
  daromad o'zgarishi: -9.5%
  [1] ma'lumot to'liqligi: kunlik qatorlar soni (kutilgan 12)
      18 kunda qatorlar kam: 10-09 dan 10-26 gacha, 11 ta
      yo'qolgan segmentlar: veb/Toshkent
      yo'qolgan segmentlarsiz (like-for-like): -0.6% (SE 1.9%)
  [2] dekompozitsiya: daromad = sessiyalar * konversiya * o'rtacha chek
      sessiyalar     -10.3%  (log-hissa 110%)
      konversiya     +0.8%  (log-hissa -8%)
      o'rtacha chek  +0.2%  (log-hissa -2%)
  [3] segmentlar: daromad o'zgarishiga hissa
      platforma: veb -31% (hissa -9.3%), ios -1% (hissa -0.3%), android +0% (hissa +0.2%)
      hudud    : Toshkent -21% (hissa -9.4%), Farg'ona -3% (hissa -0.6%), Samarqand +0% (hissa +0.1%), Xorazm +3% (hissa +0.5%)
  [4] xulosa (natijadan):
      avval MA'LUMOT XATOSI: segment qatorlari yo'qolgan - yuklash
      quvurini tekshiring; biznes xulosasi keyin
      qolgan segmentlarda o'zgarish -0.6% - 2*SE ichida, haqiqiy tushish yo'q
      [1] bo'lmasa xato xulosa: 'sessiyalar tushdi, Toshkent veb' - marketingga murojaat

⭐ Tartib: ma'lumotni tekshir -> metrikani yoy -> segmentlarga bo'l -> gipoteza

X_top = SelectKBest(k=20).fit_transform(X, y); cross_val_score(model, X_top, y)   # ⚠️
cross_val_score(make_pipeline(SelectKBest(k=20), model), X, y)                    # ✅

print(accuracy_score(y, model.predict(X)))                                        # ⚠️
print(average_precision_score(y, model.predict_proba(X)[:, 1]), y.mean())         # ✅

SELECT m.id, b.summa FROM mijozlar m LEFT JOIN buyurtmalar b ON b.mijoz_id = m.id
WHERE b.sana >= '2025-06-01';                                  -- ⚠️ INNER ga aylandi
SELECT m.id, b.summa FROM mijozlar m LEFT JOIN buyurtmalar b
  ON b.mijoz_id = m.id AND b.sana >= '2025-06-01';             -- ✅ shart ON da

ROW_NUMBER() OVER (PARTITION BY shahar ORDER BY summa DESC)            -- ⚠️ tasodifiy tartib
ROW_NUMBER() OVER (PARTITION BY shahar ORDER BY summa DESC, mijoz_id)  -- ✅ deterministik

ROUND(1.0 * COUNT(DISTINCT CASE WHEN farq = 3 THEN id END) / hajm, 2)   -- ⚠️ 0 ko'rsatadi
CASE WHEN kohort_n + 3 <= oxirgi_oy THEN ... END                        -- ✅ NULL

# ⚠️ jim o'ylab, murakkab yechimni birdaniga yozish va test qilmaslik
# ✅ "Avval O(n^2) oddiy yechim, keyin lug'at bilan O(n); ikkalasini solishtiraman"

xulosa = "trafik tushdi, marketing kerak"                                          # ⚠️
qatorlar = df.groupby("sana").size(); assert (qatorlar == kutilgan).all()          # ✅ avval

1.  # Ridge (alpha=10) nechta koeffitsientni aynan nol qiladi?
2.  # 9-darajali polinom (n=30): bias^2 yoki variance kattami?
3.  # musbat 2.5%: tasodifiy modelning PR-AUC si qancha?
4.  # tasodifiy y, belgi tanlash CV dan oldin: accuracy 0.5 ga yaqinmi?
5.  # chiziqli signalda boosting chiziqli modelni yengadimi?
6.  # 300 mijoz, 209 tasi xarid qilgan: LEFT JOIN qatorlari 300 mi?
7.  # ROW_NUMBER va RANK: tenglikda farqi?
8.  # oxirgi kohortning 3-oy retention i - 0 mi?
9.  # ikki son yig'indisi: n ikki barobar - sodda yechim qadamlari necha barobar?
10. # "sotuv 10% tushdi": birinchi qadam?