Working with Data

This block in one sentence

A number is a sentence before it is a query: a measure, over a population, after a filter.

SQL is that sentence written precisely enough for a machine to answer it and for a colleague to check it. NULL is waiting at every clause.

Agenda

1. The method: build one clause at a time
2. Why SQL: 525,461 lines, watched
3. The map: the order a query runs
4. The number as a sentence · the counts · the other aggregates · NULL in them
5. GROUP BY, its rule, the NULL group
6. The sum identity: the numbers tie out
7. A calculated column · dates · text
8. Lab 2 — on the other file

The method: build one clause at a time

1. The sentence: the measure, the population, the filter.
2. FROM + LIMIT 10: look at ten rows. Are they the thing?
3. WHERE: count the rows it keeps. The number you expected?
4. GROUP BY: say how many groups before you run it.
5. The aggregates. Then ORDER BY. Remove the LIMIT.
6. The check: compute the number a second way. The two must tie out.

Why SQL — 525,461 lines, watched

A query can be read, rerun, and checked. The file is the table: FROM names it, nothing is loaded.

The lecture file: one row is one country in one year

Watch (cell 1 of lecture.ipynb): Block 1's key test, on the lecture file.

219 countries, in the file's sense, 1990 to 2024. One row per country per year?

The map: the order a query runs

You write SELECT first. It runs fourth.

The sentence first, then the query

Total CO₂ emitted by countries in 2024 is the sum of co2 (the measure) over the country rows (the population) of 2024 (the filter).

In plain English, a sentence says three things: what the number measures, which rows it comes from, and which rows the query left out.

Predict, then watch (cell 2):

con.sql("""
    SELECT SUM(co2) AS total_co2
    FROM 'data/raw/countries.csv'
    WHERE year = 2024
""").df()

Predict: how many rows come back? One per country, or one in all?

The sentence first, then the query — what it printed

One row, not one per country: an aggregate with no GROUP BY collapses the whole filtered table into a single row.

The three counts, and what NULL does to them

Predict, then watch (cell 3). Predict: are n_rows and n_co2 equal?

con.sql("""
    SELECT COUNT(*)                AS n_rows,
           COUNT(co2)              AS n_co2,
           COUNT(DISTINCT country) AS n_countries
    FROM 'data/raw/countries.csv'
    WHERE year = 2024
""").df()

The three counts, and what NULL does to them — what it printed

COUNT(*) counts rows. COUNT(co2) counts the rows where co2 is not NULL. COUNT(DISTINCT country) counts the different values.

Rows are not things

Predict, then watch (cell 4): the same two counts, on every year.

con.sql("""
    SELECT COUNT(*)                AS n_rows,
           COUNT(DISTINCT country) AS n_countries
    FROM 'data/raw/countries.csv'
""").df()

Predict: are the two numbers equal this time?

Rows are not things — what it printed

One row is one country in one year. COUNT(*) counted rows. COUNT(DISTINCT country) counted countries. Say what one row is before you count.

The other four aggregates — watch

Watch (cell 5): SUM, AVG, MIN, MAX, on 2024.

AVG divides by the values that exist: 216, not 219.

COALESCE: a missing value becomes a zero, and a zero is a claim

Watch (cell 6). COALESCE(gdp, 0) returns the first of its values that is not NULL: gdp where there is one, otherwise 0.

GROUP BY: one row per year

Watch (cell 7). One row per year.

GROUP BY — what it printed

The rule GROUP BY enforces

Predict, then watch (cell 8a; it is wrong on purpose). Predict: does DuckDB pick a country, or refuse?

SELECT year, country, SUM(co2) FROM 'data/raw/countries.csv' GROUP BY year

year is the identifier of each group. country is a label: a column that is neither grouped nor aggregated.

The rule GROUP BY enforces — what it printed

Every column in SELECT is either in the GROUP BY or inside an aggregate. year is grouped, SUM(co2) is aggregated, country is neither: refused.

The fix: put the column in the GROUP BY

Watch (cell 8b): the identifier and its label, grouped together.

The NULL group

Predict, then watch (cell 9). Predict: how many rows — two, or three?

con.sql("""
    SELECT gdp > 1000000000000 AS big_economy, COUNT(*) AS countries, SUM(co2) AS co2
    FROM 'data/raw/countries.csv'
    WHERE year = 2022
    GROUP BY big_economy
    ORDER BY big_economy
""").df()

The NULL group — what it printed

Three rows. The <NA> row is the 55 countries whose gdp is NULL: is it above a trillion? is neither true nor false for them, so they are a group of their own. A WHERE gdp > 1e12 would keep 26; a WHERE gdp <= 1e12 would keep 138; the 55 would be in neither.

The sum identity: the numbers tie out

Predict, then watch (cell 10). Predict: do the three groups, added up, equal the total?

Emissions are additive: every breakdown adds up to the total, computed by other code, or one of them is wrong. Finance says the numbers reconcile, or tie out. This course says identity: the equation a number must satisfy.

