Data science-এর জন্য SQL — window function, cohort আর funnel

CREATE VIEW ov AS
SELECT o.order_id, o.customer_id,
       datetime(o.order_ts, '+6 hours') AS ts_dhaka,
       SUM(i.qty * i.unit_price)        AS value
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'delivered'
GROUP BY o.order_id;

-- এক গ্রাহকের ইতিহাস: কততম অর্ডার, চলমান খরচ, নিজের অর্ডারের মধ্যে ক্রম
SELECT order_id, ts_dhaka, value,
       ROW_NUMBER() OVER w                                       AS nth,
       SUM(value)   OVER w                                       AS running_spend,
       RANK() OVER (PARTITION BY customer_id ORDER BY value DESC) AS value_rank
FROM ov
WHERE customer_id = 87
WINDOW w AS (PARTITION BY customer_id ORDER BY ts_dhaka
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY ts_dhaka;

-- সব গ্রাহকের মোট খরচের ক্রম আর quartile
SELECT customer_id, SUM(value) AS total,
       RANK()   OVER (ORDER BY SUM(value) DESC) AS rnk,
       NTILE(4) OVER (ORDER BY SUM(value) DESC) AS quartile
FROM ov GROUP BY customer_id
ORDER BY rnk LIMIT 6;

CREATE VIEW g AS
SELECT customer_id, order_id, order_ts,
       ROW_NUMBER() OVER w AS nth,
       ROUND(julianday(order_ts) - julianday(LAG(order_ts) OVER w), 1) AS gap_days,
       LEAD(order_id) OVER w AS next_order
FROM orders
WHERE status = 'delivered'
WINDOW w AS (PARTITION BY customer_id ORDER BY order_ts);

SELECT * FROM g WHERE customer_id = 18;

-- পুনরায় কেনা: প্রথম থেকে দ্বিতীয় অর্ডারের ফাঁক
SELECT COUNT(DISTINCT customer_id)                     AS buyers,
       SUM(nth = 2)                                    AS repeaters,
       SUM(nth = 2 AND gap_days <= 30)                 AS repeat_30d,
       ROUND(AVG(CASE WHEN nth = 2 THEN gap_days END), 1) AS avg_gap_1_2,
       ROUND(AVG(gap_days), 1)                         AS avg_gap_all
FROM g;

WITH RECURSIVE cal(d) AS (
  SELECT '2026-06-01'
  UNION ALL SELECT date(d, '+1 day') FROM cal WHERE d < '2026-06-30'),
daily AS (                                   -- ঢাকার তারিখে গোনা
  SELECT date(order_ts, '+6 hours') AS d, COUNT(*) AS n
  FROM orders GROUP BY 1),
utc AS (SELECT date(order_ts) AS d, COUNT(*) AS n_utc FROM orders GROUP BY 1),
filled AS (                                  -- অর্ডার নেই এমন দিনেও একটি সারি
  SELECT cal.d, COALESCE(daily.n, 0) AS n, COALESCE(utc.n_utc, 0) AS n_utc
  FROM cal LEFT JOIN daily ON daily.d = cal.d LEFT JOIN utc ON utc.d = cal.d)
SELECT d, n, n_utc,
       ROUND(AVG(n) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma7,
       COUNT(*) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)     AS days_used
FROM filled ORDER BY d;

-- ভুল পথ: খালি দিন বাদ পড়লে "শেষ ৭ সারি" আর "শেষ ৭ দিন" এক নয়
SELECT d, n,
       ROUND(AVG(n) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma7_rows
FROM (SELECT date(order_ts, '+6 hours') AS d, COUNT(*) AS n FROM orders
      WHERE order_ts >= '2026-05-31 18:00:00' GROUP BY 1)
ORDER BY d;

CREATE VIEW r AS
SELECT *, ROW_NUMBER() OVER (
         PARTITION BY customer_id, event_type, event_ts   -- একই ঘটনা
         ORDER BY event_id) AS rn                         -- প্রথমবার পাঠানোটি রাখি
FROM events;

-- কোন সারিগুলো দ্বিতীয়বার এসেছে?
SELECT event_id, customer_id, event_ts, event_type, rn
FROM r WHERE rn > 1 ORDER BY event_id;

-- DISTINCT * কেন কাজ করে না: event_id প্রতিটি সারিতে আলাদা
SELECT (SELECT COUNT(*) FROM events)                       AS raw_rows,
       (SELECT COUNT(*) FROM (SELECT DISTINCT * FROM events)) AS distinct_rows,
       (SELECT COUNT(*) FROM r WHERE rn = 1)                AS dedup_rows;

-- ধাপভিত্তিক গোনা: paid-এর পাশে আসল অর্ডারের সংখ্যা
SELECT event_type, COUNT(*) AS raw_rows, SUM(rn = 1) AS dedup_rows
FROM r GROUP BY event_type ORDER BY raw_rows DESC;
SELECT COUNT(*) AS june_orders FROM orders
WHERE order_ts >= '2026-05-31 18:00:00';   -- ঢাকার ১ জুন ০০:০০ = UTC-র ৩১ মে ১৮:০০

CREATE VIEW e AS                       -- আগের এডিটরের মতো, পুনরাবৃত্তি বাদ
SELECT customer_id, event_ts, event_type FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id, event_type, event_ts
                               ORDER BY event_id) AS rn FROM events)
