CTE আর রিকার্সিভ কোয়েরি

WITH sold AS (
    SELECT o.order_id, o.customer_id, o.order_date, oi.product_id,
           oi.quantity * oi.unit_price AS amount
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status IN ('delivered', 'shipped')
)
SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(DISTINCT order_id)      AS orders,
       SUM(amount)                   AS revenue
FROM sold
GROUP BY month
ORDER BY month;

+---------+--------+---------+
| month   | orders | revenue |
+---------+--------+---------+
| 2026-01 |      2 |    6600 |
| 2026-02 |      2 |    7000 |
| 2026-03 |      3 |   21400 |
| 2026-04 |      2 |   23450 |
| 2026-05 |      2 |    8500 |
| 2026-06 |      1 |    3100 |
+---------+--------+---------+

WITH sold AS (
    SELECT oi.product_id, oi.quantity * oi.unit_price AS amount
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status IN ('delivered', 'shipped')
),
by_category AS (
    SELECT COALESCE(c.name, 'Uncategorised') AS category,
           SUM(s.amount) AS revenue
    FROM sold AS s
    JOIN products AS p ON p.product_id = s.product_id
    LEFT JOIN categories AS c ON c.category_id = p.category_id
    GROUP BY COALESCE(c.name, 'Uncategorised')
),
total AS (
    SELECT SUM(revenue) AS revenue FROM by_category
)
SELECT b.category,
       b.revenue,
       ROUND(100.0 * b.revenue / t.revenue, 1) AS pct
FROM by_category AS b
CROSS JOIN total AS t
ORDER BY b.revenue DESC, b.category;

+-------------+---------+------+
| category    | revenue | pct  |
+-------------+---------+------+
| Electronics |   31550 | 45.0 |
| Courses     |   21000 | 30.0 |
| Accessories |    8900 | 12.7 |
| Books       |    8600 | 12.3 |
+-------------+---------+------+

WITH customer_revenue AS (
    SELECT o.customer_id, 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 IN ('delivered', 'shipped')
    GROUP BY o.customer_id
)
SELECT c.name, cr.revenue,
       (SELECT ROUND(AVG(revenue), 2) FROM customer_revenue) AS avg_revenue
FROM customer_revenue AS cr
JOIN customers AS c ON c.customer_id = cr.customer_id
WHERE cr.revenue > (SELECT AVG(revenue) FROM customer_revenue)
ORDER BY cr.revenue DESC, c.customer_id;

+--------------+---------+-------------+
| name         | revenue | avg_revenue |
+--------------+---------+-------------+
| Nadia Rahman |   23600 |     11675.0 |
| Tanvir Ahmed |   22500 |     11675.0 |
+--------------+---------+-------------+

-- MySQL
WITH customer_revenue AS (
    SELECT o.customer_id, 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 IN ('delivered', 'shipped')
    GROUP BY o.customer_id
)
SELECT COUNT(*) AS customers, MAX(revenue) AS top_revenue
FROM customer_revenue;

+-----------+-------------+
| customers | top_revenue |
+-----------+-------------+
|         6 |       23600 |
+-----------+-------------+

CREATE TEMP TABLE sold_items AS
SELECT oi.order_id, oi.product_id, oi.quantity * oi.unit_price AS amount
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status IN ('delivered', 'shipped');

SELECT COUNT(*) AS item_rows, SUM(amount) AS revenue FROM sold_items;
SELECT COUNT(DISTINCT order_id) AS orders FROM sold_items;

+-----------+---------+
| item_rows | revenue |
+-----------+---------+
|        18 |   70050 |
+-----------+---------+
+--------+
| orders |
+--------+
|     12 |
+--------+

WITH RECURSIVE n(x) AS (
    SELECT 1                 -- অ্যাঙ্কর: রাউন্ড 1 দেয় x = 1
    UNION ALL
    SELECT x + 1 FROM n      -- ধাপ: প্রতি রাউন্ডে আগের রাউন্ডের সারির সাথে 1 যোগ
    WHERE x < 5              -- থামা: এটা false হলে নতুন সারি নেই, শেষ
)

