Understanding SQL for Data Science
SQL turns an analytical question into explicit rules for selecting, combining, and summarizing records. Before writing syntax, define the grain: what does one row represent? An orders table might contain one row per order, while an order_items table contains several rows per order. Confusing these grains can produce plausible but inflated totals. Also define the population, time interval, units, and denominator of every reported metric.
SELECT chooses output expressions; FROM identifies source tables; WHERE restricts input rows. GROUP BY changes the grain by collecting rows with matching keys into groups. Aggregate functions such as COUNT, SUM, and AVG then summarize each group. HAVING filters those groups after aggregation. A useful logical reading order is FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, then ORDER BY. This describes query meaning, not the database engine's physical execution plan.
JOIN combines matching records. An INNER JOIN retains matches only; a LEFT JOIN also retains unmatched rows from its left input, filling right-side columns with NULL. Matching is not necessarily one-to-one: joining an order to three items repeats its order-level attributes three times. Check key uniqueness and relationship cardinality before aggregating joined data. SQL generally preserves duplicate rows unless you explicitly remove or consolidate them.
NULL represents missing or unknown information, not zero. COUNT(*) counts rows, while COUNT(amount) counts non-null amounts. SUM and AVG ignore null inputs; an average over known amounts therefore has a different denominator from the order count. Use IS NULL to test missingness. Replacing missing amounts with zero is a substantive assumption, not merely formatting.
Window functions calculate across related rows without collapsing them. RANK() OVER (ORDER BY revenue DESC) attaches revenue ranks; PARTITION BY would restart calculations within subsets. Ties share ranks and leave gaps. Only the query's final ORDER BY guarantees presentation order. The supplied pandas guide organizes related operations under joins, missing data, grouping, and windowing, offering a useful bridge when moving between SQL and DataFrames. Aggregating in the database can reduce transferred data, but summaries discard detail needed for later checks. Neither a city comparison nor a rank establishes why outcomes differ; SQL describes the records selected by your definitions.
Practical walkthrough
Start with a precise question: for each customer's recorded city, how many January 2025 orders exist, how many have known amounts, and what is their known revenue? Include cities represented in the customer table even when they have no qualifying orders. Assume each customer has one city and each order belongs to one customer. These cities describe customer records, not necessarily delivery locations.
Run the Python example below; it uses only the standard library and an in-memory SQLite database. Window functions require SQLite 3.25 or later. Amounts are stored as integer cents, avoiding floating-point accumulation for money. Dates use consistently formatted YYYY-MM-DD text, whose lexical ordering matches calendar ordering here. Production timestamp data also needs an explicit time-zone policy.
Read the join condition carefully. The January restriction sits in ON, so customers without January orders survive the LEFT JOIN. The interval includes January 1 and excludes February 1. Half-open intervals also work naturally with timestamps, without guessing the final instant of a month.
The city_summary common table expression, introduced by WITH, names an intermediate result. Grouping produces one row per city. COUNT(o.order_id) counts actual matched orders, while COUNT(o.amount_cents) counts known amounts. COALESCE changes a null sum to zero for display; retain the counts because zero alone cannot distinguish missing amounts from no activity.
Finally, rank the city summaries and explicitly sort the output. Validate the result against source records: three January orders, two known amounts, and 3,000 known cents. These reconciliations test query meaning more effectively than checking whether the statement executes successfully.
Worked example
import sqlite3
con = sqlite3.connect(':memory:')
con.executescript('''
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
city TEXT NOT NULL
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date TEXT NOT NULL,
amount_cents INTEGER
);
INSERT INTO customers VALUES
(1, 'Austin'), (2, 'Boston'), (3, 'Chicago');
INSERT INTO orders VALUES
(101, 1, '2025-01-05', 1000),
(102, 1, '2025-01-20', 2000),
(103, 2, '2025-01-10', NULL),
(104, 3, '2025-02-01', 5000);
''')
query = '''
WITH city_summary AS (
SELECT c.city,
COUNT(o.order_id) AS orders,
COUNT(o.amount_cents) AS known_amounts,
COALESCE(SUM(o.amount_cents), 0) AS known_cents
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
AND o.order_date >= '2025-01-01'
AND o.order_date < '2025-02-01'
GROUP BY c.city
)
SELECT city, orders, known_amounts, known_cents,
RANK() OVER (ORDER BY known_cents DESC) AS revenue_rank
FROM city_summary
ORDER BY known_cents DESC, city;
'''
print('city | orders | known_amounts | known_revenue | rank')
for city, orders, known, cents, rank in con.execute(query):
money = f'{cents // 100}.{cents % 100:02d}'
print(f'{city} | {orders} | {known} | {money} | {rank}')
con.close()
Expected output
city | orders | known_amounts | known_revenue | rank
Austin | 2 | 2 | 30.00 | 1
Boston | 1 | 0 | 0.00 | 2
Chicago | 0 | 0 | 0.00 | 2
Austin has two qualifying orders totaling 30.00, with complete amount coverage. Boston has one qualifying order but no known amount: its displayed 0.00 is not evidence of zero actual revenue. Chicago's February order is excluded, yet Chicago remains because customers anchor the left join. Boston and Chicago share rank 2 because ranking uses the displayed known totals. This ranking is reproducible but analytically limited by missingness. Austin's known average order amount is 15.00; Boston's cannot be calculated from these records. Report coverage alongside revenue, and investigate missing amounts before using this table to compare commercial performance across the three cities.
Common mistakes and how to avoid them
Summing order revenue after joining orders to individual items.
Aggregate items to one row per order before joining, or calculate revenue from item-level amounts. Compare row counts and distinct order identifiers before and after the join.
Putting the right table's date filter in WHERE after a LEFT JOIN.
Place the restriction in ON when unmatched customers must remain. A WHERE comparison rejects null-extended rows and can unintentionally remove customers without qualifying orders.
Using COUNT(*) as an order count in this example.
Count the non-null order identifier instead. Chicago still contributes a preserved customer row, so COUNT(*) would incorrectly report one order.
Treating known revenue as complete revenue.
Publish amount coverage and label the total explicitly. Do not silently impute missing amounts as zero when estimating actual spending.
Review questions
What is the result grain before and after city_summary?
The join contains matched order rows plus preserved unmatched customer rows. GROUP BY city reduces these to one row per recorded city.
How does HAVING differ from WHERE?
WHERE filters input rows before grouping. HAVING filters completed groups, for example retaining cities with COUNT(o.order_id) greater than zero.
Why does the window rank not replace GROUP BY?
Grouping creates city totals by collapsing rows. Ranking then annotates those totals without reducing the number of city rows.
Would AVG(amount_cents) return zero for Boston?
No. Its only amount is NULL, so AVG returns NULL. Zero would assert a measured value that the available records do not provide.
