CASE WHEN, শর্তসাপেক্ষ গণনা আর ব্যবসার প্রশ্ন

SELECT product_id, name, price,
       CASE
           WHEN price < 2000 THEN 'budget'
           WHEN price < 5000 THEN 'mid'          -- কেবল price >= 2000 হলেই এখানে আসে
           ELSE 'premium'
       END AS band
FROM products
ORDER BY price, product_id;

+------------+-----------------------------+-------+---------+
| product_id | name                        | price | band    |
+------------+-----------------------------+-------+---------+
|          4 | Wireless Mouse              |   900 | budget  |
|         11 | Gift Card                   |  1000 | budget  |
|          1 | Python Crash Course         |  1200 | budget  |
|          8 | Laptop Stand                |  1500 | budget  |
|          9 | USB-C Hub                   |  2200 | mid     |
|          2 | Hands-On Machine Learning   |  2500 | mid     |
|          6 | SQL Masterclass             |  3000 | mid     |
|          3 | Mechanical Keyboard         |  4500 | mid     |
|         10 | Noise-Cancelling Headphones |  7500 | premium |
|          7 | AI Engineering Bootcamp     | 15000 | premium |
|          5 | 27-inch Monitor             | 18500 | premium |
+------------+-----------------------------+-------+---------+

SELECT order_id, status,
       CASE status
           WHEN 'delivered' THEN 'done'
           WHEN 'shipped'   THEN 'on the way'
           WHEN 'pending'   THEN 'waiting'
       END AS label
FROM orders
WHERE order_id = 5 OR order_id >= 11
ORDER BY order_id;

+----------+-----------+------------+
| order_id | status    | label      |
+----------+-----------+------------+
|        5 | cancelled | NULL       |
|       11 | delivered | done       |
|       12 | shipped   | on the way |
|       13 | pending   | waiting    |
|       14 | delivered | done       |
+----------+-----------+------------+

SELECT order_id, status
FROM orders
ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'shipped' THEN 2 WHEN 'cancelled' THEN 3 ELSE 4 END,
         order_id
LIMIT 5;

+----------+-----------+
| order_id | status    |
+----------+-----------+
|       13 | pending   |
|       12 | shipped   |
|        5 | cancelled |
|        1 | delivered |
|        2 | delivered |
+----------+-----------+

SELECT CASE
           WHEN price < 2000 THEN 'budget'
           WHEN price < 5000 THEN 'mid'
           ELSE 'premium'
       END              AS band,
       COUNT(*)         AS products,
       MIN(price)       AS from_price,
       MAX(price)       AS to_price
FROM products
GROUP BY band
ORDER BY MIN(price);

+---------+----------+------------+----------+
| band    | products | from_price | to_price |
+---------+----------+------------+----------+
| budget  |        4 |        900 |     1500 |
| mid     |        4 |       2200 |     4500 |
| premium |        3 |       7500 |    18500 |
+---------+----------+------------+----------+

SELECT customer_id,
       COUNT(*)                                              AS orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       COUNT(CASE WHEN status IN ('pending', 'shipped') THEN 1 END) AS open_orders,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

+-------------+--------+-----------+-------------+-----------+
| customer_id | orders | delivered | open_orders | cancelled |
+-------------+--------+-----------+-------------+-----------+
|           1 |      3 |         3 |           0 |         0 |
|           2 |      3 |         2 |           1 |         0 |
|           3 |      3 |         3 |           0 |         0 |
|           4 |      1 |         0 |           0 |         1 |
|           5 |      1 |         1 |           0 |         0 |
|           6 |      2 |         1 |           1 |         0 |
|           7 |      1 |         1 |           0 |         0 |
+-------------+--------+-----------+-------------+-----------+

-- PostgreSQL
SELECT customer_id,
       COUNT(*)                                                AS orders,
       COUNT(*) FILTER (WHERE status = 'delivered')            AS delivered,
       COUNT(*) FILTER (WHERE status IN ('pending', 'shipped')) AS open_orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id
LIMIT 3;

+-------------+--------+-----------+-------------+
| customer_id | orders | delivered | open_orders |
+-------------+--------+-----------+-------------+
|           1 |      3 |         3 |           0 |
|           2 |      3 |         2 |           1 |
|           3 |      3 |         3 |           0 |
+-------------+--------+-----------+-------------+

-- MySQL
SELECT customer_id,
       COUNT(*)                              AS orders,
       SUM(status = 'delivered')             AS delivered,
       SUM(status IN ('pending', 'shipped')) AS open_orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id
LIMIT 3;

+-------------+--------+-----------+-------------+
| customer_id | orders | delivered | open_orders |
+-------------+--------+-----------+-------------+
|           1 |      3 |         3 |           0 |
|           2 |      3 |         2 |           1 |
|           3 |      3 |         3 |           0 |
+-------------+--------+-----------+-------------+

