প্রজেক্ট: BitByte Shop-এর বিক্রয় বিশ্লেষণ রিপোর্ট

SELECT 'orders' AS table_name, COUNT(*) AS n_rows FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL
SELECT 'payments', COUNT(*) FROM payments;

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC, status;

+-------------+--------+
| table_name  | n_rows |
+-------------+--------+
| orders      |     14 |
| order_items |     20 |
| payments    |     13 |
+-------------+--------+
+-----------+--------+
| status    | orders |
+-----------+--------+
| delivered |     11 |
| cancelled |      1 |
| pending   |      1 |
| shipped   |      1 |
+-----------+--------+

SELECT COUNT(*)                   AS orders,
       COUNT(DISTINCT customer_id) AS customers,
       SUM(total)                 AS revenue,
       ROUND(AVG(total), 2)       AS avg_order_value,
       MAX(total)                 AS largest_order
FROM (
    SELECT o.order_id, o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS total
    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.order_id, o.customer_id
) AS t;

+--------+-----------+---------+-----------------+---------------+
| orders | customers | revenue | avg_order_value | largest_order |
+--------+-----------+---------+-----------------+---------------+
|     12 |         6 |   70050 |          5837.5 |         18500 |
+--------+-----------+---------+-----------------+---------------+

SELECT strftime('%Y-%m', o.order_date)  AS month,
       COUNT(DISTINCT o.order_id)        AS orders,
       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
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 |
+---------+--------+---------+

SELECT p.name,
       SUM(oi.quantity)                                AS units,
       SUM(oi.quantity * oi.unit_price)                AS revenue,
       SUM(oi.quantity * (p.price - oi.unit_price))    AS discount
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
WHERE o.status IN ('delivered', 'shipped')
GROUP BY p.product_id, p.name
ORDER BY revenue DESC, p.product_id
LIMIT 5;

+---------------------------+-------+---------+----------+
| name                      | units | revenue | discount |
+---------------------------+-------+---------+----------+
| 27-inch Monitor           |     1 |   18500 |        0 |
| AI Engineering Bootcamp   |     1 |   15000 |        0 |
| Mechanical Keyboard       |     2 |    8550 |      450 |
| SQL Masterclass           |     2 |    6000 |        0 |
| Hands-On Machine Learning |     2 |    5000 |        0 |
+---------------------------+-------+---------+----------+

SELECT p.product_id, p.name, p.stock
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.product_id
WHERE oi.product_id IS NULL
ORDER BY p.product_id;

+------------+-----------------------------+-------+
| product_id | name                        | stock |
+------------+-----------------------------+-------+
|         10 | Noise-Cancelling Headphones |     8 |
|         11 | Gift Card                   | NULL  |
+------------+-----------------------------+-------+

SELECT c.customer_id,
       c.name,
       COALESCE(c.city, 'Unknown')       AS city,
       COUNT(DISTINCT o.order_id)        AS orders,
       SUM(oi.quantity * oi.unit_price)  AS revenue,
       MIN(o.order_date)                 AS first_order,
       MAX(o.order_date)                 AS last_order
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY c.customer_id, c.name, c.city
ORDER BY revenue DESC, c.customer_id
LIMIT 5;

+-------------+---------------+------------+--------+---------+-------------+------------+
| customer_id | name          | city       | orders | revenue | first_order | last_order |
+-------------+---------------+------------+--------+---------+-------------+------------+
|           1 | Nadia Rahman  | Dhaka      |      3 |   23600 | 2026-01-12  | 2026-04-19 |
|           2 | Tanvir Ahmed  | Chattogram |      3 |   22500 | 2026-01-20  | 2026-05-21 |
|           3 | Farhana Akter | Dhaka      |      3 |    9500 | 2026-02-14  | 2026-06-09 |
|           7 | Mitu Das      | Dhaka      |      1 |    5500 | 2026-05-06  | 2026-05-06 |
|           6 | Imran Hossain | Khulna     |      1 |    4950 | 2026-04-02  | 2026-04-02 |
+-------------+---------------+------------+--------+---------+-------------+------------+

SELECT c.customer_id, c.name, COALESCE(r.revenue, 0) AS revenue
FROM customers AS c
LEFT JOIN (
    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
) AS r ON r.customer_id = c.customer_id
WHERE r.customer_id IS NULL
ORDER BY c.customer_id;

+-------------+-------------+---------+
| customer_id | name        | revenue |
+-------------+-------------+---------+
|           4 | Rafiq Islam |       0 |
|           8 | Karim Uddin |       0 |
+-------------+-------------+---------+

