These are the mistakes that bite students this week: silent row explosions, NULLs that vanish from counts, filters that land in the wrong clause, and joins that quietly inflate your totals. Skim this list now so the symptoms look familiar, then come back when a query gives you a number that feels wrong.
Every example uses the Week 9 dataset: nyc_taxi.raw_trips (aliased t, ~57K rows) and nyc_taxi.raw_zones (aliased z, 265 rows).
Symptom: a query that should return a few thousand rows returns millions, or never finishes. You used a comma join with no condition linking the tables, so Postgres pairs every trip with every zone (57,000 x 265 = over 15 million rows).
-- Before (broken): comma join with nothing linking the tables is a cross join
select t.fare_amount, z.borough
from nyc_taxi.raw_trips t, nyc_taxi.raw_zones z;
-- Fix: write an explicit join on the matching key
select t.fare_amount, z.borough
from nyc_taxi.raw_trips t
join nyc_taxi.raw_zones z
on t.pickup_location_id = z.location_id;
Why: a comma join (from nyc_taxi.raw_trips t, nyc_taxi.raw_zones z) with no where linking the tables is a cross join, multiplying row counts instead of matching them. Writing the join as join ... on ... protects you: if you leave off the on, Postgres raises a syntax error instead of silently exploding.
<aside>
⚠️ If a join makes your row count grow instead of shrink or stay flat, stop and check the on clause before running anything heavier.
</aside>
Symptom: count(*) and count(tip_amount) return different numbers on the same query, and a group by shows an unexpected extra row with a blank key. Some trips have a NULL payment_type, and count(column) skips them.
-- Before: you assume both counts are equal
select count(*) as all_rows,
count(tip_amount) as rows_with_tip
from nyc_taxi.raw_trips t;
count(*) counts every row. count(tip_amount) counts only rows where tip_amount is not null, so the two differ whenever tips are missing.
-- Fix: be explicit about what you are counting
select count(*) as all_rows,
count(*) filter (where tip_amount is not null) as rows_with_tip,
count(*) filter (where tip_amount is null) as rows_missing_tip
from nyc_taxi.raw_trips t;
Why: count(*) counts rows, count(col) counts non-NULL values, and group by puts all NULLs into a single separate group rather than ignoring them.
Symptom: you try to filter on an aggregate in WHERE and Postgres rejects the query. WHERE runs before aggregation, so it cannot see count(*) or avg(...) yet.
-- Before (broken): aggregate filter in WHERE
select z.borough, count(*) as trips
from nyc_taxi.raw_trips t
join nyc_taxi.raw_zones z on t.pickup_location_id = z.location_id
where count(*) > 1000
group by z.borough;
Postgres raises:
ERROR: aggregate functions are not allowed in WHERE
-- Fix: filter rows in WHERE, filter groups in HAVING
select z.borough, count(*) as trips
from nyc_taxi.raw_trips t
join nyc_taxi.raw_zones z on t.pickup_location_id = z.location_id
where t.fare_amount > 0 -- per-row filter, before grouping
group by z.borough
having count(*) > 1000; -- per-group filter, after aggregation
Why: WHERE filters individual rows before grouping; HAVING filters the grouped results after the aggregate is computed.
Symptom: a join fails because the same column name lives on more than one table and you referenced it without a table alias. This bites when you join nyc_taxi.raw_zones twice, once for the pickup zone and once for the dropoff zone: now borough exists under both aliases.
-- Before (broken): borough is unqualified, and it exists on both zone joins
select borough
from nyc_taxi.raw_trips t
join nyc_taxi.raw_zones zp on t.pickup_location_id = zp.location_id
join nyc_taxi.raw_zones zd on t.dropoff_location_id = zd.location_id;
Postgres raises:
ERROR: column reference "borough" is ambiguous
-- Fix: qualify every column with its table alias
select zp.borough as pickup_borough, zd.borough as dropoff_borough
from nyc_taxi.raw_trips t
join nyc_taxi.raw_zones zp on t.pickup_location_id = zp.location_id
join nyc_taxi.raw_zones zd on t.dropoff_location_id = zd.location_id;
Why: when a column name lives on more than one joined table, Postgres cannot guess which one you mean, so it errors until you prefix it with an alias.
Symptom: a sum(fare_amount) comes out far larger than the real revenue. A trip has both a pickup and a dropoff location, so joining nyc_taxi.raw_zones twice (or on the wrong key) can match one trip to multiple zone rows and count its fare more than once.
-- Before: joining zones on both pickup and dropoff
-- fans each trip into multiple rows, double-counting fare
select sum(t.fare_amount) as total_fare
from nyc_taxi.raw_trips t
join nyc_taxi.raw_zones z
on t.pickup_location_id = z.location_id
or t.dropoff_location_id = z.location_id;
-- Fix: keep the grain at one row per trip, join only on the key you need
select sum(t.fare_amount) as total_fare
from nyc_taxi.raw_trips t
join nyc_taxi.raw_zones z
on t.pickup_location_id = z.location_id;
Why: a one-to-many join multiplies the rows on the "many" side, so any sum or count over the duplicated rows is inflated. Decide the grain (one row per trip) and join so it stays that way.
<aside>
⚠️ Whenever a metric looks too big after adding a join, suspect fan-out first. Compare count(*) before and after the join: if it grew, your grain changed.
</aside>
Symptom: slow queries and unreadable output. select * pulls every column (including ones you never use) and hides what the query actually depends on.
-- Before: drags every column across the join, including duplicates
select *
from nyc_taxi.raw_trips t
join nyc_taxi.raw_zones z on t.pickup_location_id = z.location_id;
-- Fix: name only the columns you need
select t.pickup_datetime, t.fare_amount, z.borough
from nyc_taxi.raw_trips t
join nyc_taxi.raw_zones z on t.pickup_location_id = z.location_id;
Why: naming columns moves less data, makes the query self-documenting, and avoids surprises when the underlying table gains or reorders columns.
Symptom: a trip count drops after an inner join, or named-zone reports quietly miss rows. The raw data does not guarantee that every pickup_location_id in nyc_taxi.raw_trips exists in nyc_taxi.raw_zones. Always validate before you trust a join.
-- Validation: are there pickup ids with no matching zone?
select count(*) as orphaned_trips
from nyc_taxi.raw_trips t
left join nyc_taxi.raw_zones z on t.pickup_location_id = z.location_id
where z.location_id is null;
For the January 2024 green taxi dataset this returns 0: every pickup_location_id has a matching zone. That is the result you want to see. In production data, the count is often not zero, and an inner join would silently drop those trips. Run this check first so you know whether an inner join is safe or whether you need a left join to keep the unmatched rows.
Why: raw source tables are not always clean. Checking for orphaned keys first tells you whether an inner join will lose data before you build a report on top of it.
Symptom: a view that was correct last week now returns stale or wrong numbers after nyc_taxi.raw_trips was reloaded. A view stores the query, not a snapshot of results, but a view written against assumptions that no longer hold can silently misbehave.
-- A view created earlier in the week
create view trips_by_borough as
select z.borough, count(*) as trips
from nyc_taxi.raw_trips t
join nyc_taxi.raw_zones z on t.pickup_location_id = z.location_id
group by z.borough;
After reloading the underlying tables, re-run the checks behind the view: orphaned-key validation, row counts, and a spot-check of one borough total. Do not assume a view that compiled once is still correct against new data.
Why: a view always reflects the current state of its source tables, so any data quality issue in a fresh load flows straight through it. Re-validate after every reload.
Symptom: your INSERT or load step runs for a very long time, or Azure PostgreSQL reports it is out of storage. The full NYC taxi dataset is millions of rows per month across many years. The class only uses one month.
For this week, load only January 2024 green taxi data (~57K rows) into nyc_taxi.raw_trips. Do not load the full multi-year dataset.
Why: the shared Azure Postgres instance has limited storage, and one month is more than enough rows to practice every join, aggregation, and CTE in this week. Loading the full dataset fills the disk and blocks your classmates.
<aside>
⚠️ Before bulk-loading, confirm you are pointing at the single-month file (~57K rows), not the full historical dump. Check the row count with select count(*) from nyc_taxi.raw_trips; right after loading: it should read roughly 57,000, not millions.
</aside>
count(*), count(col), and filter treat NULLs.Next up: Glossary, where you'll find quick-reference definitions for every SQL and data-warehousing term used this week.
The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*
Built with ❤️ by the HackYourFuture community · Thank you, contributors
Found a mistake or have a suggestion? Let us know in the feedback form.