Compare sums with a tolerance, never ==. Two correct sums of the same numbers can differ in the tenth decimal. Money: to the penny, abs(a - b) < 0.005.

Means and ratios have their own identities. An average does not add up. A ratio's top and bottom are reconciled separately, on the same rows.

The sum identity: the numbers tie out — what it printed

The same number, twice. == says False; within half a thousandth says True. The second query is a subquery: a query inside a query, SELECT SUM(…) FROM ( … GROUP BY … ).

A calculated column, inside the aggregate

Watch (cell 11): tonnes per person, by year. co2 is in million tonnes and population in persons, so co2 * 1000000 / population.

The expression goes inside the SUM; AS names the result; ROUND(…, 2) is for display.

Dates, the minimum

Watch (cell 12). One timestamp, three ways:

date_trunc gives the start of its month, a value you can GROUP BY. year gives the number. strftime gives text, for a label.

Text, the minimum: left

Watch (cell 13). left(country, 1) is the first character of the name; a census can group by it.

What you just saw, as one idea

A filter that sounds right can throw away rows the sentence keeps.
WHERE gdp > 1e12 keeps 26 countries and WHERE gdp <= 1e12 keeps 138; the 55 with no GDP figure are in neither. GROUP BY showed them; WHERE would not have.

A ratio can be built from two different populations.
SUM(gdp) / COUNT(*) is a top from 164 countries over a bottom of 219: 595 billion, the same wrong number as counting the missing as zero.

No error in either. The sentence says what the number counts; the query must do all of it, and only it. The check finds them.

Git thread — the message names the cause

git commit -m "Keep countries with no GDP figure in the breakdown: gdp <= 1e12 dropped 55 NULL rows"

not git commit -m "fix". Commit after each fix. Six months from now, git log --oneline is the only diagnosis note anyone will read.

AI thread — the assistant does not know your data

Ask an assistant: "Average GDP per country in 2022, from this table", and paste DESCRIBE. It will write AVG(gdp). Then ask it: "over how many countries?"

It cannot know. COUNT(gdp) against COUNT(*): 164 against 219.

The assistant does not know your data's conventions unless you paste them. The census is how you learn them yourself.

5-minute stand-and-stretch — start the Lab 2 clone first

In your terminal (Git Bash on Windows, Terminal on macOS), in the folder where you keep course work:

git clone https://github.com/earino/ecbs5294-lab02-one-table.git
cd ecbs5294-lab02-one-table
uv sync

Start it, then take your break: it downloads while you are away.

Lab 2 — The revenue the brief did not ask for

The file: data/raw/online_retail.parquet — the shop's invoice lines. The brief: finance's definitions of every number, in the README and the notebook.

The symptom: the report runs without an error and says 2010 revenue was £8,739,637.52 and the average identified customer brought in £2,052.52. Its own per-customer table does not average to £2,052.52. And every number has a sentence in the brief.

First minute: the clone finished? Open the folder in VS Code, open notebooks/report.ipynb, pick the .venv kernel, run the first cell: it prints the Lab 2 folder.

Your work, in report.ipynb, top to bottom. A: run the three supplied inspections and paste what they show into your note. B: the brief's filter, written once as a view sales_2010, then the census again on it. C: revenue by month, from an empty cell. D: the five countries with the most invoices, from an empty cell. E: the check: your view against the raw file, your months against your view, both to the penny. Then DIAGNOSIS.md. After the note, if there is time: F, the per-customer figure (also HW1 question 7). Neighbour at minute 33. Checkpoint on Moodle. The syntax is on one page: REFERENCE-sql.md; the full reference, with a task index, is on the course site.

The last ten minutes — explain it to a neighbour

One of you explains, about a minute, pointing at the screen: what was wrong · why · the query that proved it · what you changed · how you know it is right. The other asks:

1. Show me the query that proves it.
2. Why was it wrong, not just where?
3. A colleague says your view's filter is the long way round: WHERE Quantity > 0 does the same job. Same lines? Same revenue?

Then swap. Unsure, or you disagree? Hands up. Then the checkpoint on Moodle.

Three kinds of check

Every number has a check its kind permits. The homework names the kind under each question.

If no query would catch the error: write how the number could be wrong, and what you would see if it were.

Homework 1 assigned — One table, the weekly numbers

The head of sales wants seven numbers from the same file; two more are stretch, not graded. The brief carries Lab 2's definitions and a few more. About 5 hours for the seven. Due Saturday 10 October, 23:59, on Moodle.

The starter is earino/ecbs5294-hw01-one-table on GitHub; its README has the clone line, for home. It opens with a worked example.

Five parts under every question, in the notebook:

1. The sentence: what the number measures, which rows it comes from, which rows the query left out
2. The row estimate, with its source: the brief, a section 0 cell, or "neither fixes this"
3. The query, and the number
4. The check, in the kind the question names: a sum, a ranked list, a ratio
5. One line: how would I know if this were wrong?

Then the reconciliation — by month = by country = the total, to the penny. Commits that name the cause · GIT_LOG.txt · the zip, as SUBMITTING.md says · AI_USE.md.