Window function ও CTE

"Window function লাগে না" — junior থেকে mid analyst-দের সবচেয়ে costly belief।

Pure SQL (pre-window) approach:

• Top-N per group → correlated subquery (slow)।
• Running total → self-join (quadratic complexity)।
• Moving average → multiple self-join + GROUP BY (painful)।
• LAG/LEAD → subquery with row_number trick।

Concrete comparison — running total:

Without window:

SELECT a.day, a.revenue,
       (SELECT SUM(b.revenue)
        FROM daily b
        WHERE b.day <= a.day) AS cumulative
FROM daily a;
-- O(n²) — 10000 rows = 100M operations

With window:

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS cumulative
FROM daily;
-- O(n log n) — 10000 rows = 130k operations

৭৭০x speedup on this scale alone।

Window-only operations:

• Frame definition: "last 7 days" — single SQL clause; subquery-তে date arithmetic + grouping।
• Multiple windows: 7-day MA + 30-day MA + cumulative — single query; subquery-তে ৩x effort।
• Gaps and islands: consecutive run detection — window অপরিহার্য।
• Sessionization: user activity gap-based session — without window, ugly procedural।

Why analyst এড়িয়ে যান:

1. "Looks complex" — OVER, PARTITION BY syntax intimidating প্রথমে।
2. Tutorials beginners-এ কম। Hidden gem।
3. Old DB version — MySQL ৮.০-এর আগে support নেই।
4. Habit — "subquery দিয়েই করি" mentality।

Real-world impact:

• Daraz analytics dashboard — pre-window 30-second load, post-window 2-second।
• bKash daily report — pre-window 4 separate jobs, post-window single CTE।
• Analyst productivity — same problem, half the LOC, double the readability।

মূল উপলব্ধি: Window function না জানা — "still using horse-and-buggy" সমান। ২০২৪-এর data analyst-এর জন্য non-negotiable। ১ সপ্তাহ-এর deep dive — careerlong reward।

"Default frame" — SQL-এর সবচেয়ে gotcha-laden topic-গুলোর একটি।

SQL standard default:

• OVER-এ ORDER BY থাকলে default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW।
• OVER-এ ORDER BY না থাকলে default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (পুরো partition)।

Trap 1 — RANGE vs ROWS:

-- ROWS — শুধু row position-এ
SUM(x) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- RANGE — value-based, equal value একসাথে
SUM(x) OVER (ORDER BY date RANGE BETWEEN '6 days' PRECEDING AND CURRENT ROW)

Same date-এর multiple row-এ behavior ভিন্ন।

Trap 2 — ORDER BY-এ ties:

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS cumul
FROM daily;
-- যদি একই day-এ multiple row → RANGE default-এ একই day-এর সব row একসাথে aggregate
-- ROWS দিয়ে individually:
SUM(revenue) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Trap 3 — ORDER BY-হীন aggregate:

SELECT id, x, SUM(x) OVER () FROM data;
-- পুরো result-এ same value (grand total)
-- vs
SELECT id, x, SUM(x) OVER (ORDER BY id) FROM data;
-- running total — radically different

Trap 4 — LAST_VALUE puzzle:

-- "শেষ value" পেতে চাই
LAST_VALUE(x) OVER (ORDER BY date)
-- result: প্রতি row-এ "current" — কারণ default frame current row-এ থামে
-- সঠিক:
LAST_VALUE(x) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Trap 5 — performance:

• RANGE default — extra value comparison overhead।
• ROWS frame সাধারণত দ্রুত।
• Same partition, different ORDER BY — multiple sort।

Best practices:

1. Frame সবসময় explicit লিখুন — assume করবেন না।
2. ROWS use করুন যদি না RANGE specifically দরকার।
3. LAST_VALUE/FIRST_VALUE use করলে frame অবশ্যই full partition।
4. Documentation comment — "windowed by 7-day rolling"।

Database differences:

• PostgreSQL — strict standard।
• SQL Server ২০১২+ — ROWS support।
• MySQL ৮+ — ROWS/RANGE support।
• Older versions — frame ignored (silent bug)।

মূল উপলব্ধি: Default frame bug-এর invisible source। Senior practitioner-রা frame সবসময় explicit লেখেন। "সংক্ষিপ্ত SQL" valid concern নয় — প্রতিটি bug debug-এ ৫ গুণ বেশি সময় খরচ।

SQL architecture-এর চিরন্তন প্রশ্ন।

(১) Subquery (inline):

SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) sub
WHERE sub.total > 10000;

• Pro: simple, no extra setup।
• Con: nested = unreadable, একই subquery use multi-times duplicate।
• Use: small one-time, single use।

(২) CTE (WITH):

WITH customer_total AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM customer_total WHERE total > 10000;

• Pro: readable, reusable in same query, recursive support।
• Con: some DB-তে materialized (slow for huge data); session-end লাগে।
• Use: complex multi-step query, recursive logic।

(৩) Temp table:

CREATE TEMP TABLE customer_total AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

CREATE INDEX ON customer_total(customer_id);

SELECT * FROM customer_total WHERE total > 10000;

• Pro: indexed, multi-query reuse, statistics for optimizer।
• Con: session-bound, cleanup burden, transaction issue।
• Use: repeated heavy compute in same session, ETL pipeline।