WHERE rn = 1;

CREATE VIEW s AS
SELECT *, SUM(CASE WHEN gap_min IS NULL OR gap_min > 30 THEN 1 ELSE 0 END)
          OVER (PARTITION BY customer_id ORDER BY event_ts
                ROWS UNBOUNDED PRECEDING) AS session_no
FROM (SELECT *, ROUND((julianday(event_ts) - julianday(LAG(event_ts)
          OVER (PARTITION BY customer_id ORDER BY event_ts))) * 1440, 1) AS gap_min
      FROM e);

-- এক গ্রাহকের ঘটনা আর তাদের session
SELECT event_ts, event_type, gap_min, session_no FROM s WHERE customer_id = 7;

-- সব session-এর সারাংশ
SELECT COUNT(*) AS sessions, ROUND(AVG(n_events), 2) AS avg_events,
       SUM(has_paid) AS paid_sessions,
       ROUND(AVG(minutes), 1) AS avg_minutes
FROM (SELECT customer_id, session_no, COUNT(*) AS n_events,
             MAX(event_type = 'paid') AS has_paid,
             (julianday(MAX(event_ts)) - julianday(MIN(event_ts))) * 1440 AS minutes
      FROM s GROUP BY customer_id, session_no);

CREATE VIEW f AS                       -- প্রতি গ্রাহক: প্রতিটি ধাপের প্রথম সময়
SELECT customer_id,
       MIN(CASE WHEN event_type = 'view'     THEN event_ts END) AS t_view,
       MIN(CASE WHEN event_type = 'cart'     THEN event_ts END) AS t_cart,
       MIN(CASE WHEN event_type = 'checkout' THEN event_ts END) AS t_check,
       MIN(CASE WHEN event_type = 'paid'     THEN event_ts END) AS t_paid
FROM events GROUP BY customer_id;

WITH steps AS (                        -- ক্রম মেনে পৌঁছানো গ্রাহক
  SELECT 1 AS step, 'view' AS name, COUNT(t_view) AS users FROM f
  UNION ALL SELECT 2, 'cart', COUNT(*) FROM f WHERE t_cart >= t_view
  UNION ALL SELECT 3, 'checkout', COUNT(*) FROM f
            WHERE t_check >= t_cart AND t_cart >= t_view
  UNION ALL SELECT 4, 'paid', COUNT(*) FROM f
            WHERE t_paid >= t_check AND t_check >= t_cart AND t_cart >= t_view)
SELECT step, name, users,
       ROUND(100.0 * users / LAG(users) OVER (ORDER BY step), 1)         AS step_pct,
       ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step), 1) AS overall_pct
FROM steps ORDER BY step;

