অনেক টেবিল জয়েন, আর ডুপ্লিকেট সারির ফাঁদ

customers ─< orders ─< order_items >─ products >─ categories
                │
                └─< payments

SELECT o.order_id, c.name AS customer, p.name AS product, oi.quantity, oi.unit_price
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.order_id IN (1, 4, 9)
ORDER BY o.order_id, p.product_id;

+----------+---------------+---------------------------+----------+------------+
| order_id | customer      | product                   | quantity | unit_price |
+----------+---------------+---------------------------+----------+------------+
|        1 | Nadia Rahman  | Python Crash Course       |        1 |       1200 |
|        1 | Nadia Rahman  | Wireless Mouse            |        1 |        900 |
|        4 | Farhana Akter | Hands-On Machine Learning |        1 |       2500 |
|        4 | Farhana Akter | Laptop Stand              |        1 |       1500 |
|        9 | Imran Hossain | Mechanical Keyboard       |        1 |       4050 |
|        9 | Imran Hossain | Wireless Mouse            |        1 |        900 |
+----------+---------------+---------------------------+----------+------------+

SELECT cat.name AS category,
       SUM(oi.quantity) AS units,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY cat.category_id, cat.name
ORDER BY revenue DESC;

+-------------+-------+---------+
| category    | units | revenue |
+-------------+-------+---------+
| Electronics |     8 |   31550 |
| Courses     |     3 |   21000 |
| Accessories |     5 |    8900 |
| Books       |     5 |    8600 |
+-------------+-------+---------+

SELECT oi.order_id, p.name, p.price AS list_price, oi.unit_price
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id AND oi.unit_price < p.price
ORDER BY oi.order_id;

+----------+---------------------+------------+------------+
| order_id | name                | list_price | unit_price |
+----------+---------------------+------------+------------+
|        9 | Mechanical Keyboard |       4500 |       4050 |
+----------+---------------------+------------+------------+

SELECT c.name AS customer, p.name AS product, SUM(oi.quantity) AS units
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE c.customer_id IN (1, 3)
GROUP BY c.customer_id, c.name, p.product_id, p.name
ORDER BY c.customer_id, p.product_id;

+---------------+---------------------------+-------+
| customer      | product                   | units |
+---------------+---------------------------+-------+
| Nadia Rahman  | Python Crash Course       |     1 |
| Nadia Rahman  | Wireless Mouse            |     1 |
| Nadia Rahman  | 27-inch Monitor           |     1 |
| Nadia Rahman  | SQL Masterclass           |     1 |
| Farhana Akter | Python Crash Course       |     2 |
| Farhana Akter | Hands-On Machine Learning |     1 |
| Farhana Akter | Wireless Mouse            |     1 |
| Farhana Akter | Laptop Stand              |     1 |
| Farhana Akter | USB-C Hub                 |     1 |
+---------------+---------------------------+-------+

SELECT pa.name AS product_a, pb.name AS product_b, COUNT(*) AS times_together
FROM order_items a
JOIN order_items b ON a.order_id = b.order_id AND a.product_id < b.product_id
JOIN products pa ON pa.product_id = a.product_id
JOIN products pb ON pb.product_id = b.product_id
GROUP BY a.product_id, b.product_id, pa.name, pb.name
ORDER BY times_together DESC, a.product_id, b.product_id
LIMIT 3;

+---------------------------+-----------------+----------------+
| product_a                 | product_b       | times_together |
+---------------------------+-----------------+----------------+
| Wireless Mouse            | USB-C Hub       |              2 |
| Python Crash Course       | Wireless Mouse  |              1 |
| Hands-On Machine Learning | SQL Masterclass |              1 |
+---------------------------+-----------------+----------------+

SELECT o.order_id,
       SUM(oi.quantity * oi.unit_price) AS items_total,
       SUM(pay.amount) AS paid_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments pay ON pay.order_id = o.order_id
WHERE o.order_id IN (1, 2, 6, 9)
GROUP BY o.order_id
ORDER BY o.order_id;

+----------+-------------+------------+
| order_id | items_total | paid_total |
+----------+-------------+------------+
|        1 |        2100 |       4200 |
|        2 |        4500 |       4500 |
|        6 |       30000 |      15000 |
|        9 |        4950 |       9900 |
+----------+-------------+------------+

SELECT o.order_id, oi.product_id, oi.quantity * oi.unit_price AS item_value, pay.payment_id, pay.amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments pay ON pay.order_id = o.order_id
WHERE o.order_id IN (1, 6)
ORDER BY o.order_id, oi.product_id, pay.payment_id;

+----------+------------+------------+------------+--------+
| order_id | product_id | item_value | payment_id | amount |
+----------+------------+------------+------------+--------+
|        1 |          1 |       1200 |          1 |   2100 |
|        1 |          4 |        900 |          1 |   2100 |
|        6 |          7 |      15000 |          5 |   5000 |
|        6 |          7 |      15000 |          6 |  10000 |
+----------+------------+------------+------------+--------+

