Unit 11.00: Deciding what to aggregate in SQL versus pandas

Unit ID: SQL-M11-U01 - Estimated active time: 14-17 minutes Objective: choose where each step of an analysis runs, and justify the choice.

The default that scales

Aggregate in SQL, analyse the small result in pandas.

import duckdb, pandas as pd

con = duckdb.connect()
con.execute(open("schema.sql").read())
con.execute(open("seed.sql").read())

df = con.execute("""
    SELECT status, COUNT(*) AS orders, SUM(order_total) AS revenue
    FROM orders
    GROUP BY status
    ORDER BY revenue DESC
""").df()

print(df)
#       status  orders    revenue
# 0  completed     988  2685905.0
# 1    pending      12    15558.0

Two rows crossed the boundary instead of a thousand. On a real table of ten million rows this is the difference between a query that returns and one that exhausts memory.

The rule of thumb

Do it in SQL	Do it in pandas
Filtering, joining, grouping, aggregating	Plotting and presentation
Anything that reduces row count	Statistical modelling
Work the database is optimised for	Iterative, exploratory reshaping of a small result

The boundary is row count. Reduce first, then move.

When pulling raw rows is right

Sometimes you genuinely need the detail - modelling, row-level export, or an operation SQL expresses badly. Then pull deliberately and say so:

full = con.execute("SELECT order_id, order_total FROM orders").df()
print(len(full))   # 1000

A thousand rows is fine. The mistake is pulling ten million out of habit and then calling .groupby() on something the database could have reduced in one pass.

Practice

You need average order value per country for a chart. Where does the aggregation belong, and why?

Check your answer

In SQL. The chart needs at most five rows - one per country including unknown - so there is no reason to move a thousand order rows into Python first.

df = con.execute("""
    SELECT COALESCE(c.country, 'Unknown') AS country,
           COUNT(*)                       AS orders,
           ROUND(AVG(o.order_total), 2)   AS avg_order_value
    FROM orders o
    JOIN customers c ON c.customer_id = o.customer_id
    GROUP BY 1
    ORDER BY orders DESC
""").df()

pandas then does what it is good at: formatting and plotting a five-row frame.

Takeaway

Reduce in SQL, present in pandas. The boundary is row count, and the default direction is to aggregate before you move.

---