SELECT strftime('%Y-%m', paid_on)                              AS month,
       COUNT(*)                                                AS payments,
       SUM(amount)                                             AS received,
       SUM(CASE WHEN method = 'bkash' THEN amount ELSE 0 END)  AS bkash,
       SUM(CASE WHEN method = 'card'  THEN amount ELSE 0 END)  AS card,
       SUM(CASE WHEN method = 'cash'  THEN amount ELSE 0 END)  AS cash
FROM payments
GROUP BY month
ORDER BY month;

+---------+----------+----------+-------+-------+------+
| month   | payments | received | bkash | card  | cash |
+---------+----------+----------+-------+-------+------+
| 2026-01 |        2 |     6600 |  2100 |  4500 |    0 |
| 2026-02 |        2 |     7000 |  3000 |     0 | 4000 |
| 2026-03 |        4 |    21400 |  7400 | 14000 |    0 |
| 2026-04 |        2 |    23450 |     0 | 23450 |    0 |
| 2026-05 |        2 |     8500 |  5500 |  3000 |    0 |
| 2026-06 |        1 |     3100 |     0 |     0 | 3100 |
+---------+----------+----------+-------+-------+------+

-- PostgreSQL
SELECT TO_CHAR(paid_on, 'YYYY-MM') AS month, SUM(amount) AS received
FROM payments
GROUP BY month
ORDER BY month;

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

SELECT ROUND(SUM(CASE WHEN method = 'bkash' THEN amount END) * 100.0 / SUM(amount), 1) AS bkash_pct,
       ROUND(SUM(CASE WHEN method = 'card'  THEN amount END) * 100.0 / SUM(amount), 1) AS card_pct,
       ROUND(SUM(CASE WHEN method = 'cash'  THEN amount END) * 100.0 / SUM(amount), 1) AS cash_pct
FROM payments;

+-----------+----------+----------+
| bkash_pct | card_pct | cash_pct |
+-----------+----------+----------+
|      25.7 |     64.2 |     10.1 |
+-----------+----------+----------+

-- PostgreSQL
SELECT COALESCE(method, 'ALL') AS method, COUNT(*) AS payments, SUM(amount) AS total
FROM payments
GROUP BY ROLLUP (method)
ORDER BY GROUPING(method), total DESC;

+--------+----------+-------+
| method | payments | total |
+--------+----------+-------+
| card   |        6 | 44950 |
| bkash  |        5 | 18000 |
| cash   |        2 |  7100 |
| ALL    |       13 | 70050 |
+--------+----------+-------+

-- MySQL
SELECT COALESCE(method, 'ALL') AS method, COUNT(*) AS payments, SUM(amount) AS total
FROM payments
GROUP BY method WITH ROLLUP
ORDER BY GROUPING(method), total DESC;

+--------+----------+-------+
| method | payments | total |
+--------+----------+-------+
| card   |        6 | 44950 |
| bkash  |        5 | 18000 |
| cash   |        2 |  7100 |
| ALL    |       13 | 70050 |
+--------+----------+-------+

SELECT order_id, COUNT(*) AS payments, SUM(amount) AS paid
FROM payments
GROUP BY order_id
HAVING COUNT(*) > 1;

+----------+----------+-------+
| order_id | payments | paid  |
+----------+----------+-------+
|        6 |        2 | 15000 |
+----------+----------+-------+

CREATE TABLE signups (signup_id INTEGER PRIMARY KEY, email TEXT NOT NULL, signed_up_on TEXT NOT NULL);
INSERT INTO signups (email, signed_up_on) VALUES
    ('nadia@example.com',    '2026-03-01'),
    ('Nadia@Example.com ',   '2026-03-04'),
    ('tanvir@example.com',   '2026-03-05'),
    ('mitu@example.com',     '2026-03-07'),
    (' NADIA@example.com',   '2026-03-09'),
    ('mitu@example.com',     '2026-03-10');

SELECT email, COUNT(*) AS n FROM signups GROUP BY email HAVING COUNT(*) > 1;

SELECT LOWER(TRIM(email))  AS clean_email,
       COUNT(*)            AS n,
       MIN(signup_id)      AS keep_id,
       MIN(signed_up_on)   AS first_signup
FROM signups
GROUP BY clean_email
HAVING COUNT(*) > 1
ORDER BY clean_email;

+------------------+---+
| email            | n |
+------------------+---+
| mitu@example.com | 2 |
+------------------+---+
+-------------------+---+---------+--------------+
| clean_email       | n | keep_id | first_signup |
+-------------------+---+---------+--------------+
| mitu@example.com  | 2 |       4 | 2026-03-07   |
| nadia@example.com | 3 |       1 | 2026-03-01   |
+-------------------+---+---------+--------------+

SELECT category_id, COUNT(*) AS products
FROM products
GROUP BY category_id
ORDER BY category_id;