SELECT c.name                          AS category,
       COUNT(p.product_id)             AS products,
       COALESCE(SUM(s.units), 0)       AS units,
       COALESCE(SUM(s.revenue), 0)     AS revenue,
       ROUND(100.0 * COALESCE(SUM(s.revenue), 0) / (
           SELECT SUM(oi.quantity * oi.unit_price)
           FROM order_items AS oi
           JOIN orders AS o ON o.order_id = oi.order_id
           WHERE o.status IN ('delivered', 'shipped')
       ), 1)                           AS pct_of_revenue
FROM categories AS c
LEFT JOIN products AS p ON p.category_id = c.category_id
LEFT JOIN (
    SELECT oi.product_id,
           SUM(oi.quantity) AS units,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM order_items AS oi
    JOIN orders AS o ON o.order_id = oi.order_id
    WHERE o.status IN ('delivered', 'shipped')
    GROUP BY oi.product_id
) AS s ON s.product_id = p.product_id
GROUP BY c.category_id, c.name
ORDER BY revenue DESC, c.category_id;

+-------------+----------+-------+---------+----------------+
| category    | products | units | revenue | pct_of_revenue |
+-------------+----------+-------+---------+----------------+
| Electronics |        4 |     8 |   31550 |           45.0 |
| Courses     |        2 |     3 |   21000 |           30.0 |
| Accessories |        2 |     5 |    8900 |           12.7 |
| Books       |        2 |     5 |    8600 |           12.3 |
| Furniture   |        0 |     0 |       0 |            0.0 |
+-------------+----------+-------+---------+----------------+

SELECT method,
       COUNT(*)                 AS payments,
       COUNT(DISTINCT order_id) AS orders,
       SUM(amount)              AS amount,
       ROUND(100.0 * SUM(amount) / (SELECT SUM(amount) FROM payments), 1) AS pct
FROM payments
GROUP BY method
ORDER BY amount DESC, method;

+--------+----------+--------+--------+------+
| method | payments | orders | amount | pct  |
+--------+----------+--------+--------+------+
| card   |        6 |      6 |  44950 | 64.2 |
| bkash  |        5 |      5 |  18000 | 25.7 |
| cash   |        2 |      2 |   7100 | 10.1 |
+--------+----------+--------+--------+------+

SELECT o.order_id,
       o.status,
       t.total,
       COALESCE(pay.paid, 0)           AS paid,
       t.total - COALESCE(pay.paid, 0) AS unpaid