-- ধাপের মাঝের সময় (যাঁরা টাকা দিয়েছেন), মিনিটে
SELECT COUNT(*) AS payers,
       ROUND(AVG((julianday(t_check) - julianday(t_cart)) * 1440), 1) AS cart_to_check,
       ROUND(AVG((julianday(t_paid) - julianday(t_check)) * 1440), 1) AS check_to_paid,
       ROUND(AVG((julianday(t_paid) - julianday(t_view)) * 24), 1)    AS view_to_paid_h
FROM f WHERE t_paid IS NOT NULL;

CREATE VIEW act AS                     -- কে কোন মাসে কিনেছেন (ঢাকার সময়ে)
SELECT DISTINCT c.customer_id, substr(c.signup_date, 1, 7) AS cohort,
       (CAST(strftime('%Y', o.order_ts, '+6 hours') AS INTEGER)
          - CAST(substr(c.signup_date, 1, 4) AS INTEGER)) * 12
     + CAST(strftime('%m', o.order_ts, '+6 hours') AS INTEGER)
          - CAST(substr(c.signup_date, 6, 2) AS INTEGER) AS m
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered';

WITH size AS (SELECT substr(signup_date, 1, 7) AS cohort, COUNT(*) AS n
              FROM customers GROUP BY 1)
SELECT s.cohort, s.n,
  ROUND(100.0 * SUM(a.m = 0) / s.n, 1) AS m0,
  ROUND(100.0 * SUM(a.m = 1) / s.n, 1) AS m1,
  ROUND(100.0 * SUM(a.m = 2) / s.n, 1) AS m2,
  CASE WHEN date(s.cohort || '-01', '+3 months') < '2026-07-01'   -- মাসটি কি
       THEN ROUND(100.0 * SUM(a.m = 3) / s.n, 1) END AS m3,       -- দেখা হয়েছে?
  CASE WHEN date(s.cohort || '-01', '+4 months') < '2026-07-01'
       THEN ROUND(100.0 * SUM(a.m = 4) / s.n, 1) END AS m4
FROM size s LEFT JOIN act a ON a.cohort = s.cohort
GROUP BY s.cohort ORDER BY s.cohort;

CREATE VIEW v AS                       -- পরপর দুই অর্ডারের মাঝের দিন
SELECT x FROM (
  SELECT julianday(order_ts) - julianday(LAG(order_ts) OVER (
           PARTITION BY customer_id ORDER BY order_ts)) AS x
  FROM orders WHERE status = 'delivered')
WHERE x IS NOT NULL;

-- PERCENTILE_CONT-এর মতো: সাজানো ক্রমে দুই প্রতিবেশীর মাঝে রৈখিক মিশ্রণ
WITH r AS (SELECT x, ROW_NUMBER() OVER (ORDER BY x) AS rn,
                  COUNT(*) OVER () AS n FROM v),
q AS (SELECT 0.5 AS p UNION ALL SELECT 0.9),
h AS (SELECT p, 1 + (n - 1) * p AS pos FROM q, (SELECT MAX(n) AS n FROM r))
SELECT h.p, ROUND(h.pos, 2) AS pos, ROUND(lo.x, 2) AS x_lo, ROUND(hi.x, 2) AS x_hi,
       ROUND(lo.x + (h.pos - CAST(h.pos AS INTEGER)) * (hi.x - lo.x), 2) AS pct_cont
FROM h
JOIN r lo ON lo.rn = CAST(h.pos AS INTEGER)
JOIN r hi ON hi.rn = CAST(h.pos AS INTEGER) + 1;

-- গড়ের পাশে median: median() শুধু এই পাতার sql.js-এর বাড়তি ফাংশন
SELECT COUNT(*) AS n, ROUND(AVG(x), 2) AS mean_gap, ROUND(median(x), 2) AS median_gap FROM v;
-- NTILE(4): সারি গুনে চারটি প্রায় সমান ভাগ
SELECT quartile, COUNT(*) AS n, ROUND(MIN(x), 1) AS lo, ROUND(MAX(x), 1) AS hi
FROM (SELECT x, NTILE(4) OVER (ORDER BY x) AS quartile FROM v)
GROUP BY quartile;

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY x) AS median_gap,
       percentile_cont(0.9) WITHIN GROUP (ORDER BY x) AS p90_gap,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY x) AS median_disc
