উইন্ডো ফাংশন ২: LAG, LEAD, রানিং টোটাল আর ফ্রেম

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY month
)
SELECT month, revenue FROM monthly ORDER BY month;

+---------+---------+
| month   | revenue |
+---------+---------+
| 2026-01 |    6600 |
| 2026-02 |    7000 |
| 2026-03 |   21400 |
| 2026-04 |   23450 |
| 2026-05 |    8500 |
| 2026-06 |   18100 |
+---------+---------+

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY month
)
SELECT month,
       revenue,
       LAG(revenue)  OVER (ORDER BY month)            AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month)   AS change,
       LEAD(revenue) OVER (ORDER BY month)            AS next_month,
       LAG(revenue, 2, 0) OVER (ORDER BY month)       AS two_back
FROM monthly
ORDER BY month;

+---------+---------+------------+--------+------------+----------+
| month   | revenue | prev_month | change | next_month | two_back |
+---------+---------+------------+--------+------------+----------+
| 2026-01 |    6600 | NULL       | NULL   |       7000 |        0 |
| 2026-02 |    7000 |       6600 |    400 |      21400 |        0 |
| 2026-03 |   21400 |       7000 |  14400 |      23450 |     6600 |
| 2026-04 |   23450 |      21400 |   2050 |       8500 |     7000 |
| 2026-05 |    8500 |      23450 | -14950 |      18100 |    21400 |
| 2026-06 |   18100 |       8500 |   9600 | NULL       |    23450 |
+---------+---------+------------+--------+------------+----------+

SELECT customer_id,
       order_id,
       order_date,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order,
       CAST(julianday(order_date)
            - julianday(LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date))
            AS INTEGER) AS days_since_prev
FROM orders
WHERE status <> 'cancelled' AND customer_id <= 3
ORDER BY customer_id, order_date;

+-------------+----------+------------+------------+-----------------+
| customer_id | order_id | order_date | prev_order | days_since_prev |
+-------------+----------+------------+------------+-----------------+
|           1 |        1 | 2026-01-12 | NULL       | NULL            |
|           1 |        3 | 2026-02-03 | 2026-01-12 |              22 |
|           1 |       10 | 2026-04-19 | 2026-02-03 |              75 |
|           2 |        2 | 2026-01-20 | NULL       | NULL            |
|           2 |        6 | 2026-03-08 | 2026-01-20 |              47 |
|           2 |       12 | 2026-05-21 | 2026-03-08 |              74 |
|           3 |        4 | 2026-02-14 | NULL       | NULL            |
|           3 |        8 | 2026-03-28 | 2026-02-14 |              42 |
|           3 |       14 | 2026-06-09 | 2026-03-28 |              73 |
+-------------+----------+------------+------------+-----------------+

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY month
)
SELECT month,
       revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total,
       SUM(revenue) OVER ()               AS grand_total,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM monthly
ORDER BY month;

+---------+---------+---------------+-------------+--------------+
| month   | revenue | running_total | grand_total | pct_of_total |
+---------+---------+---------------+-------------+--------------+
| 2026-01 |    6600 |          6600 |       85050 |          7.8 |
| 2026-02 |    7000 |         13600 |       85050 |          8.2 |
| 2026-03 |   21400 |         35000 |       85050 |         25.2 |
| 2026-04 |   23450 |         58450 |       85050 |         27.6 |
| 2026-05 |    8500 |         66950 |       85050 |         10.0 |
| 2026-06 |   18100 |         85050 |       85050 |         21.3 |
+---------+---------+---------------+-------------+--------------+

SELECT c.name AS category,
       p.name AS product,
       SUM(oi.quantity * oi.unit_price) AS revenue,
       ROUND(100.0 * SUM(oi.quantity * oi.unit_price)
             / SUM(SUM(oi.quantity * oi.unit_price)) OVER (PARTITION BY c.name), 1) AS pct_of_category
FROM order_items AS oi
JOIN orders     AS o ON o.order_id = oi.order_id
JOIN products   AS p ON p.product_id = oi.product_id
JOIN categories AS c ON c.category_id = p.category_id
WHERE o.status <> 'cancelled' AND c.name IN ('Books', 'Electronics')
GROUP BY c.name, p.name
ORDER BY category, revenue DESC;