round 1: 1    round 2: 2    round 3: 3    round 4: 4    round 5: 5    round 6: (none)

WITH RECURSIVE n(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM n WHERE x < 5
)
SELECT x, x * x AS square FROM n;

+---+--------+
| x | square |
+---+--------+
| 1 |      1 |
| 2 |      4 |
| 3 |      9 |
| 4 |     16 |
| 5 |     25 |
+---+--------+

WITH RECURSIVE days(day) AS (
    SELECT '2026-05-28'
    UNION ALL
    SELECT date(day, '+1 day') FROM days WHERE day < '2026-06-10'
)
SELECT d.day, COUNT(o.order_id) AS orders
FROM days AS d
LEFT JOIN orders AS o ON o.order_date = d.day
GROUP BY d.day
ORDER BY d.day;

+------------+--------+
| day        | orders |
+------------+--------+
| 2026-05-28 |      0 |
| 2026-05-29 |      0 |
| 2026-05-30 |      0 |
| 2026-05-31 |      0 |
| 2026-06-01 |      0 |
| 2026-06-02 |      1 |
| 2026-06-03 |      0 |
| 2026-06-04 |      0 |
| 2026-06-05 |      0 |
| 2026-06-06 |      0 |
| 2026-06-07 |      0 |
| 2026-06-08 |      0 |
| 2026-06-09 |      1 |
| 2026-06-10 |      0 |
+------------+--------+

-- MySQL
WITH RECURSIVE days AS (
    SELECT CAST('2026-06-01' AS DATE) AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY FROM days WHERE day < '2026-06-03'
)
SELECT day FROM days ORDER BY day;

+------------+
| day        |
+------------+
| 2026-06-01 |
| 2026-06-02 |
| 2026-06-03 |
+------------+

-- PostgreSQL
SELECT CAST(d AS DATE) AS day
FROM generate_series(DATE '2026-06-01', DATE '2026-06-03', INTERVAL '1 day') AS g(d)
ORDER BY day;

+------------+
| day        |
+------------+
| 2026-06-01 |
| 2026-06-02 |
| 2026-06-03 |
+------------+

WITH RECURSIVE chart AS (
    SELECT employee_id, name, 1 AS level, name AS path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, c.level + 1, c.path || ' > ' || e.name
    FROM employees AS e
    JOIN chart AS c ON e.manager_id = c.employee_id
)
SELECT level, name, path
FROM chart
ORDER BY path;

+-------+-----------------+-----------------------------------------------+
| level | name            | path                                          |
+-------+-----------------+-----------------------------------------------+
|     1 | Ayesha Siddiqua | Ayesha Siddiqua                               |
|     2 | Hasan Mahmud    | Ayesha Siddiqua > Hasan Mahmud                |
|     3 | Nusrat Jahan    | Ayesha Siddiqua > Hasan Mahmud > Nusrat Jahan |
|     3 | Zahid Hasan     | Ayesha Siddiqua > Hasan Mahmud > Zahid Hasan  |
|     2 | Lamia Karim     | Ayesha Siddiqua > Lamia Karim                 |
|     3 | Priya Sen       | Ayesha Siddiqua > Lamia Karim > Priya Sen     |
|     3 | Shuvo Roy       | Ayesha Siddiqua > Lamia Karim > Shuvo Roy     |
+-------+-----------------+-----------------------------------------------+

-- PostgreSQL
WITH RECURSIVE chart AS (
    SELECT employee_id, name, 1 AS level, name AS path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, c.level + 1, c.path || ' > ' || e.name
    FROM employees AS e
    JOIN chart AS c ON e.manager_id = c.employee_id
)
SELECT level, name, path FROM chart ORDER BY path;

Error: recursive query "chart" column 4 has type character varying(100) in non-recursive term but type character varying overall

