উইন্ডো ফাংশন ১: PARTITION BY, ROW_NUMBER ও RANK

SELECT department, ROUND(AVG(salary), 0) AS dept_avg
FROM employees
GROUP BY department
ORDER BY department;

+-------------+----------+
| department  | dept_avg |
+-------------+----------+
| Data        | 121667.0 |
| Engineering | 140000.0 |
| Management  | 250000.0 |
+-------------+----------+

SELECT name,
       department,
       salary,
       ROUND(AVG(salary) OVER (PARTITION BY department), 0)          AS dept_avg,
       salary - ROUND(AVG(salary) OVER (PARTITION BY department), 0) AS vs_avg,
       ROUND(100.0 * salary / SUM(salary) OVER (), 1)                AS pct_of_payroll
FROM employees
ORDER BY department, salary DESC, employee_id;

+-----------------+-------------+--------+----------+----------+----------------+
| name            | department  | salary | dept_avg | vs_avg   | pct_of_payroll |
+-----------------+-------------+--------+----------+----------+----------------+
| Lamia Karim     | Data        | 160000 | 121667.0 |  38333.0 |           15.5 |
| Priya Sen       | Data        | 110000 | 121667.0 | -11667.0 |           10.6 |
| Shuvo Roy       | Data        |  95000 | 121667.0 | -26667.0 |            9.2 |
| Hasan Mahmud    | Engineering | 180000 | 140000.0 |  40000.0 |           17.4 |
| Nusrat Jahan    | Engineering | 120000 | 140000.0 | -20000.0 |           11.6 |
| Zahid Hasan     | Engineering | 120000 | 140000.0 | -20000.0 |           11.6 |
| Ayesha Siddiqua | Management  | 250000 | 250000.0 |      0.0 |           24.2 |
+-----------------+-------------+--------+----------+----------+----------------+

RANK() OVER ( PARTITION BY category_id   ORDER BY price DESC )
  |              |                          |
  |              |                          +-- প্রতিটা উইন্ডোর ভেতরের ক্রম: কে প্রথম, কে দ্বিতীয় …
  |              +-- সারিগুলোকে আলাদা আলাদা উইন্ডোতে ভাগ (ঐচ্ছিক; না দিলে একটাই উইন্ডো)
  +-- প্রতিটা সারির জন্য কী হিসাব হবে

SELECT name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id) AS row_num,
       RANK()       OVER (ORDER BY salary DESC)              AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC)              AS dense_rnk
FROM employees
ORDER BY salary DESC, employee_id;

+-----------------+--------+---------+-----+-----------+
| name            | salary | row_num | rnk | dense_rnk |
+-----------------+--------+---------+-----+-----------+
| Ayesha Siddiqua | 250000 |       1 |   1 |         1 |
| Hasan Mahmud    | 180000 |       2 |   2 |         2 |
| Lamia Karim     | 160000 |       3 |   3 |         3 |
| Nusrat Jahan    | 120000 |       4 |   4 |         4 |
| Zahid Hasan     | 120000 |       5 |   4 |         4 |
| Priya Sen       | 110000 |       6 |   6 |         5 |
| Shuvo Roy       |  95000 |       7 |   7 |         6 |
+-----------------+--------+---------+-----+-----------+

SELECT COALESCE(c.name, 'Uncategorised') AS category,
       p.name,
       p.price,
       RANK() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS price_rank
FROM products AS p
LEFT JOIN categories AS c ON c.category_id = p.category_id
ORDER BY category, price_rank, p.product_id;