+-------------+----------+
| category_id | products |
+-------------+----------+
| NULL        |        1 |
|           1 |        2 |
|           2 |        4 |
|           3 |        2 |
|           4 |        2 |
+-------------+----------+

-- আগাম ঝলক: যে ক্যাটাগরিতে প্রোডাক্ট নেই, LEFT JOIN সেটাও রাখে
SELECT c.category_id, c.name, COUNT(p.product_id) AS products
FROM categories AS c
LEFT JOIN products AS p ON p.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY c.category_id;

+-------------+-------------+----------+
| category_id | name        | products |
+-------------+-------------+----------+
|           1 | Books       |        2 |
|           2 | Electronics |        4 |
|           3 | Courses     |        2 |
|           4 | Accessories |        2 |
|           5 | Furniture   |        0 |
+-------------+-------------+----------+

SELECT COUNT(*)                                                         AS orders,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)            AS cancelled,
       SUM(CASE WHEN status IN ('pending', 'shipped') THEN 1 ELSE 0 END) AS still_open,
       ROUND(AVG(CASE WHEN status = 'cancelled' THEN 1.0 ELSE 0 END) * 100, 1) AS cancel_pct
FROM orders;

+--------+-----------+------------+------------+
| orders | cancelled | still_open | cancel_pct |
+--------+-----------+------------+------------+
|     14 |         1 |          2 |        7.1 |
+--------+-----------+------------+------------+

SELECT customer_id,
       COUNT(*)                                  AS orders,
       CASE WHEN COUNT(*) >= 2 THEN 1 ELSE 0 END AS repeat_buyer
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
ORDER BY customer_id;

+-------------+--------+--------------+
| customer_id | orders | repeat_buyer |
+-------------+--------+--------------+
|           1 |      3 |            1 |
|           2 |      3 |            1 |
|           3 |      3 |            1 |
|           5 |      1 |            0 |
|           6 |      2 |            1 |
|           7 |      1 |            0 |
+-------------+--------+--------------+

-- সহজ
SELECT CASE
           WHEN stock IS NULL THEN 'not tracked'
           WHEN stock = 0     THEN 'out of stock'
           WHEN stock < 10    THEN 'low'
           ELSE 'ok'
       END      AS stock_label,
       COUNT(*) AS products
FROM products
GROUP BY stock_label
ORDER BY products DESC, stock_label;

+--------------+----------+
| stock_label  | products |
+--------------+----------+
| ok           |        5 |
| not tracked  |        3 |
| low          |        2 |
| out of stock |        1 |
+--------------+----------+

-- মাঝারি
SELECT strftime('%Y-%m', order_date)                            AS month,
       COUNT(*)                                                 AS orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END)    AS delivered,
       SUM(CASE WHEN status <> 'delivered' THEN 1 ELSE 0 END)   AS not_delivered
FROM orders
GROUP BY month
ORDER BY month;

+---------+--------+-----------+---------------+
| month   | orders | delivered | not_delivered |
+---------+--------+-----------+---------------+
| 2026-01 |      2 |         2 |             0 |
| 2026-02 |      3 |         2 |             1 |
| 2026-03 |      3 |         3 |             0 |
| 2026-04 |      2 |         2 |             0 |
| 2026-05 |      2 |         1 |             1 |
| 2026-06 |      2 |         1 |             1 |
+---------+--------+-----------+---------------+

-- কঠিন
SELECT customer_id,
       COUNT(*)                                                       AS orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END)          AS delivered,
       ROUND(AVG(CASE WHEN status = 'delivered' THEN 1.0 ELSE 0 END) * 100, 1) AS delivered_rate,
       strftime('%Y-%m', MAX(order_date))                             AS last_order_month,
       CASE
           WHEN COUNT(*) >= 3 THEN 'loyal'
           WHEN COUNT(*) = 2  THEN 'returning'
           ELSE 'new'
       END                                                            AS segment
FROM orders
GROUP BY customer_id
ORDER BY orders DESC, customer_id;

+-------------+--------+-----------+----------------+------------------+-----------+
| customer_id | orders | delivered | delivered_rate | last_order_month | segment   |
+-------------+--------+-----------+----------------+------------------+-----------+
|           1 |      3 |         3 |          100.0 | 2026-04          | loyal     |
|           2 |      3 |         2 |           66.7 | 2026-05          | loyal     |
|           3 |      3 |         3 |          100.0 | 2026-06          | loyal     |
|           6 |      2 |         1 |           50.0 | 2026-06          | returning |
|           4 |      1 |         0 |            0.0 | 2026-02          | new       |
|           5 |      1 |         1 |          100.0 | 2026-03          | new       |
|           7 |      1 |         1 |          100.0 | 2026-05          | new       |
+-------------+--------+-----------+----------------+------------------+-----------+