Data analytics tutorial: build a monthly revenue report with SQLite

A spreadsheet is useful for inspecting rows, but SQL makes repeated questions easier to reproduce. In this tutorial, build an in-memory database, group fictional orders by month and compare channels. Everything disappears when the program closes, so the exercise cannot modify your website database.

1. Define the question

We want paid revenue per calendar month, not the number of times an order was updated. Store one row per order and use an integer amount in minor currency units. For naira, 100 kobo equals one naira; using integers avoids introducing floating-point rounding into stored amounts.

You need Python 3.12 or later. SQLite is included in standard Python distributions. Save the complete example as `monthly_report.py`.

2. Create data and run a parameterised query

import sqlite3

db = sqlite3.connect(":memory:")
try:
    db.execute("""CREATE TABLE orders (
        id TEXT PRIMARY KEY,
        ordered_on TEXT NOT NULL,
        channel TEXT NOT NULL,
        amount_kobo INTEGER NOT NULL CHECK(amount_kobo >= 0),
        status TEXT NOT NULL
    )""")
    rows = [
        ("A1", "2026-09-02", "email", 1200000, "paid"),
        ("A2", "2026-09-18", "search", 800000, "paid"),
        ("A3", "2026-10-03", "email", 1500000, "paid"),
        ("A4", "2026-10-05", "search", 500000, "cancelled"),
    ]
    db.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?)", rows)
    monthly = db.execute("""
        SELECT substr(ordered_on, 1, 7) AS month,
               COUNT(*) AS paid_orders,
               SUM(amount_kobo) AS revenue_kobo
        FROM orders
        WHERE status = ?
        GROUP BY month
        ORDER BY month
    """, ("paid",)).fetchall()
    for month, count, amount in monthly:
        print(month, count, f"NGN {amount / 100:,.2f}")
    assert monthly == [("2026-09", 2, 2000000), ("2026-10", 1, 1500000)]
finally:
    db.close()

3. Read the output

Run `python monthly_report.py`. September should show two paid orders worth NGN 20,000.00, while October shows one worth NGN 15,000.00. The cancelled October order is excluded. The final assertion protects the exercise from a query that accidentally includes it.

The question mark in the query is a parameter placeholder. Pass values separately rather than inserting user input into SQL strings. The same approach works when you later filter by a channel supplied by a form.

4. Add a second dimension

To investigate channel performance, add `channel` to the selected columns and to the `GROUP BY` clause. Expect September to split into two rows. Explain the change to anyone consuming the report: the meaning of a row has changed from one month to one month-and-channel combination.

This example uses ISO-style dates so extracting the first seven characters produces a month key. Before applying that shortcut to another export, validate its date format and decide which timezone defines a reporting day. Text such as `03/04/2026` cannot safely be interpreted without knowing the convention.

Troubleshooting and extension

A primary-key error means you tried to insert the same order twice. Investigate the duplicate instead of removing the constraint. A missing month does not automatically mean zero sales; it can also indicate missing input. Extend the exercise by adding a table of expected months and joining it to your results, so absent data becomes visible.

References

Python sqlite3 documentation · SQLite SELECT reference