Chapter 6 · Project and Next Steps
Project: A Sales Analytics Report for BitByte Shop
- Page 21 of 22
- 18 min read
Time to use everything at once. The manager of BitByte Shop wants a sales report: how much did we sell each month, which products and customers matter most, how do people pay, and which orders need attention? You will answer each question with one query, check the numbers against each other, and finish with a Python script that runs the whole report and saves every table as a CSV file, ready for a chart or a spreadsheet.
This is the daily work of a data analyst, and also the first step of most ML projects: before anyone trains a churn model or a recommender, someone has to turn raw order rows into trustworthy numbers per month, per product and per customer.
What you will learn
- How to turn a vague business question into a precise rule (what counts as revenue?) and apply it everywhere.
- How to combine joins,
GROUP BY,CASE,LEFT JOINand subqueries into report queries. - How to check a report: totals that must agree, rows that must not disappear.
- How to run the report from Python with pandas and export it to CSV.
The brief and the ground rules
Every report starts with definitions. If two people count "revenue" differently, their numbers never match and nobody trusts either. Write the rules down before the first query:
| Term | Rule used in this report |
|---|---|
| Sold order | An order with status delivered or shipped. Cancelled and pending orders are reported separately. |
| Revenue | quantity * unit_price from order_items of sold orders: the price actually charged, discounts included. |
| Month | The month of order_date, as YYYY-MM. |
| Average order value | Revenue ÷ number of sold orders, computed from one total per order. |
First, a quick look at the size of the data and at the order statuses, so you know what you are counting:
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 |
+-----------+--------+14 orders, of which 12 count as sold. Keep those numbers in mind; every later result must be consistent with them.
Step 1: The headline numbers
Average order value needs one total per order first, then an average of those totals. Averaging item rows directly would give the average item value instead. So build the per-order totals in a subquery and summarise them outside:
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 |
+--------+-----------+---------+-----------------+---------------+12 sold orders from 6 customers, 70,050 taka in total. Notice the gap between the average (about 5,800) and the largest order (18,500): a few big orders, a monitor and a bootcamp, carry a large share of the revenue.
Step 2: Revenue by month
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 |
+---------+--------+---------+What to notice:
COUNT(DISTINCT o.order_id), notCOUNT(*): after the join there is one row per item, and an order with two items would be counted twice.- The six monthly figures add up to 70,050, the headline total. That is your first consistency check.
- March and April are big because of two expensive orders (the bootcamp and the monitor), not because of more orders. Always look at the count next to the sum.
strftimeis SQLite. In MySQL writeDATE_FORMAT(order_date, '%Y-%m'), in PostgreSQLto_char(order_date, 'YYYY-MM')(see Text, Number and Date Functions, CAST and COALESCE).
A month with no sales would simply be missing from this result, and a chart would quietly skip it. CTEs and Recursive Queries, the first page of the advanced tutorial, builds a calendar with a recursive CTE so that empty months show up as 0.
Step 3: Top products, and products that never sold
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 |
+---------------------------+-------+---------+----------+The discount column compares the list price with the price charged: the keyboard was once sold for 4,050 instead of 4,500. This is why revenue always uses order_items.unit_price, never products.price: the product table holds today's price, the order item holds what the customer really paid.
The products that never appear in any order need a LEFT JOIN and a check for the missing side (see LEFT, RIGHT, FULL, CROSS and Self Joins):
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 |
+------------+-----------------------------+-------+Eight headphones sitting in stock, unsold: a useful finding for the manager.
Step 4: Best customers
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 |
+-------------+---------------+------------+--------+---------+-------------+------------+Number of orders, revenue and last order date per customer: these three numbers (how often, how much, how recently) are exactly the RFM features (recency, frequency, monetary) that churn and lifetime-value models are built on. You will build them as ML features in the advanced tutorial.
Who brought in nothing? Put the per-customer revenue in a subquery and LEFT JOIN all customers to it:
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 |
+-------------+-------------+---------+Two different stories: Karim never ordered, while Rafiq ordered once and cancelled. A "customers without orders" query (using the orders table alone) would find only Karim. Which list you want depends on the question, so say which one you report.
Step 5: Category performance
Every category should appear, including Furniture with no products. So start from categories, LEFT JOIN the products, and LEFT JOIN a subquery with sales per product. The share of revenue divides by the overall total, taken from a scalar subquery:
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 |
+-------------+----------+-------+---------+----------------+Check: 31,550 + 21,000 + 8,900 + 8,600 = 70,050, and the percentages add up to 100. The Gift Card has no category, so it cannot appear in this list; that is safe here only because it never sold. If it had, the category total would fall short of the headline total, which is exactly why you compare them.
Notice the sales rule status IN ('delivered', 'shipped') is now written twice in one query. In the advanced tutorial a CTE (WITH sold AS (…)) lets you define it once and reuse it.
Step 6: Payment mix
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 |
+--------+----------+--------+--------+------+Card brings in almost two thirds of the money. Add up the orders column: 6 + 5 + 2 = 13, but only 12 orders were paid. Order 6 was paid in two parts, bKash and card, so it counts once in each method. Neither number is wrong; they answer different questions. Say "payments" when you count payments.
Step 7: Orders that need attention
The last check reconciles what each order is worth with what was paid for it. Aggregate items and payments separately, then join the two totals; joining the raw rows would multiply order 6's items by its two payments (the trap from Joining Many Tables, and the Duplicate-Row Trap):
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 |
+----------+-----------+-------+------+--------+Only two orders do not balance, and both are expected: the cancelled order was never paid, and the pending one is still waiting for payment (15,000 taka at risk). Every sold order is fully paid. In a real shop, this query would run every night and anything unexpected in it would raise an alert.
Step 8: The report as a Python script
Queries in a notebook are not a report yet. Put them in a script so the whole report reruns in one command, and save each result as a CSV that a spreadsheet, a chart or a dashboard can read. Save this as report.py next to shop.db:
"""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')" # the business rule, written once
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")
# Reconcile: revenue from items must equal money received for sold orders.
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 5000Run it with:
python report.pyDesign choices worth copying:
- One place for the rule.
SOLDis defined once, so changing the definition of a sale changes every table together. It is a fixed piece of SQL written by you, which is why an f-string is acceptable here; values typed by a user must always be passed as parameters, never pasted into SQL (the advanced tutorial shows why, live). - The report checks itself. The
assertstops the script if revenue and payments ever disagree, so a broken number never reaches the manager silently. - Plain CSV output. Every tool can read it: Excel, Google Sheets, pandas, a BI dashboard, a training pipeline.
- The connection is closed in
finally, even when a query fails.
Look at what was written:
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,3100From CSV to a chart
A quick text chart needs no library at all, which is handy in a terminal or a log:
monthly = pd.read_csv("report/monthly_revenue.csv")
for row in monthly.itertuples():
bar = "#" * round(row.revenue / 1000) # one # per 1,000 taka
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 ### 3100For a real chart, use Matplotlib as in Charts with Matplotlib (Python for AI); this saves a bar chart as a PNG next to the CSV files:
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")Where this shows up
- Dashboards (Metabase, Power BI, Looker Studio) are exactly these queries, run on a schedule and drawn as charts.
- ML features: the per-customer table from Step 4 is the starting point of a churn or lifetime-value dataset; the per-product table feeds demand forecasting and recommendations.
- AI product analytics: swap orders for LLM calls and revenue for tokens or cost, and the same monthly, per-model and per-user queries tell you where an AI feature spends its money.
Common mistakes
- Counting cancelled orders as revenue. Without the status filter, February looks 18,500 taka better (the cancelled monitor) and June 15,000 better (the unpaid pending order). Fix: agree the rule first and apply it in every query (one
SOLDconstant in code). - Using
products.pricefor revenue. It ignores discounts and price changes; the keyboard sold for 4,050, not 4,500. Fix:SUM(oi.quantity * oi.unit_price). - Joining items and payments in one go. Order 6 has one item and two payments, so its item value is counted twice. Fix: aggregate each side in its own subquery, then join the totals.
- Losing rows with an inner join. Furniture vanishes from the category table and Karim from the customer list. Fix: start from the table whose rows must all appear and
LEFT JOINthe rest, withCOALESCE(…, 0). - Trusting a number without a cross-check. Fix: make totals meet: monthly sum = category sum = headline revenue = payments for sold orders.
Try it yourself
- Easy: Revenue by city for sold orders, with customers who have no city shown as
Unknown, biggest first. - Easy: How many items (sum of quantity) does a sold order contain on average? Round to 2 decimals.
- Medium: A month × category table: one row per month, one column each for Books, Electronics, Courses and Accessories revenue (conditional aggregation, see CASE WHEN, Conditional Counts and Business Questions).
- Medium: Add a
category_revenuequery toQUERIESinreport.pyand run the report again. - Hard: For each sold order, the number of days between the order date and the date it was fully paid (the last payment). List only orders paid later than the order date.
Answers
-- 1. Revenue by city
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. Average items per sold order
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. Month x category revenue
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. Days from order to full payment
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. One more entry in QUERIES, then rerun the report
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))Summary
- A report starts with written rules: what counts as a sale, what revenue means, which date decides the month.
- Revenue is
SUM(quantity * unit_price)over sold orders; count orders withCOUNT(DISTINCT order_id)after a join. - Start from the table whose rows must all appear and
LEFT JOINthe rest; aggregate one-to-many sides separately before joining them. - Cross-check totals: months, categories, the headline number and payments must agree.
- A Python script with one dictionary of queries,
pd.read_sql_queryandto_csvturns the queries into a repeatable, self-checking report.
Next: Summary, Cheat Sheet and What's Next collects the syntax of the whole beginner tutorial on one page and shows where the advanced tutorial takes you, starting with CTEs that would make the long queries on this page much easier to read.