+-------------+---------------------------+---------+-----------------+
| category    | product                   | revenue | pct_of_category |
+-------------+---------------------------+---------+-----------------+
| Books       | Hands-On Machine Learning |    5000 |            58.1 |
| Books       | Python Crash Course       |    3600 |            41.9 |
| Electronics | 27-inch Monitor           |   18500 |            58.6 |
| Electronics | Mechanical Keyboard       |    8550 |            27.1 |
| Electronics | Wireless Mouse            |    4500 |            14.3 |
+-------------+---------------------------+---------+-----------------+

ROWS   BETWEEN 2 PRECEDING AND CURRENT ROW              -- এই সারি আর তার আগের দুটো
ROWS   BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW      -- পার্টিশনের শুরু থেকে এখান পর্যন্ত
ROWS   BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   -- পুরো পার্টিশন
RANGE  BETWEEN 30 PRECEDING AND CURRENT ROW             -- ORDER BY-এর মান এই সারির 30-এর মধ্যে
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW              -- এই পিয়ার গ্রুপ আর তার আগেরটা

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY month
)
SELECT month,
       revenue,
       ROUND(AVG(revenue) OVER (ORDER BY month
                                ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS avg_3m,
       COUNT(*) OVER (ORDER BY month
                      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS months_in_avg
FROM monthly
ORDER BY month;

+---------+---------+---------+---------------+
| month   | revenue | avg_3m  | months_in_avg |
+---------+---------+---------+---------------+
| 2026-01 |    6600 |  6600.0 |             1 |
| 2026-02 |    7000 |  6800.0 |             2 |
| 2026-03 |   21400 | 11667.0 |             3 |
| 2026-04 |   23450 | 17283.0 |             3 |
| 2026-05 |    8500 | 17783.0 |             3 |
| 2026-06 |   18100 | 16683.0 |             3 |
+---------+---------+---------+---------------+

SELECT name,
       salary,
       SUM(salary) OVER (ORDER BY salary) AS default_range,
       SUM(salary) OVER (ORDER BY salary
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rows,
       SUM(salary) OVER (ORDER BY salary
                         GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS this_and_prev_group
FROM employees
ORDER BY salary, name;

+-----------------+--------+---------------+---------+---------------------+
| name            | salary | default_range | by_rows | this_and_prev_group |
+-----------------+--------+---------------+---------+---------------------+
| Shuvo Roy       |  95000 |         95000 |   95000 |               95000 |
| Priya Sen       | 110000 |        205000 |  205000 |              205000 |
| Nusrat Jahan    | 120000 |        445000 |  325000 |              350000 |
| Zahid Hasan     | 120000 |        445000 |  445000 |              350000 |
| Lamia Karim     | 160000 |        605000 |  605000 |              400000 |
| Hasan Mahmud    | 180000 |        785000 |  785000 |              340000 |
| Ayesha Siddiqua | 250000 |       1035000 | 1035000 |              430000 |
+-----------------+--------+---------------+---------+---------------------+

SELECT order_id,
       order_date,
       COUNT(*) OVER (ORDER BY julianday(order_date)
                      RANGE BETWEEN 30 PRECEDING AND CURRENT ROW) AS orders_last_30d
FROM orders
WHERE order_date < '2026-04-01'
ORDER BY order_date;

+----------+------------+-----------------+
| order_id | order_date | orders_last_30d |
+----------+------------+-----------------+
|        1 | 2026-01-12 |               1 |
|        2 | 2026-01-20 |               2 |
|        3 | 2026-02-03 |               3 |
|        4 | 2026-02-14 |               3 |
|        5 | 2026-02-25 |               3 |
|        6 | 2026-03-08 |               3 |
|        7 | 2026-03-15 |               4 |
|        8 | 2026-03-28 |               3 |
+----------+------------+-----------------+

-- PostgreSQL: তারিখ দিয়ে সরাসরি সাজানো যায়; অফসেটটা একটা INTERVAL
SELECT order_id,
       order_date,
       COUNT(*) OVER (ORDER BY order_date
                      RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW) AS orders_last_30d
FROM orders
WHERE order_date < '2026-04-01'
ORDER BY order_date;

+----------+------------+-----------------+
| order_id | order_date | orders_last_30d |
+----------+------------+-----------------+
|        1 | 2026-01-12 |               1 |
|        2 | 2026-01-20 |               2 |
|        3 | 2026-02-03 |               3 |
|        4 | 2026-02-14 |               3 |
|        5 | 2026-02-25 |               3 |
|        6 | 2026-03-08 |               3 |
|        7 | 2026-03-15 |               4 |
|        8 | 2026-03-28 |               3 |
+----------+------------+-----------------+

-- MySQL: একই ধারণা, MySQL-এর INTERVAL লেখার ধরনে
SELECT order_id,
       order_date,
       COUNT(*) OVER (ORDER BY order_date
                      RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW) AS orders_last_30d
FROM orders
WHERE order_date < '2026-04-01'
ORDER BY order_date;

SELECT department,
       name,
       salary,
       FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC, name) AS top_earner,
       LAST_VALUE(name)  OVER (PARTITION BY department ORDER BY salary DESC, name) AS wrong_lowest,
       LAST_VALUE(name)  OVER (PARTITION BY department ORDER BY salary DESC, name
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest
FROM employees
WHERE department IN ('Data', 'Engineering')
ORDER BY department, salary DESC, name;

+-------------+--------------+--------+--------------+--------------+-------------+
| department  | name         | salary | top_earner   | wrong_lowest | lowest      |
+-------------+--------------+--------+--------------+--------------+-------------+
| Data        | Lamia Karim  | 160000 | Lamia Karim  | Lamia Karim  | Shuvo Roy   |
| Data        | Priya Sen    | 110000 | Lamia Karim  | Priya Sen    | Shuvo Roy   |
| Data        | Shuvo Roy    |  95000 | Lamia Karim  | Shuvo Roy    | Shuvo Roy   |
| Engineering | Hasan Mahmud | 180000 | Hasan Mahmud | Hasan Mahmud | Zahid Hasan |
| Engineering | Nusrat Jahan | 120000 | Hasan Mahmud | Nusrat Jahan | Zahid Hasan |
| Engineering | Zahid Hasan  | 120000 | Hasan Mahmud | Zahid Hasan  | Zahid Hasan |
+-------------+--------------+--------+--------------+--------------+-------------+

SELECT o.customer_id,
       o.order_id,
       o.order_date,
       SUM(oi.quantity * oi.unit_price) AS order_value,
       ROW_NUMBER() OVER w AS order_no,
       SUM(SUM(oi.quantity * oi.unit_price)) OVER w AS spend_to_date,
       ROUND(AVG(SUM(oi.quantity * oi.unit_price)) OVER w, 0) AS avg_order_to_date
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled' AND o.customer_id IN (1, 2)
GROUP BY o.customer_id, o.order_id, o.order_date
WINDOW w AS (PARTITION BY o.customer_id ORDER BY o.order_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY o.customer_id, o.order_date;

+-------------+----------+------------+-------------+----------+---------------+-------------------+
| customer_id | order_id | order_date | order_value | order_no | spend_to_date | avg_order_to_date |
+-------------+----------+------------+-------------+----------+---------------+-------------------+
|           1 |        1 | 2026-01-12 |        2100 |        1 |          2100 |            2100.0 |
|           1 |        3 | 2026-02-03 |        3000 |        2 |          5100 |            2550.0 |
|           1 |       10 | 2026-04-19 |       18500 |        3 |         23600 |            7867.0 |
|           2 |        2 | 2026-01-20 |        4500 |        1 |          4500 |            4500.0 |
|           2 |        6 | 2026-03-08 |       15000 |        2 |         19500 |            9750.0 |
|           2 |       12 | 2026-05-21 |        3000 |        3 |         22500 |            7500.0 |
+-------------+----------+------------+-------------+----------+---------------+-------------------+

-- সহজ
SELECT customer_id, order_id, order_date,
       LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order
FROM orders
WHERE status <> 'cancelled' AND customer_id IN (1, 3)
ORDER BY customer_id, order_date;

-- মাঝারি
SELECT payment_id, paid_on, amount,
       SUM(amount) OVER (ORDER BY paid_on, payment_id) AS running_total,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct_of_all
FROM payments
ORDER BY paid_on, payment_id;

-- কঠিন
SELECT department, name, salary,
       MAX(salary) OVER d AS dept_max,
       MAX(salary) OVER d - salary AS gap_to_top,
       ROUND(AVG(salary) OVER d, 0) AS dept_avg
FROM employees
WINDOW d AS (PARTITION BY department)
ORDER BY department, salary DESC, name;