SQL for Field Work

CSVs got you through October and November. Then Maria asked a question that doesn't fit in a spreadsheet — and Dev said the words every data engineer eventually says: "This goes in a database now."

Assumes: Posts 1–2 — Python basics and the intake script. No SQL experience needed; everything is taught from zero.

Before you code: clarify the ask

"Slowest" is doing a lot of unexamined work in Maria's message. Pin it down before you touch a database:

Input: the November intake rows, in a database
Output: average days-to-close by agency, closed requests only — plus the query itself, so anyone can rerun it
Deadline: tomorrow's ops review

That "closed requests only" is the whole game. It's the difference between a metric and a misunderstanding, and you'll see exactly how it breaks in the Break it section.

The minimal concept

Forget everything intimidating about databases. You need four ideas:

A table is a list of dicts with a contract. Post 1 taught you dicts; Post 2's intake normalized every row into one. A table is that idea made permanent: fixed column names, fixed types, enforced by the database instead of by your discipline. EXPECTED_COLUMNS was a Python set that produced a warning note. A table schema is the same contract with teeth.

SQL describes what you want, not how to get it. Your Python script says: loop, accumulate, divide. SQL says: "the average days-to-close, per agency, for closed requests" — and the database figures out the looping. That shift is the entire reason SQL exists.

The 80% is five clauses. Almost every question Maria will ever ask is some combination of:

Clause	What it does	Python equivalent
SELECT	Which columns (and computations) you want back	Building the output dict
FROM	Which table to read	The list you're looping over
WHERE	Which rows qualify	The if inside your loop
GROUP BY	Bucket rows, then aggregate each bucket	Counter (Post 1!)
ORDER BY	Sort the answer	most_common()

JOIN looks up matching rows in another table. That's it. One sentence. Everything else about JOINs is practice, and you'll get it below.

Fix it together: design the schema first

Before a single query, you design the tables — because the schema is the data contract now. Two tables. Not one, not six:

-- schema.sql — the data contract, runnable
CREATE TABLE agencies (
  agency_code TEXT PRIMARY KEY,      -- 'DSNY', 'DEP', 'DOT'
  agency_name TEXT NOT NULL           -- 'Sanitation', ...
);

CREATE TABLE requests (
  id            INTEGER PRIMARY KEY,
  borough       TEXT NOT NULL
                CHECK (borough IN ('Queens','Brooklyn','Bronx','Manhattan','Staten Island')),
  complaint_type TEXT NOT NULL,
  agency_code   TEXT NOT NULL REFERENCES agencies(agency_code),
  created_date  TEXT NOT NULL,       -- ISO 'YYYY-MM-DD'; real datetime handling lands in Post 5
  closed_date   TEXT,                -- NULL means still open
  status        TEXT NOT NULL DEFAULT 'open'
                CHECK (status IN ('open','closed')),
  -- the business invariant Maria's metric depends on, enforced:
  CHECK (
    (status = 'open' AND closed_date IS NULL)
    OR (status = 'closed' AND closed_date IS NOT NULL)
  )
);

Why two tables instead of one? Because agency names are free text, and free text rots. The day someone writes "Dept. of Sanitation" instead of "Sanitation", a single-table GROUP BY splits one agency into two and nobody notices. One agencies table, referenced by code — the same instinct as Post 1's KNOWN_BOROUGHS, now enforced by the database instead of a Python set. And the borough CHECK is that same set, grown up: try to insert 'Qeens' and the database refuses at the door. The final CHECK goes one step further — it encodes a relationship between columns: open means no close date, closed means there is one. A data contract isn't just "this field is text"; it can encode the invariants that must stay true between fields, and this one happens to be the invariant Maria's entire metric rests on.

Loading it is a dozen lines of stdlib Python — no new packages, no server to install. SQLite lives inside Python itself, with one gotcha worth knowing upfront: it parses REFERENCES but doesn't enforce foreign keys unless you ask, per connection. Declaring the constraint without enabling it looks safer than it is — exactly the kind of assumption this course teaches you to verify:

import sqlite3

db = sqlite3.connect("cityops.db")
db.execute("PRAGMA foreign_keys = ON")       # without this, orphan agency_codes slip in
db.executescript(open("schema.sql").read())  # create tables; run once

rows = [
    ("Queens", "Noise", "DEP", "2026-11-01", "2026-11-04", "closed"),
    ("Queens", "Noise", "DEP", "2026-11-02", "2026-11-10", "closed"),
    # ... the rest of the intake output; agency_code comes from Dev's roster
]
with db:  # the transaction is the unit of success
    db.executemany(
        """INSERT INTO requests
           (borough, complaint_type, agency_code, created_date, closed_date, status)
           VALUES (?, ?, ?, ?, ?, ?)""",
        rows,
    )
# leaving the with block commits; a contract violation inside it rolls everything back

The ? placeholders matter — you'll see why in Production-safe. For the rest of this post, work with a cleaned 10-row excerpt of the November intake sitting in cityops.db. Every output below is the real result of running the query above it.