FROM orders AS o
JOIN (
    SELECT order_id, SUM(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
) AS t ON t.order_id = o.order_id
LEFT JOIN (
    SELECT order_id, SUM(amount) AS paid
    FROM payments
    GROUP BY order_id
) AS pay ON pay.order_id = o.order_id
WHERE t.total <> COALESCE(pay.paid, 0)
ORDER BY o.order_id;

+----------+-----------+-------+------+--------+
| order_id | status    | total | paid | unpaid |
+----------+-----------+-------+------+--------+
|        5 | cancelled | 18500 |    0 |  18500 |
|       13 | pending   | 15000 |    0 |  15000 |
+----------+-----------+-------+------+--------+

"""BitByte Shop sales report: run the analysis queries and save each result as CSV."""
import sqlite3
from pathlib import Path

import pandas as pd

SOLD = "o.status IN ('delivered', 'shipped')"   # ব্যবসার নিয়ম, একবারই লেখা

QUERIES = {
    "monthly_revenue": f"""
        SELECT strftime('%Y-%m', o.order_date) AS month,
               COUNT(DISTINCT o.order_id) AS orders,
               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 {SOLD}
        GROUP BY month
        ORDER BY month""",
    "top_products": f"""
        SELECT p.name, SUM(oi.quantity) AS units,
               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
        WHERE {SOLD}
        GROUP BY p.product_id, p.name
        ORDER BY revenue DESC, p.product_id
        LIMIT 5""",
    "best_customers": f"""
        SELECT c.name, COUNT(DISTINCT o.order_id) AS orders,
               SUM(oi.quantity * oi.unit_price) AS revenue,
               MAX(o.order_date) AS last_order
        FROM customers AS c
        JOIN orders AS o ON o.customer_id = c.customer_id
        JOIN order_items AS oi ON oi.order_id = o.order_id
        WHERE {SOLD}
        GROUP BY c.customer_id, c.name
        ORDER BY revenue DESC, c.customer_id""",
    "payment_mix": """
        SELECT method, COUNT(*) AS payments, SUM(amount) AS amount
        FROM payments
        GROUP BY method
        ORDER BY amount DESC, method""",
}


def run_report(db_path="shop.db", out_dir="report"):
    out = Path(out_dir)
    out.mkdir(exist_ok=True)
    con = sqlite3.connect(db_path)
    try:
        results = {}
        for name, sql in QUERIES.items():
            df = pd.read_sql_query(sql, con)
            df.to_csv(out / f"{name}.csv", index=False)
            results[name] = df
            print(f"{name:16} {len(df):2} rows -> {out / name}.csv")

        # মিলিয়ে দেখা: আইটেম থেকে পাওয়া আয় = বিক্রি হওয়া অর্ডারে পাওয়া টাকা
        paid = pd.read_sql_query(
            f"SELECT SUM(p.amount) AS paid FROM payments AS p "
            f"JOIN orders AS o ON o.order_id = p.order_id WHERE {SOLD}", con
        )["paid"].iloc[0]
        revenue = results["monthly_revenue"]["revenue"].sum()
        assert revenue == paid, f"revenue {revenue} does not match payments {paid}"
        print(f"check passed: revenue {revenue} = payments {paid}")
        return results
    finally:
        con.close()


if __name__ == "__main__":
    report = run_report()
    print()
    print(report["top_products"].to_string(index=False))

monthly_revenue   6 rows -> report/monthly_revenue.csv
top_products      5 rows -> report/top_products.csv
best_customers    6 rows -> report/best_customers.csv
payment_mix       3 rows -> report/payment_mix.csv
check passed: revenue 70050 = payments 70050

                     name  units  revenue
          27-inch Monitor      1    18500
  AI Engineering Bootcamp      1    15000
      Mechanical Keyboard      2     8550
          SQL Masterclass      2     6000
Hands-On Machine Learning      2     5000

python report.py

print(Path("report/monthly_revenue.csv").read_text())

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

monthly = pd.read_csv("report/monthly_revenue.csv")
for row in monthly.itertuples():
    bar = "#" * round(row.revenue / 1000)     # প্রতি 1,000 টাকায় একটা #
    print(f"{row.month} {bar:24} {row.revenue:>6}")

2026-01 #######                    6600
2026-02 #######                    7000
2026-03 #####################     21400
2026-04 #######################   23450
2026-05 ########                   8500
2026-06 ###                        3100

import matplotlib.pyplot as plt

monthly = pd.read_csv("report/monthly_revenue.csv")
ax = monthly.plot.bar(x="month", y="revenue", legend=False, rot=0)
ax.set_title("BitByte Shop revenue by month (taka)")
plt.tight_layout()
plt.savefig("report/monthly_revenue.png")

-- 1. শহরভিত্তিক আয়
SELECT COALESCE(c.city, 'Unknown') AS city,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers AS c
JOIN orders AS o       ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY COALESCE(c.city, 'Unknown')
ORDER BY revenue DESC, city;

-- 2. বিক্রি হওয়া অর্ডারে গড় আইটেম
SELECT ROUND(AVG(items), 2) AS avg_items
FROM (
    SELECT oi.order_id, SUM(oi.quantity) AS items
    FROM order_items AS oi
    JOIN orders AS o ON o.order_id = oi.order_id
    WHERE o.status IN ('delivered', 'shipped')
    GROUP BY oi.order_id
) AS t;

-- 3. মাস x ক্যাটাগরি আয়
SELECT strftime('%Y-%m', o.order_date) AS month,
       SUM(CASE WHEN p.category_id = 1 THEN oi.quantity * oi.unit_price ELSE 0 END) AS books,
       SUM(CASE WHEN p.category_id = 2 THEN oi.quantity * oi.unit_price ELSE 0 END) AS electronics,
       SUM(CASE WHEN p.category_id = 3 THEN oi.quantity * oi.unit_price ELSE 0 END) AS courses,
       SUM(CASE WHEN p.category_id = 4 THEN oi.quantity * oi.unit_price ELSE 0 END) AS accessories
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p     ON p.product_id = oi.product_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY month
ORDER BY month;

-- 5. অর্ডার থেকে পুরো টাকা পাওয়া পর্যন্ত দিন
SELECT o.order_id, o.order_date, MAX(pay.paid_on) AS fully_paid,
       CAST(julianday(MAX(pay.paid_on)) - julianday(o.order_date) AS INTEGER) AS days
FROM orders AS o
JOIN payments AS pay ON pay.order_id = o.order_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY o.order_id, o.order_date
HAVING MAX(pay.paid_on) > o.order_date
ORDER BY days DESC, o.order_id;

# 4. QUERIES-এ আরেকটা এন্ট্রি, তারপর রিপোর্ট আবার চালান
QUERIES["category_revenue"] = f"""
    SELECT COALESCE(cat.name, 'Uncategorised') 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
    LEFT JOIN categories AS cat ON cat.category_id = p.category_id
    WHERE {SOLD}
    GROUP BY category
    ORDER BY revenue DESC, category"""

report = run_report()
print(report["category_revenue"].to_string(index=False))