FROM v;

-- ভুল: অর্ডার-স্তরের delivery_fee প্রতিটি item-সারিতে একবার করে যোগ হয়
SELECT COUNT(*)                   AS joined_rows,
       SUM(o.delivery_fee)        AS fee_total,
       SUM(i.qty * i.unit_price)  AS items_total
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'delivered';

-- ঠিক: আগে item-কে অর্ডার-স্তরে গুটিয়ে নিন, তারপর join
WITH it AS (SELECT order_id, SUM(qty * unit_price) AS items
            FROM order_items GROUP BY order_id)
SELECT COUNT(*)            AS orders,
       SUM(o.delivery_fee) AS fee_total,
       SUM(it.items)       AS items_total
FROM orders o JOIN it ON it.order_id = o.order_id
WHERE o.status = 'delivered';

-- NULL আর গড়: AVG শুধু non-NULL মান গোনে
SELECT COUNT(*) AS orders, COUNT(discount) AS with_coupon,
       ROUND(AVG(discount), 1)              AS avg_if_used,
       ROUND(AVG(COALESCE(discount, 0)), 1) AS avg_per_order
FROM orders WHERE status = 'delivered';

import sqlite3
import pandas as pd

con = sqlite3.connect(":memory:")              # ব্রাউজারের Python-এর ভেতরে SQLite
con.executescript(open("bazar.sql", encoding="utf-8").read())
print("SQLite", sqlite3.sqlite_version)

customers = pd.read_sql("SELECT * FROM customers", con)
orders = pd.read_sql("SELECT * FROM orders", con)
events = pd.read_sql("SELECT * FROM events ORDER BY event_id", con)
print("সারি:", len(customers), len(orders), len(events))

sql_cohort = pd.read_sql("""
WITH act AS (
  SELECT DISTINCT c.customer_id, substr(c.signup_date, 1, 7) AS cohort,
    (CAST(strftime('%Y', o.order_ts, '+6 hours') AS INTEGER)
       - CAST(substr(c.signup_date, 1, 4) AS INTEGER)) * 12
    + CAST(strftime('%m', o.order_ts, '+6 hours') AS INTEGER)
       - CAST(substr(c.signup_date, 6, 2) AS INTEGER) AS m
  FROM customers c JOIN orders o ON o.customer_id = c.customer_id
  WHERE o.status = 'delivered')
SELECT a.cohort, a.m, COUNT(*) AS active, s.n
FROM act a JOIN (SELECT substr(signup_date, 1, 7) AS cohort, COUNT(*) AS n
                 FROM customers GROUP BY 1) s ON s.cohort = a.cohort
GROUP BY a.cohort, a.m""", con)
print(sql_cohort.head(3).to_string(index=False))
sql_tab = (100 * sql_cohort.active / sql_cohort.n).round(1).to_frame("pct") \
    .join(sql_cohort[["cohort", "m"]]).pivot(index="cohort", columns="m", values="pct")
print(sql_tab)

import tzdata                                   # পাতার Python-এ সময়-অঞ্চলের তথ্য
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

o = orders[orders.status == "delivered"].copy()
o["ts"] = pd.to_datetime(o.order_ts).dt.tz_localize("UTC").dt.tz_convert("Asia/Dhaka")
d = o.merge(customers, on="customer_id")
sd = pd.to_datetime(d.signup_date)
d["cohort"] = d.signup_date.str[:7]
d["m"] = (d.ts.dt.year - sd.dt.year) * 12 + d.ts.dt.month - sd.dt.month

size = customers.groupby(customers.signup_date.str[:7]).size()
active = d.groupby(["cohort", "m"]).customer_id.nunique().unstack()
pd_tab = (100 * active.div(size, axis=0)).round(1)
last_m = {c: 6 - int(c[5:]) for c in pd_tab.index}   # জুন পর্যন্ত কত মাস দেখা গেছে
for c, k in last_m.items():                            # দেখা মাসে কেনা নেই = 0, না-দেখা = NaN
    pd_tab.loc[c, [m for m in pd_tab.columns if m <= k]] = pd_tab.loc[c].fillna(0)