Query 1: your Counter, translated

Post 1's entire analysis — top complaint types — in one statement. Read it as a sentence: "for each complaint type, count the rows, biggest first."

SELECT complaint_type, COUNT(*) AS n
FROM requests
GROUP BY complaint_type
ORDER BY n DESC;

complaint_type | n
Noise          | 5
Sanitation     | 4
Parking        | 1

GROUP BY is Counter wearing a suit. Same bucketing instinct, now running inside the database where Maria's future questions live too.

Query 2: JOIN the agency names

The requests table stores agency_code — compact, typo-proof. But Maria wants to read "Sanitation", not "DSNY". The JOIN looks up each code in the agencies table:

SELECT r.id, r.borough, a.agency_name, r.complaint_type
FROM requests r
JOIN agencies a ON r.agency_code = a.agency_code
ORDER BY r.id
LIMIT 3;

id | borough | agency_name            | complaint_type
1  | Queens  | Environmental Protection | Noise
2  | Queens  | Environmental Protection | Noise
3  | Queens  | Sanitation             | Sanitation

The r and a are aliases — nicknames so you don't type requests.borough fifty times. ON states the match rule: rows join where the codes are equal. Rows whose code matches nothing simply vanish from an inner JOIN — remember that; it's in the Break it table.

The same JOIN, pointed at a different question — what didn't match? Run this whenever counts look off:

SELECT r.id, r.agency_code
FROM requests r
LEFT JOIN agencies a ON r.agency_code = a.agency_code
WHERE a.agency_code IS NULL;

(no rows — every request matched an agency)

An inner JOIN answers the business question. This LEFT JOIN answers the debugging question. On clean data it returns nothing, and that silence is the answer: nothing is orphaned.

Query 3: answer Maria (CTEs are just named steps)

Now the real question. A CTE (the WITH block) is a named intermediate result — a way to break a hard query into readable steps instead of one nested monster:

WITH closed AS (
  SELECT agency_code,
         julianday(closed_date) - julianday(created_date) AS days_open
  FROM requests
  WHERE status = 'closed'
)
SELECT a.agency_name,
       ROUND(AVG(c.days_open), 1) AS avg_days,
       COUNT(*) AS closed_n
FROM closed c
JOIN agencies a ON c.agency_code = a.agency_code
GROUP BY a.agency_name
ORDER BY avg_days DESC;

agency_name            | avg_days | closed_n
Environmental Protection | 5.2      | 5
Sanitation             | 1.7      | 3
Transportation         | 1.0      | 1

Read it top to bottom: first build closed (days open for closed requests only), then average per agency, then attach the readable names. The WHERE status = 'closed' is Maria's clarified requirement, encoded where nobody can forget it. julianday is SQLite's date-difference function — the date arithmetic itself gets its proper treatment in Post 5; today it's a means to an end.

Before you send it, reconcile. The principle of this post isn't a slogan — it's two queries:

SELECT COUNT(*) AS total FROM requests;

SELECT
  SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) AS closed_n,
  SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) AS open_n
FROM requests;

total
10

closed_n | open_n
9        | 1

9 closed + 1 open = 10 total. And Maria's per-agency closed counts — 5 + 3 + 1 — sum to the same 9. Every request is accounted for, twice, from two different angles. That's what "don't trust a metric you can't reconcile" means in practice.

Query 4: window functions rank within groups

Maria's follow-up, arriving ten minutes later (of course): "And the top two complaint types per borough?" GROUP BY gives you one ranking. A window function ranks inside each group separately — PARTITION BY borough means "restart the ranking for every borough":

WITH counts AS (
  SELECT borough, complaint_type, COUNT(*) AS n
  FROM requests
  GROUP BY borough, complaint_type
),
ranked AS (
  SELECT borough, complaint_type, n,
         ROW_NUMBER() OVER (PARTITION BY borough ORDER BY n DESC, complaint_type) AS rnk
  FROM counts
)
SELECT borough, complaint_type, n
FROM ranked
WHERE rnk <= 2
ORDER BY borough, rnk;

borough  | complaint_type | n
Bronx    | Noise          | 1
Brooklyn | Sanitation     | 2
Brooklyn | Noise          | 1
Queens   | Noise          | 3
Queens   | Sanitation     | 2

Two CTEs, each doing one job: count, then rank. The second sort key, complaint_type, makes ties deterministic — without it, two categories tied at 8 would take ranks 2 and 3 in an order you'd be unwise to rely on. You don't need every window function today. Remember the pattern: when Maria asks for a ranking within each group, PARTITION BY is usually where to look.

Break it: the ways metrics lie

SQL's danger isn't that it errors — it's that it answers confidently. Every row below is a query that runs fine and misleads you.