+---------------+-----------------------------+-------+------------+
| category      | name                        | price | price_rank |
+---------------+-----------------------------+-------+------------+
| Accessories   | USB-C Hub                   |  2200 |          1 |
| Accessories   | Laptop Stand                |  1500 |          2 |
| Books         | Hands-On Machine Learning   |  2500 |          1 |
| Books         | Python Crash Course         |  1200 |          2 |
| Courses       | AI Engineering Bootcamp     | 15000 |          1 |
| Courses       | SQL Masterclass             |  3000 |          2 |
| Electronics   | 27-inch Monitor             | 18500 |          1 |
| Electronics   | Noise-Cancelling Headphones |  7500 |          2 |
| Electronics   | Mechanical Keyboard         |  4500 |          3 |
| Electronics   | Wireless Mouse              |   900 |          4 |
| Uncategorised | Gift Card                   |  1000 |          1 |
+---------------+-----------------------------+-------+------------+

SELECT name, category_id,
       RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rnk
FROM products
WHERE rnk <= 2;

Error: misuse of aliased window function rnk

WITH product_revenue AS (
    SELECT p.product_id, p.name, c.name AS category,
           SUM(oi.quantity * oi.unit_price) AS revenue
    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 IN ('delivered', 'shipped')
    GROUP BY p.product_id, p.name, c.name
),
ranked AS (
    SELECT *,
           RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
    FROM product_revenue
)
SELECT category, rnk, name, revenue
FROM ranked
WHERE rnk <= 2
ORDER BY category, rnk, product_id;

+-------------+-----+---------------------------+---------+
| category    | rnk | name                      | revenue |
+-------------+-----+---------------------------+---------+
| Accessories |   1 | Laptop Stand              |    4500 |
| Accessories |   2 | USB-C Hub                 |    4400 |
| Books       |   1 | Hands-On Machine Learning |    5000 |
| Books       |   2 | Python Crash Course       |    3600 |
| Courses     |   1 | AI Engineering Bootcamp   |   15000 |
| Courses     |   2 | SQL Masterclass           |    6000 |
| Electronics |   1 | 27-inch Monitor           |   18500 |
| Electronics |   2 | Mechanical Keyboard       |    8550 |
+-------------+-----+---------------------------+---------+

WITH ordered AS (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY order_date DESC, order_id DESC) AS rn
    FROM orders AS o
)
SELECT c.name, ordered.order_id, ordered.order_date, ordered.status
FROM ordered
JOIN customers AS c ON c.customer_id = ordered.customer_id
WHERE rn = 1
ORDER BY ordered.order_date DESC, ordered.order_id;

+-----------------+----------+------------+-----------+
| name            | order_id | order_date | status    |
+-----------------+----------+------------+-----------+
| Farhana Akter   |       14 | 2026-06-09 | delivered |
| Imran Hossain   |       13 | 2026-06-02 | pending   |
| Tanvir Ahmed    |       12 | 2026-05-21 | shipped   |
| Mitu Das        |       11 | 2026-05-06 | delivered |
| Nadia Rahman    |       10 | 2026-04-19 | delivered |
| Sadia Chowdhury |        7 | 2026-03-15 | delivered |
| Rafiq Islam     |        5 | 2026-02-25 | cancelled |
+-----------------+----------+------------+-----------+

-- PostgreSQL
WITH ordered AS (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY order_date DESC, order_id DESC) AS rn
    FROM orders AS o
)
SELECT customer_id, order_id, order_date
FROM ordered
WHERE rn = 1 AND customer_id <= 3
ORDER BY customer_id;

+-------------+----------+------------+
| customer_id | order_id | order_date |
+-------------+----------+------------+
|           1 |       10 | 2026-04-19 |
|           2 |       12 | 2026-05-21 |
|           3 |       14 | 2026-06-09 |
+-------------+----------+------------+

SELECT name,
       price,
       NTILE(4) OVER (ORDER BY price DESC, product_id) AS price_tier
FROM products
ORDER BY price DESC, product_id;

