Use AI to Help Write SQL and Python Analysis

Make generated code explain its joins, preserve the intended records, and reconcile its answer.

An AI-generated query runs without error. It returns an impressively precise total. The total is wrong because each order was repeated once for every related item and refund.

This is one of the most useful lessons in analytical programming: executable code is not necessarily correct analysis. The database follows the query you wrote, including relationships you may not have intended.

AI can help translate a question into SQL or Python, explain an unfamiliar function, and suggest checks. You still need the schema, the meaning of each row, and a small reference result that can be calculated independently.

Readers new to code can follow the tables first and return after the programming bridge in Articles 103 and 105.

Read the relationships before writing the query

Our fictional shop has three tables. orders contains one row per order. items contains one row per line item. refunds contains one row per refund event.

Amounts are integer cents in one teaching currency. This avoids binary floating-point rounding in the monetary arithmetic. Dates are consistently formatted ISO calendar strings under the sample’s reporting convention.

The packet defines the refund table as complete for the selected orders at the snapshot. Thus, no refund rows means zero recorded refunds in this particular example. An incomplete operational feed would require different treatment.

The intended result is one row per order, with its item count, gross amount, refund total, and gross amount less recorded refunds. That final quantity is defined for the exercise; it is not a complete accounting measure of profit or recognized revenue.

Understand why the naive join multiplies values

If O1’s order row is joined directly to its two item rows and two refund rows, it produces four combinations. Its 10,000-cent gross amount appears four times. Each refund appears once for each item.

O2 contributes one combination. A left join preserves O3 even though it has no item rows. Summing this combined table yields 55,000 gross cents and 4,500 refund cents, both inflated relative to the source.

SQLite’s documentation describes how joins form combinations and how left joins retain unmatched rows. Those mechanics are essential to interpreting the result. See SQLite’s SELECT and join documentation.

Adding DISTINCT is not a universal repair. SUM(DISTINCT gross_cents) would treat O1’s and O2’s equal amounts as one distinct value and produce only 15,000 cents. The values are equal, but the orders are different.

Aggregate related records before joining

Bring each related table to the intended order grain first. Count items by order. Sum refunds by order. Then join those one-row-per-order summaries to orders.

This preserves the identity of the orders and makes missing related rows explicit. A left join retains O3. The exercise’s completeness assumption justifies replacing a missing refund summary with zero recorded refunds.

For dates, use an inclusive start and exclusive end: July 1 up to, but not including, August 1. This pattern clearly defines the month for the sample’s ISO dates. Real timestamp data also requires a time-zone and reporting-boundary definition.

Explain these operations in ordinary language before running the query. If you cannot state how many rows should remain, you are not yet ready to trust the total.

Run the complete local example

The following standalone script uses Python’s standard library. It creates only a temporary in-memory teaching database, inserts the fictional records, then enables SQLite’s query-only mode before analysis. It does not connect to an existing database or require credentials.

Save it as join_lab.py in your chosen learning folder and run python3 join_lab.py. Python’s sqlite3 documentation explains the connection and parameter-binding interfaces used here. Bound parameters keep values separate from SQL syntax; they do not by themselves validate the analytical question.

import sqlite3
from collections import Counter, defaultdict

orders = [
    ("O1", "2026-07-02", 10000),
    ("O2", "2026-07-03", 10000),
    ("O3", "2026-07-04", 5000),
]
items = [("I1", "O1"), ("I2", "O1"), ("I3", "O2")]
refunds = [("R1", "O1", 1000), ("R2", "O1", 1000),
           ("R3", "O3", 500)]
start, end = "2026-07-01", "2026-08-01"

con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")
con.executescript("""
CREATE TABLE orders (
    order_id TEXT PRIMARY KEY,
    order_date TEXT NOT NULL,
    gross_cents INTEGER NOT NULL
);
CREATE TABLE items (
    item_id TEXT PRIMARY KEY,
    order_id TEXT NOT NULL REFERENCES orders(order_id)
);
CREATE TABLE refunds (
    refund_id TEXT PRIMARY KEY,
    order_id TEXT NOT NULL REFERENCES orders(order_id),
    amount_cents INTEGER NOT NULL
);
""")
con.executemany("INSERT INTO orders VALUES (?, ?, ?)", orders)
con.executemany("INSERT INTO items VALUES (?, ?)", items)
con.executemany("INSERT INTO refunds VALUES (?, ?, ?)", refunds)
con.commit()
con.execute("PRAGMA query_only = ON")

naive_sql = """
SELECT SUM(o.gross_cents), SUM(r.amount_cents)
FROM orders AS o
LEFT JOIN items AS i ON i.order_id = o.order_id
LEFT JOIN refunds AS r ON r.order_id = o.order_id
WHERE o.order_date >= ? AND o.order_date < ?
"""
naive = con.execute(naive_sql, (start, end)).fetchone()
print("Naive gross/refund cents:", naive)