-- PostgreSQL
WITH RECURSIVE chart AS (
    SELECT employee_id, name, 1 AS level, CAST(name AS TEXT) AS path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, c.level + 1, c.path || ' > ' || e.name
    FROM employees AS e
    JOIN chart AS c ON e.manager_id = c.employee_id
)
SELECT level, path FROM chart WHERE level = 3 ORDER BY path;

+-------+-----------------------------------------------+
| level | path                                          |
+-------+-----------------------------------------------+
|     3 | Ayesha Siddiqua > Hasan Mahmud > Nusrat Jahan |
|     3 | Ayesha Siddiqua > Hasan Mahmud > Zahid Hasan  |
|     3 | Ayesha Siddiqua > Lamia Karim > Priya Sen     |
|     3 | Ayesha Siddiqua > Lamia Karim > Shuvo Roy     |
+-------+-----------------------------------------------+

-- MySQL
WITH RECURSIVE chart AS (
    SELECT employee_id, name, 1 AS level, CAST(name AS CHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, c.level + 1, CONCAT(c.path, ' > ', e.name)
    FROM employees AS e
    JOIN chart AS c ON e.manager_id = c.employee_id
)
SELECT level, path FROM chart WHERE level = 3 ORDER BY path;

+-------+-----------------------------------------------+
| level | path                                          |
+-------+-----------------------------------------------+
|     3 | Ayesha Siddiqua > Hasan Mahmud > Nusrat Jahan |
|     3 | Ayesha Siddiqua > Hasan Mahmud > Zahid Hasan  |
|     3 | Ayesha Siddiqua > Lamia Karim > Priya Sen     |
|     3 | Ayesha Siddiqua > Lamia Karim > Shuvo Roy     |
+-------+-----------------------------------------------+

WITH a AS (SELECT 1 AS x),
WITH b AS (SELECT 2 AS y)
SELECT * FROM a, b;

Error: near "b": syntax error

WITH sold AS (SELECT * FROM orders WHERE status = 'delivered')
SELECT COUNT(*) AS delivered FROM sold;

SELECT COUNT(*) FROM sold;

+-----------+
| delivered |
+-----------+
|        11 |
+-----------+
Error: no such table: sold

-- MySQL
WITH RECURSIVE n AS (
    SELECT 1 AS x
    UNION ALL
    SELECT x + 1 FROM n
)
SELECT COUNT(*) FROM n;

Error: ERROR 3636: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

-- MySQL
WITH RECURSIVE chart AS (
    SELECT employee_id, name, '' AS indent
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, CONCAT(c.indent, '-- ')
    FROM employees AS e JOIN chart AS c ON e.manager_id = c.employee_id
)
SELECT CONCAT(indent, name) AS org FROM chart;

Error: ERROR 1406: Data too long for column 'indent' at row 1

-- 1. কম স্টক
WITH low_stock AS (
    SELECT product_id, name, category_id, stock
    FROM products
    WHERE stock < 10
)
SELECT l.name, l.stock, c.name AS category
FROM low_stock AS l
LEFT JOIN categories AS c ON c.category_id = l.category_id
ORDER BY l.stock, l.product_id;

-- 2. সেরা মাস
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 IN ('delivered', 'shipped')
    GROUP BY month
),
best AS (
    SELECT MAX(revenue) AS revenue FROM monthly
)
SELECT m.month, m.revenue
FROM monthly AS m
JOIN best AS b ON b.revenue = m.revenue;

-- 3. প্রত্যেক ম্যানেজারের অধীনে সবাই
WITH RECURSIVE under AS (
    SELECT manager_id AS boss_id, employee_id
    FROM employees
    WHERE manager_id IS NOT NULL
    UNION ALL
    SELECT u.boss_id, e.employee_id
    FROM under AS u
    JOIN employees AS e ON e.manager_id = u.employee_id
)
SELECT b.name, COUNT(u.employee_id) AS people_under
FROM employees AS b
LEFT JOIN under AS u ON u.boss_id = b.employee_id
GROUP BY b.employee_id, b.name
ORDER BY people_under DESC, b.employee_id;