GROUP BY, HAVING ও সংখ্যান

"Average" — পরিসংখ্যানের সবচেয়ে অপব্যবহৃত metric।

(১) Outlier sensitivity:

• ৯৯ জনের আয় ৩০,০০০ + ১ জনের আয় ১ কোটি → AVG ১,২৯,৭০০ — বাস্তব representative নয়।
• AVG income reporting-এ — distortion বিশাল।
• Median (middle value) সাধারণত better।

(২) Simpson's paradox:

• প্রতি subgroup-এ A > B, কিন্তু overall B > A — counterintuitive।
• Hospital A ও B — A সব patient-এ better recovery rate, কিন্তু overall B better — কারণ A বেশি serious case নেয়।

(৩) AVG of ratios vs ratio of AVGs:

• "Average conversion rate" — তিন store-এ 5%, 10%, 50% → AVG 21.67%।
• কিন্তু সঠিক "overall conversion" = total_conversion / total_visit — হয়তো 8%।
• Weighted average দরকার, simple AVG ভুল।

(৪) NULL exclusion:

• "AVG response time" — যেসব ticket এখনো response পায়নি (NULL) — counted হয় না।
• Apparent AVG ভাল লাগে; reality খারাপ।

(৫) Categorical mean nonsense:

• "Average district code" বা "average category" — অর্থহীন।
• Numeric encoding-এর ফাঁদ।

(৬) Time aggregation distortion:

• Hourly AVG → daily AVG-এ pattern হারায়।
• Peak hour signal smoothed out।

(৭) Skewed distribution:

• Income, transaction amount — log-normal distribution।
• AVG > median সাধারণত — "typical" বলতে median বেশি honest।

Better metrics depending on context:

• Median: robust to outlier।
• Percentile: P50, P90, P99 — distribution shape।
• Trimmed mean: top & bottom 5% drop, then AVG।
• Geometric mean: growth rate-এ।
• Weighted average: different group size থাকলে।

Bangladesh examples:

• "Average income in Dhaka" — ৬০,০০০ BDT, কিন্তু median ৩০,০০০। AVG misleading।
• "Average bKash transaction" — small daily transactions + occasional huge ones।
• "Average rider rating in Pathao" — 4.8/5 (most ratings 5)। median = 5; AVG smaller picture।

Best practice — always show distribution:

• Histogram, box plot, percentile।
• Single AVG number-এ সিদ্ধান্ত avoid।
• "Mean ± std" + median + P90 — comprehensive picture।

মূল উপলব্ধি: AVG-এ blind trust danger। যেকোনো AVG report-এ — "distribution shape কী, outlier আছে কি?" — এই প্রশ্ন reflexive করে নিন। Senior analyst-রা AVG দিয়ে বিচার করেন না, distribution দিয়ে।

Top-N per group — interview question hall of fame।

কেন GROUP BY যথেষ্ট নয়:

• GROUP BY collapse করে — প্রতি group থেকে এক row।
• "Top-৩" — প্রতি group থেকে ৩টি row লাগবে।
• Aggregate function single value return — list নয়।

Approach 1 — correlated subquery (slow but understandable):

SELECT category, product_name, sales
FROM products p1
WHERE 3 > (
    SELECT COUNT(*)
    FROM products p2
    WHERE p2.category = p1.category
      AND p2.sales > p1.sales
)
ORDER BY category, sales DESC;

Performance বাজে — প্রতিটি row-এ subquery।

Approach 2 — JOIN with rank (older DB):

SELECT p.category, p.product_name, p.sales
FROM products p
INNER JOIN products p2
    ON p.category = p2.category
   AND p2.sales >= p.sales
GROUP BY p.category, p.product_name, p.sales
HAVING COUNT(*) <= 3
ORDER BY p.category, p.sales DESC;

Self-JOIN, expensive। Quadratic complexity।

Approach 3 — window function (modern, preferred):

SELECT category, product_name, sales
FROM (
    SELECT
        category,
        product_name,
        sales,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
    FROM products
) ranked
WHERE rn <= 3
ORDER BY category, sales DESC;

Single pass, O(n log n) sort — দ্রুত ও clean।

Tie handling — ROW_NUMBER vs RANK vs DENSE_RANK:

• ROW_NUMBER: 1, 2, 3 — duplicates arbitrary ordered।
• RANK: 1, 2, 2, 4 — same value same rank, gap।
• DENSE_RANK: 1, 2, 2, 3 — same value same rank, no gap।

ব্যবহার অনুযায়ী:

• "Top-3 strict" → ROW_NUMBER।
• "All top-3 including ties" → RANK ≤ 3।

PostgreSQL extension — LATERAL JOIN:

SELECT c.category, p.product_name, p.sales
FROM (SELECT DISTINCT category FROM products) c
CROSS JOIN LATERAL (
    SELECT product_name, sales
    FROM products
    WHERE category = c.category
    ORDER BY sales DESC
    LIMIT 3
) p;

Per-group LIMIT — সবচেয়ে readable, performance ভাল।

Real-world scaling:

• ১ লক্ষ category × ১০ লক্ষ product → window function partition হাজার million row।
• Pre-aggregation দরকার — daily snapshot table।
• Specialized OLAP database (ClickHouse, Snowflake) এ optimal।

মূল উপলব্ধি: Top-N per group — SQL-এর "rite of passage"। Window function না জানলে এই প্রশ্ন painful। আজকের data analyst-এর জন্য window function essential — পরের পাঠের topic।

Cohort analysis — Daraz, bKash, Pathao সব analytics team-এর basic competency।