print(pd_tab)
print("SQL আর pandas মিলেছে:", np.allclose(pd_tab, sql_tab, equal_nan=True))

fig, ax = plt.subplots(figsize=(6.4, 3.2))
im = ax.imshow(pd_tab.values, cmap="Blues", vmin=0, vmax=100)
for i in range(pd_tab.shape[0]):
    for j in range(pd_tab.shape[1]):
        v = pd_tab.values[i, j]
        if not np.isnan(v):
            ax.text(j, i, f"{v:.0f}%", ha="center", va="center", color="white" if v > 60 else "black")
ax.set_xticks(range(pd_tab.shape[1]), [f"M{m}" for m in pd_tab.columns])
ax.set_yticks(range(pd_tab.shape[0]), pd_tab.index)
ax.set_xlabel("Months since signup month"); ax.set_ylabel("Signup cohort")
ax.set_title("Monthly retention (% of cohort who ordered)")
fig.colorbar(im, ax=ax, label="% active")
fig.subplots_adjust(left=0.18, right=0.95, bottom=0.18, top=0.88)

import pandas as pd

ev = events.drop_duplicates(["customer_id", "event_type", "event_ts"])   # প্রথমটি থাকে
ev = ev.assign(t=pd.to_datetime(ev.event_ts))
first = ev.pivot_table(index="customer_id", columns="event_type", values="t", aggfunc="min")
view = first["view"].notna()
cart = view & (first["cart"] >= first["view"])
check = cart & (first["checkout"] >= first["cart"])
paid = check & (first["paid"] >= first["checkout"])
users = pd.Series([view.sum(), cart.sum(), check.sum(), paid.sum()],
                  index=["view", "cart", "checkout", "paid"])
print(pd.DataFrame({"users": users,
                    "step_pct": (100 * users / users.shift()).round(1),
                    "overall_pct": (100 * users / users.iloc[0]).round(1)}))

ev = ev.sort_values(["customer_id", "t"])
gap = ev.groupby("customer_id").t.diff()
new = gap.isna() | (gap > pd.Timedelta(minutes=30))     # নতুন session শুরু
ev["session_no"] = new.astype(int).groupby(ev.customer_id).cumsum()
print("session:", ev.groupby(["customer_id", "session_no"]).ngroups)

import pandas as pd

cut = "2026-05-31 18:00:00"          # ঢাকার ১ জুন ০০:০০, UTC-তে
feat = pd.read_sql("""
WITH o AS (
  SELECT customer_id, order_ts,
         julianday(order_ts) - julianday(LAG(order_ts) OVER (
           PARTITION BY customer_id ORDER BY order_ts)) AS gap
  FROM orders WHERE status = 'delivered' AND order_ts < :cut)
SELECT c.customer_id, c.district,
       COUNT(o.order_ts)                                         AS frequency,
       ROUND(julianday(:cut) - julianday(MAX(o.order_ts)), 1)    AS recency_days,
       ROUND(AVG(o.gap), 1)                                      AS avg_gap,
       EXISTS (SELECT 1 FROM orders x WHERE x.customer_id = c.customer_id
               AND x.order_ts >= :cut)                           AS bought_june
FROM customers c LEFT JOIN o ON o.customer_id = c.customer_id
GROUP BY c.customer_id""", con, params={"cut": cut})
print(feat.head(4).to_string(index=False))
print(feat.dtypes.to_dict())

feat["recency_band"] = pd.cut(feat.recency_days, [0, 14, 30, 60, 200],
                              labels=["0-14", "15-30", "31-60", "60+"])
print(feat.groupby("recency_band", observed=True).bought_june.agg(["size", "mean"]).round(3))
print("কখনো কেনেননি:", feat.recency_days.isna().sum(),
      "| তাঁদের জুনে কেনার হার:", feat[feat.recency_days.isna()].bought_june.mean().round(3))