(৪) Materialized view:

CREATE MATERIALIZED VIEW customer_total AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

REFRESH MATERIALIZED VIEW customer_total;  -- periodic
SELECT * FROM customer_total WHERE total > 10000;

• Pro: persistent, indexed, instant query।
• Con: stale data, refresh strategy, storage cost।
• Use: dashboard backend, expensive aggregation reused often।

Decision matrix:

• Single query, simple → subquery।
• Single query, complex/recursive → CTE।
• Multiple queries same session → temp table।
• Reused across sessions → materialized view।
• Real-time freshness needed → CTE/view।
• Performance critical, stale OK → materialized view।

Performance pitfall — CTE optimization fence:

• PostgreSQL ১১ এবং আগে — CTE materialized always; predicate push down হয় না।
• PostgreSQL ১২+ — inline by default; MATERIALIZED hint দিয়ে force।
• SQL Server, Oracle — সাধারণত inline।

Modern alternative — dbt models:

• dbt-এ CTE-based modeling layered।
• Incremental model — "today's diff only" refresh।
• Test, doc, lineage — analytics engineering practice।

Bangladesh production patterns:

• Daraz analytics — CTE-heavy ad-hoc + materialized view dashboard।
• bKash fraud — temp table in batch ETL।
• Pathao — dbt + Snowflake stack (recent)।

মূল উপলব্ধি: "One size fits all" নয়। Query lifecycle (one-shot vs reused) + freshness + scale — তিনটি বিচার করে choice। Senior data engineer-এর kit-এ চারটিই থাকে।

একটি classic "customer journey" query — RFM analysis-এর precursor।

Approach 1 — Multi-CTE:

WITH first_order AS (
    SELECT
        customer_id,
        product_id    AS first_product,
        order_date    AS first_date,
        total_amount  AS first_amount
    FROM (
        SELECT *,
               ROW_NUMBER() OVER (
                   PARTITION BY customer_id
                   ORDER BY order_date ASC
               ) AS rn
        FROM orders
    ) t
    WHERE rn = 1
),
last_order AS (
    SELECT
        customer_id,
        product_id    AS last_product,
        order_date    AS last_date
    FROM (
        SELECT *,
               ROW_NUMBER() OVER (
                   PARTITION BY customer_id
                   ORDER BY order_date DESC
               ) AS rn
        FROM orders
    ) t
    WHERE rn = 1
),
totals AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_spent,
        COUNT(*)          AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT
    c.customer_id,
    c.name,
    fp.name  AS first_product_name,
    lp.name  AS last_product_name,
    fo.first_date,
    lo.last_date,
    (lo.last_date - fo.first_date) AS days_as_customer,
    t.total_spent,
    t.order_count
FROM customers c
JOIN first_order fo  ON c.customer_id = fo.customer_id
JOIN last_order  lo  ON c.customer_id = lo.customer_id
JOIN totals      t   ON c.customer_id = t.customer_id
JOIN products   fp   ON fo.first_product = fp.product_id
JOIN products   lp   ON lo.last_product  = lp.product_id;

Approach 2 — FIRST_VALUE/LAST_VALUE:

WITH journey AS (
    SELECT DISTINCT
        customer_id,
        FIRST_VALUE(product_id) OVER (
            PARTITION BY customer_id ORDER BY order_date ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS first_product,
        LAST_VALUE(product_id) OVER (
            PARTITION BY customer_id ORDER BY order_date ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_product,
        MIN(order_date) OVER (PARTITION BY customer_id) AS first_date,
        MAX(order_date) OVER (PARTITION BY customer_id) AS last_date,
        SUM(total_amount) OVER (PARTITION BY customer_id) AS total_spent,
        COUNT(*) OVER (PARTITION BY customer_id) AS order_count
    FROM orders
)
SELECT *,
       (last_date - first_date) AS days_as_customer
FROM journey;

Note: LAST_VALUE-এর full frame critical।

Approach 3 — JSON aggregation:

SELECT
    customer_id,
    JSONB_AGG(
        JSONB_BUILD_OBJECT('date', order_date, 'product', product_id)
        ORDER BY order_date
    ) AS journey,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;
-- Full timeline as JSON array

Tradeoffs:

• Multi-CTE: readable, multi-JOIN cost।
• Window: single pass, অবলম্বন।
• JSON: flexible, application-side processing।

Adding richness — RFM:

• Recency: CURRENT_DATE - last_date।
• Frequency: order_count।
• Monetary: total_spent।
• NTILE(4)-এ R, F, M — segment 1-4।
• RFM combined → customer segments (champion, at-risk, lost, etc.)।

Real-world consideration:

• Customer "journey" শুধু order নয় — view, cart, search-ও।
• Multi-touch attribution — multi-table window।
• Sessionization — gap detection।
• Cohort overlay — first_date-এর month-এ partition।

মূল উপলব্ধি: Window function customer analytics-এর primary tool। FIRST_VALUE, LAST_VALUE, MIN/MAX OVER, COUNT OVER — single query-তে customer-360 view। SQL ২০০৩ standard ছাড়া এই kind of analysis painful ছিল।