The trap	What it does	The fix
AVG over NULLs	AVG() silently ignores NULLs. Average "days to close" over all rows quietly becomes "average over closed rows" — possibly what you want, but the query doesn't say so.	The schema's cross-column CHECK guarantees a closed row always carries a date — so filter explicitly (WHERE status = 'closed') and label what the metric covers. Invariant in the schema, intent in the query.
Missing WHERE	Run the days-open average without the status filter: you get 3.56 — a real number answering a question nobody asked, with the open request silently excluded.	Encode the clarified requirement in the query. If Maria said "closed only", the query says WHERE status = 'closed'.
SQLite's GROUP BY leniency	SQLite lets you SELECT borough, complaint_type, COUNT(*) grouped by borough alone — complaint_type comes from an arbitrary row. Postgres would refuse; SQLite shrugs.	Every non-aggregated column in SELECT must be in the GROUP BY. Don't rely on the database to scold you.
Case-mismatch JOIN	'dsny' vs 'DSNY' in the join keys — the rows silently vanish from an inner JOIN. Your counts drop and nothing errors.	Normalize keys at intake (Post 2's boundary rule). Diagnose orphans with LEFT JOIN ... WHERE a.agency_code IS NULL.
Duplicate loads	The same source record lands twice under different local IDs — id is just our counter, so it can't catch this. COUNT(*) happily counts both.	Preserve a stable source-system ID, make it UNIQUE, and make ingestion idempotent — rerunning a load must never duplicate yesterday's records.

Production-safe: the contract has teeth now

The schema rejects bad data at the door. Post 2's EXPECTED_COLUMNS produced a warning note in the manifest. Watch what the same mistake meets now:

>>> db.execute(
...     "INSERT INTO requests (borough, complaint_type, agency_code, created_date, closed_date, status)"
...     "VALUES ('Qeens', 'Noise', 'DEP', '2026-11-08', '2026-11-09', 'closed')")
sqlite3.IntegrityError: CHECK constraint failed: borough IN ('Queens','Brooklyn','Bronx','Manhattan','Staten Island')

No manifest note. No quarantine file. The row never enters. That is the data contract as a runnable artifact — schema drift gets rejected programmatically before it can pollute a single downstream table. The cross-column rule bites the same way — a "closed" request with no close date is a contradiction, and the database treats it as one:

>>> db.execute(
...     """INSERT INTO requests (borough, complaint_type, agency_code,
...        created_date, closed_date, status)
...        VALUES ('Queens', 'Noise', 'DEP', '2026-11-08', ?, 'closed')""",
...     (None,))
sqlite3.IntegrityError: CHECK constraint failed: (status = 'open' AND closed_date IS NULL)
    OR (status = 'closed' AND closed_date IS NOT NULL)

Never build SQL with f-strings. The ? placeholders in the loader aren't decoration — values travel separately from the query text:

borough = "Queens"  # imagine this came from a web form someday
cur = db.execute(
    "SELECT complaint_type, COUNT(*) FROM requests WHERE borough = ?",
    (borough,),
)

The day a value comes from a user instead of your script, one stray quote character in an f-string turns Tom's borough into a database command. Placeholders close that hole permanently. (Lisa nods approvingly.)

Make the transaction explicit. The with db: block marks the unit of success: if every row satisfies the contract, the batch commits; if one row violates it, the whole batch rolls back instead of leaving a partial load behind. Don't equate commit() with safety — the transaction boundary is what makes a load atomic, and you should be able to point at it. That's the exact property Milestone 2's scheduled pipeline will depend on.

Explain it to the customer

Maria gets the answer, the caveats, and the rerunnable query — in that order:

"Maria — slowest to close is Environmental Protection at 5.2 days on average (5 closed requests), then Sanitation at 1.7 (3), then Transportation at 1.0 (1). Two caveats before the review: one request is still open and excluded from the average, and this is a 10-row cleaned excerpt of the November intake, so treat it as a first cut — the full-intake numbers follow once the pipeline loads them. Reconciled: 9 closed + 1 open = 10 total rows, and the per-agency counts sum to the same 9. The query is attached; it reruns against the live database any time."

Notice what's doing the heavy lifting: the caveats. "Closed requests only" and "10-row excerpt" are the difference between a metric and a misunderstanding — the same clarification you extracted on Wednesday morning, now disclosed before anyone has to ask.

Where this lands in CityOps

cityops.db is now the project's memory, and schema.sql is checked into the repo next to intake.py. The pipeline shape is emerging: intake quarantines the bad rows, the database refuses the bad values, the manifest records what arrived, and queries answer Maria's questions in sentences instead of scripts. In Milestone 2 this becomes the scheduled loop — Socrata pull, clean, load, reconcile — with the schema as the contract every stage must satisfy.

One thing the schema doesn't have yet: our id is a local counter. Before the scheduled pipeline goes live, we'll preserve the source request ID and enforce uniqueness on it — so rerunning an intake never duplicates yesterday's records. Plant that flag now: idempotency is a pipeline requirement, not a nice-to-have.

Post 2's principle was don't trust data just because it parsed. Post 3's: don't trust a metric you can't reconcile.