SELECT o.order_id, i.items_total, p.paid_total
FROM orders o
JOIN (SELECT order_id, SUM(quantity * unit_price) AS items_total
      FROM order_items
      GROUP BY order_id) i ON i.order_id = o.order_id
JOIN (SELECT order_id, SUM(amount) AS paid_total
      FROM payments
      GROUP BY order_id) p ON p.order_id = o.order_id
WHERE o.order_id IN (1, 2, 6, 9)
ORDER BY o.order_id;

+----------+-------------+------------+
| order_id | items_total | paid_total |
+----------+-------------+------------+
|        1 |        2100 |       2100 |
|        2 |        4500 |       4500 |
|        6 |       15000 |      15000 |
|        9 |        4950 |       4950 |
+----------+-------------+------------+

SELECT o.order_id, o.status, i.items_total, COALESCE(p.paid_total, 0) AS paid_total,
       i.items_total - COALESCE(p.paid_total, 0) AS balance
FROM orders o
JOIN (SELECT order_id, SUM(quantity * unit_price) AS items_total
      FROM order_items GROUP BY order_id) i ON i.order_id = o.order_id
LEFT JOIN (SELECT order_id, SUM(amount) AS paid_total
           FROM payments GROUP BY order_id) p ON p.order_id = o.order_id
WHERE i.items_total <> COALESCE(p.paid_total, 0)
ORDER BY o.order_id;

+----------+-----------+-------------+------------+---------+
| order_id | status    | items_total | paid_total | balance |
+----------+-----------+-------------+------------+---------+
|        5 | cancelled |       18500 |          0 |   18500 |
|       13 | pending   |       15000 |          0 |   15000 |
+----------+-----------+-------------+------------+---------+

SELECT c.customer_id, c.name, c.city,
       COUNT(DISTINCT o.order_id) AS orders,
       COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
       COUNT(DISTINCT oi.product_id) AS distinct_products,
       MAX(o.order_date) AS last_order
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status <> 'cancelled'
LEFT JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.customer_id, c.name, c.city
ORDER BY revenue DESC, c.customer_id;

+-------------+-----------------+------------+--------+---------+-------------------+------------+
| customer_id | name            | city       | orders | revenue | distinct_products | last_order |
+-------------+-----------------+------------+--------+---------+-------------------+------------+
|           1 | Nadia Rahman    | Dhaka      |      3 |   23600 |                 4 | 2026-04-19 |
|           2 | Tanvir Ahmed    | Chattogram |      3 |   22500 |                 3 | 2026-05-21 |
|           6 | Imran Hossain   | Khulna     |      2 |   19950 |                 3 | 2026-06-02 |
|           3 | Farhana Akter   | Dhaka      |      3 |    9500 |                 5 | 2026-06-09 |
|           7 | Mitu Das        | Dhaka      |      1 |    5500 |                 2 | 2026-05-06 |
|           5 | Sadia Chowdhury | NULL       |      1 |    4000 |                 2 | 2026-03-15 |
|           4 | Rafiq Islam     | Sylhet     |      0 |       0 |                 0 | NULL       |
|           8 | Karim Uddin     | Rajshahi   |      0 |       0 |                 0 | NULL       |
+-------------+-----------------+------------+--------+---------+-------------------+------------+

import pandas as pd
from sqlhelp import con

features = pd.read_sql("""
    SELECT c.customer_id, c.city,
           COUNT(DISTINCT o.order_id) AS orders,
           COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
           COUNT(DISTINCT oi.product_id) AS distinct_products
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status <> 'cancelled'
    LEFT JOIN order_items oi ON oi.order_id = o.order_id
    GROUP BY c.customer_id, c.city
    ORDER BY c.customer_id
""", con)

print(features.shape)
print(features.dtypes)
print(features["revenue"].sum(), (features["orders"] == 0).sum())

(8, 5)
customer_id          int64
city                   str
orders               int64
revenue              int64
distinct_products    int64
dtype: object
85050 2

-- 1
SELECT pay.payment_id, c.name, pay.amount, pay.method
FROM payments pay
JOIN orders o ON o.order_id = pay.order_id
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY pay.payment_id;

-- 2
SELECT c.city, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'delivered'
GROUP BY c.city
ORDER BY revenue DESC;

+------------+---------+
| city       | revenue |
+------------+---------+
| Dhaka      |   38600 |
| Chattogram |   19500 |
| Khulna     |    4950 |
| NULL       |    4000 |
+------------+---------+

-- 3
SELECT pay.method,
       COUNT(*) AS payments,
       SUM(pay.amount) AS total,
       COUNT(DISTINCT o.customer_id) AS customers
FROM payments pay
JOIN orders o ON o.order_id = pay.order_id
GROUP BY pay.method
ORDER BY total DESC;