+-----------------------------+-------+------------+
| name                        | price | price_tier |
+-----------------------------+-------+------------+
| 27-inch Monitor             | 18500 |          1 |
| AI Engineering Bootcamp     | 15000 |          1 |
| Noise-Cancelling Headphones |  7500 |          1 |
| Mechanical Keyboard         |  4500 |          2 |
| SQL Masterclass             |  3000 |          2 |
| Hands-On Machine Learning   |  2500 |          2 |
| USB-C Hub                   |  2200 |          3 |
| Laptop Stand                |  1500 |          3 |
| Python Crash Course         |  1200 |          3 |
| Gift Card                   |  1000 |          4 |
| Wireless Mouse              |   900 |          4 |
+-----------------------------+-------+------------+

CREATE TABLE signups (
    signup_id INTEGER PRIMARY KEY,
    email     TEXT NOT NULL,
    source    TEXT NOT NULL,
    signed_up TEXT NOT NULL
);
INSERT INTO signups (signup_id, email, source, signed_up) VALUES
    (1, 'nadia@example.com',  'web', '2026-03-01'),
    (2, 'tanvir@example.com', 'web', '2026-03-02'),
    (3, 'Nadia@Example.com',  'app', '2026-03-05'),
    (4, 'rafiq@example.com',  'web', '2026-03-02'),
    (5, 'rafiq@example.com',  'web', '2026-03-02'),
    (6, 'mitu@example.com',   'app', '2026-03-07');

SELECT signup_id, email, signed_up,
       ROW_NUMBER() OVER (PARTITION BY LOWER(email)
                          ORDER BY signed_up DESC, signup_id DESC) AS rn
FROM signups
ORDER BY LOWER(email), rn;

+-----------+--------------------+------------+----+
| signup_id | email              | signed_up  | rn |
+-----------+--------------------+------------+----+
|         6 | mitu@example.com   | 2026-03-07 |  1 |
|         3 | Nadia@Example.com  | 2026-03-05 |  1 |
|         1 | nadia@example.com  | 2026-03-01 |  2 |
|         5 | rafiq@example.com  | 2026-03-02 |  1 |
|         4 | rafiq@example.com  | 2026-03-02 |  2 |
|         2 | tanvir@example.com | 2026-03-02 |  1 |
+-----------+--------------------+------------+----+

DELETE FROM signups
WHERE signup_id IN (
    SELECT signup_id
    FROM (
        SELECT signup_id,
               ROW_NUMBER() OVER (PARTITION BY LOWER(email)
                                  ORDER BY signed_up DESC, signup_id DESC) AS rn
        FROM signups
    ) AS t
    WHERE rn > 1
);

SELECT signup_id, email, source FROM signups ORDER BY signup_id;

+-----------+--------------------+--------+
| signup_id | email              | source |
+-----------+--------------------+--------+
|         2 | tanvir@example.com | web    |
|         3 | Nadia@Example.com  | app    |
|         5 | rafiq@example.com  | web    |
|         6 | mitu@example.com   | app    |
+-----------+--------------------+--------+

-- 1. প্রতি গ্রাহকের অর্ডারের ক্রম
SELECT customer_id, order_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id
                          ORDER BY order_date, order_id) AS order_number
FROM orders
ORDER BY customer_id, order_number;

-- 2. প্রতি ক্যাটাগরিতে দ্বিতীয় সবচেয়ে দামি
SELECT category_id, name, price
FROM (
    SELECT category_id, name, price, product_id,
           DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS dr
    FROM products
    WHERE category_id IS NOT NULL
) AS t
WHERE dr = 2
ORDER BY category_id, product_id;

-- 3. অ্যাগ্রিগেটকে র‍্যাংক: উইন্ডো চলে GROUP BY-এর পরে
SELECT c.name,
       COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
       DENSE_RANK() OVER (ORDER BY COALESCE(SUM(oi.quantity * oi.unit_price), 0) DESC) AS revenue_rank
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.customer_id
      AND o.status IN ('delivered', 'shipped')
LEFT JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY c.customer_id, c.name
ORDER BY revenue_rank, c.customer_id;