correct_sql = """
WITH item_counts AS (
    SELECT order_id, COUNT(*) AS item_count
    FROM items
    GROUP BY order_id
), refund_totals AS (
    SELECT order_id, SUM(amount_cents) AS refund_cents
    FROM refunds
    GROUP BY order_id
)
SELECT o.order_id,
       COALESCE(i.item_count, 0) AS item_count,
       o.gross_cents,
       COALESCE(r.refund_cents, 0) AS refund_cents,
       o.gross_cents - COALESCE(r.refund_cents, 0) AS net_cents
FROM orders AS o
LEFT JOIN item_counts AS i ON i.order_id = o.order_id
LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
WHERE o.order_date >= ? AND o.order_date < ?
ORDER BY o.order_id
"""
rows = con.execute(correct_sql, (start, end)).fetchall()

# Independent calculation from the original Python records.
item_counts = Counter(order_id for _, order_id in items)
refund_totals = defaultdict(int)
for _, order_id, amount in refunds:
    refund_totals[order_id] += amount
reference = []
for order_id, order_date, gross in orders:
    if start <= order_date < end:
        refunded = refund_totals[order_id]
        reference.append((order_id, item_counts[order_id],
                          gross, refunded, gross - refunded))
reference.sort()
assert rows == reference
assert len(rows) == 3
assert sum(row[2] for row in rows) == 25000
assert sum(row[3] for row in rows) == 2500
assert sum(row[4] for row in rows) == 22500
for row in rows:
    print(row)
print("Correct net cents:", sum(row[4] for row in rows))
con.close()

The script was executed during preparation of this manuscript using Python 3.12.13 and SQLite 3.53.1. Its numerical results are:

The correct net is 225.00 teaching currency units. The naive query reports 55,000 and 4,500 cents, confirming the multiplication defect.

Understand what the checks establish

The independent Python calculation follows the original records without using the SQL join. Agreement provides a meaningful check on this small example.

The assertions also check row count and separate gross and refund totals. Checking only the final net could miss offsetting errors. A correct-looking difference does not establish that both components are right.

These checks do not prove that an unrelated production query is correct. A different schema may have order revisions, partial refunds, multiple currencies, missing feeds, or different date meanings. Rebuild the reference calculation around the actual definitions.

A useful regression case preserves the feature that caused the defect: one order with multiple items and multiple refunds. Include equal amounts on distinct orders so a mistaken SUM(DISTINCT ...) repair is caught too.

Constrain the real analysis environment

For operational work, begin with authorized sample data and appropriate read-only access. Do not paste credentials into a model prompt. Review generated operations and resource requirements before running them against a shared database.

The teaching script performs setup writes only inside its own in-memory database. That does not imply authorization to create tables in an existing environment. Keep setup, analysis, and any proposed operational mutation clearly separated.

A query can be read-only and still expensive. Inspect the expected scope and use the database’s appropriate review tools for large work. A row limit on the final output does not necessarily limit the work required to produce an aggregate.

Save the analytical meaning with the code

Keep the query, schema, data version, environment, parameter values, and expected result together. Explain why missing refund rows mean zero in this packet and which assumption would invalidate that interpretation.

If the report is rerun later, preserve the period and snapshot definition. A later refund can change the net for an older order under this query because refunds are selected for the orders at the snapshot, not filtered by refund-event month.

That is intentional here. If the question instead concerns refunds issued during July, the schema needs refund dates and the query needs a different definition. AI should identify that distinction before rewriting the SQL.

A reusable prompt

Write a read-only analysis query for this schema and question. Explain each table’s grain, key relationships, join cardinality, date boundaries, null meaning, and expected output grain before coding. Aggregate one-to-many tables where needed. Provide a small independent reference calculation and checks for row counts and component totals. Use bound parameters for values. Do not infer completeness, currency, refund timing, or production access from the sample.

For students: explain the wrong result

Run the script in an appropriate learning environment or trace it on paper. Draw the four O1 combinations created by two item rows and two refund rows, then explain why pre-aggregation removes that multiplication.

Computing students can add a fourth order with no related rows. Business students can explain why the net measure is not profit. Research students can connect the same problem to joining repeated measurements with repeated annotations.

Your goal is to explain the result without relying on AI to repeat the explanation. Running code is only one part of understanding it.

Practice: repair and verify the join

Use the complete script and confirm the output. Then add order O4 for 2,000 cents in July with no items or refunds. Revise the expected checks: four orders, gross 27,000, refunds 2,500, and net 24,500 cents.

Explain what changes if the refund feed is incomplete. Identify why replacing every missing refund summary with zero would then be an unsupported assumption.

Completion check: The original query returns one row per order, gross 25,000 cents, refunds 2,500, and net 22,500. The independent calculation agrees; O3 is retained; equal order amounts remain distinct; and the learner can explain the naive join’s inflated totals.

Stretch: Add parameter validation and a regression case with several related rows on both sides. Preserve a check that the start date is included and the exclusive end date is not.