Cohort-এর ধারণা:

• "যারা একই সময়ে join করেছেন তাদের একসাথে track।"
• প্রতিটি cohort-এর retention pattern আলাদা — marketing campaign, product change-এর effect বের হয়।

Step 1 — Cohort assignment:

SELECT
    customer_id,
    DATE_TRUNC('month', MIN(order_date)) AS cohort_month
FROM orders
GROUP BY customer_id;
-- প্রতিটি customer-এর "first order month" → cohort

Step 2 — Activity tracking:

WITH cohort AS (
    SELECT customer_id,
           DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),
activity AS (
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', o.order_date) AS activity_month,
        COUNT(DISTINCT o.customer_id) AS active_customers
    FROM orders o
    JOIN cohort c ON o.customer_id = c.customer_id
    GROUP BY c.cohort_month, DATE_TRUNC('month', o.order_date)
)
SELECT
    cohort_month,
    activity_month,
    active_customers,
    EXTRACT(MONTH FROM AGE(activity_month, cohort_month)) AS months_after
FROM activity
ORDER BY cohort_month, activity_month;

Step 3 — Retention rate:

WITH cohort_size AS (
    SELECT cohort_month, COUNT(*) AS size
    FROM cohort
    GROUP BY cohort_month
)
SELECT
    a.cohort_month,
    a.months_after,
    a.active_customers,
    cs.size,
    ROUND(100.0 * a.active_customers / cs.size, 2) AS retention_pct
FROM activity_with_lag a
JOIN cohort_size cs ON cs.cohort_month = a.cohort_month;

Output (heatmap-ready):

cohort_month   m0     m1     m2     m3
2024-01      100%   45%    32%    25%
2024-02      100%   52%    38%    --
2024-03      100%   48%    --     --

Insights from cohort:

• Newer cohort retention better → product improving।
• Specific month sharp drop → bug, competitor event, seasonality।
• Cohort size varying → marketing effect।

Aggregation patterns used:

• COUNT DISTINCT — unique customer।
• GROUP BY multi-column — cohort × activity month।
• JOIN to cohort dimension।
• Date arithmetic — DATE_TRUNC, AGE।
• CTE chain — readability।

Bangladesh business-এ application:

• bKash agent retention — কোন intake batch-এর agent best survival?
• Pathao rider — different city-এর rider lifetime ভিন্ন কিনা।
• Daraz customer — discount campaign-এর cohort retention impact।
• Banking — different month-এ open হওয়া account-এর dormancy rate।

Pitfalls:

• "Active" definition — order? login? page view? — clearly define।
• Right-censoring — recent cohort data কম থাকে।
• Cohort size variation — small cohort percentage misleading।
• Survivor bias — যারা churn করেছেন pure data-তে invisible।

মূল উপলব্ধি: Cohort analysis pure SQL-এ ১০০% করা যায় — তবে CTE, date function, multi-level GROUP BY মিলে। এই pattern শিখলে retention, A/B test analysis, marketing attribution — সব unlock।

SQL standard-এর সবচেয়ে controversial rule-এর একটি।

Strict rule (SQL standard):

• SELECT-এ যে column non-aggregate, সেটি GROUP BY-তে থাকতে হবে।
• কারণ — group-এ multiple value হলে SQL জানে না কোনটি দেখাবে।

MySQL-এর "feature" (legacy):

• MySQL ৫.৭ আগে — non-grouped column allow, arbitrary value।
• "Loose mode" — কোনো warning নেই, ভুল ফলাফল।
• ৫.৭+ ONLY_FULL_GROUP_BY default mode — strict।

"Functional dependency" exception:

• যদি একটি column অন্য column-এর সাথে functionally dependent (যেমন primary key) — তবে non-grouped allowable।

• -- valid because customer_id determines name uniquely
  SELECT customer_id, name, COUNT(*)
  FROM customers
  GROUP BY customer_id;

• PostgreSQL ৯.১+ এই smart detection support করে।

ANY_VALUE/MIN/MAX trick:

SELECT
    customer_id,
    MIN(name) AS name,         -- explicit pick
    COUNT(*)
FROM customers
GROUP BY customer_id;

Why strict matters:

• Bug — group-এ multiple value-এ silent ভুল।
• Portability — MySQL → PostgreSQL migration-এ break।
• Predictability — SQL behavior consistent।

Modern alternatives — window function:

-- GROUP BY-এর বদলে window function
SELECT
    customer_id,
    name,                                    -- কোনো aggregation issue নেই
    SUM(amount) OVER (PARTITION BY customer_id) AS total_spent
FROM orders
JOIN customers USING (customer_id);

Aggregate + non-aggregate-এর preferred pattern:

1. Aggregate alone CTE-তে।
2. JOIN back to dimension table।
3. Non-aggregate column conventionally আনা।

WITH agg AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT c.customer_id, c.name, c.email, a.order_count
FROM customers c
JOIN agg a ON c.customer_id = a.customer_id;

Performance considerations:

• Window function-এ partition cost আছে।
• GROUP BY-এ less memory if proper aggregate।
• Query optimizer-এর choice — EXPLAIN দেখতে হবে।

Best practice:

• Strict mode সবসময় ON রাখুন।
• Functional dependency-এ rely না করে explicit GROUP BY।
• Window function modern alternative।
• Code review-এ "implicit grouping" red flag।

মূল উপলব্ধি: "GROUP BY rule" SQL-এর rigor-এর symbol। এর exception understand করা advanced। কিন্তু production-এ — strict mode + explicit grouping + window function — তিনটি combine করে modern data engineer।