Welcome to the "heavy lifting" part of the week. In Week 6, you learned how to use Python to push data into PostgreSQL using INSERT and how to peek at it using SELECT *.
But in a real data pipeline, the raw data is usually a mess. It's fragmented across different tables and full of codes that don't make sense to humans. Today, we move beyond just "reading" data and start "transforming" it using the three pillars of analytical SQL: Joins, Aggregations and CTEs.
In Week 6, you created a single weather_readings table. In our NYC Taxi dataset, life is more complex. We have Trips (the events) and Zones (the labels).
If you look at the trips table, you’ll see pickuplocationid (Pickup) and dropofflocationid (Dropoff). These are just numbers, like 43 or 132. To turn "43" into "Central Park," we need a JOIN.
A JOIN allows us to combine multiple tables into a single one, combining the elements of each table together based on common identifiers. These common identifiers should be IDs that allow us to tell which row of Table A corresponds to each row of Table B.
<aside>
📘 Core Program Refresher: You met JOIN and GROUP BY in the Core program SQL track. The mechanics are the same here; what is new is applying them to a fact table (nyc_taxi.raw_trips) and a dimension table (nyc_taxi.raw_zones) at analytical scale. For a refresher, see SQL Basics: DDL and DML.
</aside>
We have different types of JOIN depending on what we want to keep from our two original tables:
Venn-diagram comparison of INNER, LEFT, RIGHT and FULL JOIN
The type of JOIN determines what we want to keep from each dataset regarding the rows that did not have a match in A and B. In our dataset, this would be the output:
Now let’s look at how we can translate this to SQL syntax!
Let’s say we are asked to get the latest 10 trips with pickup location in Manhattan, including pickup time, trip distance and fare amount. This is how we can quickly get this info:
SELECT
z.borough,
z.zone AS pickup_zone,
t.pickup_datetime,
t.trip_distance,
t.fare_amount
FROM nyc_taxi.raw_trips t
INNER JOIN nyc_taxi.raw_zones z
ON t.pickup_location_id = z.location_id
WHERE z.borough = 'Manhattan' -- This command filters the pickup location to Manhattan
ORDER BY pickup_datetime DESC -- As we want to retrieve the latest trips, we sort by date descending
LIMIT 10;
nyc_taxi.raw_trips t and nyc_taxi.raw_zones z. We use these short nicknames so we don't have to type the full table name every time.ON t.pickup_location_id = z.location_id tells Postgres exactly how these two worlds are connected.<aside> 💡 Do you start seeing the benefit of joining multiple tables instead of storing everything in one? If zone names were repeated in the trips table, we'd introduce a lot of redundancy.
</aside>
Let's keep in mind that strings are the heaviest data types to process, hence working with numerical short IDs brings great performance improvements!
Now let’s see how we turn 57,000 rows (or millions of rows in real work life!) into just a few rows of pure insight. Analysts don't want to see every single taxi ride; they often want to see the Average Fare or Total Trips per day.
To do this, we use Aggregate Functions (SUM, AVG, COUNT, MIN, MAX) combined with a GROUP BY clause. Anything that follows the GROUP BY will be what defines how detailed our aggregation will be.
<aside> ⚠️ The golden rule of aggregation: Every column in your SELECT statement must either be wrapped in an aggregate function (like SUM()) OR be listed in the GROUP BY clause. If you forget this, Postgres will throw an error that essentially says: "I don't know what to do with the extra columns!"
</aside>
Let's look at how we can add multiple aggregations in a single query:
SELECT
z.borough,
COUNT(*) AS total_trips,
AVG(t.fare_amount) AS avg_fare,
SUM(t.trip_distance) AS total_distance
FROM nyc_taxi.raw_trips t
INNER JOIN nyc_taxi.raw_zones z
ON t.pickup_location_id = z.location_id
GROUP BY z.borough
ORDER BY total_trips DESC;
You can see how in a single query we got the total number of trips per borough, their average fare and the total distance!
<aside>
⌨️ Hands on: Run the borough-aggregation query above against nyc_taxi.raw_trips and nyc_taxi.raw_zones. Which borough has the most trips? Read the top row of your result to find out.
</aside>
<aside> 🚀 Try it in the widget: Run this in the in-browser SQL playground (real Postgres, no setup). Then switch to the Join trips to zone names and Busiest day with a CTE examples to see the same shapes you just read.
</aside>
In Python, you wouldn't write a 100-line script inside a single function. You’d break it into steps. In SQL, we do this with CTEs (Common Table Expressions) using the WITH keyword.
CTEs are "temporary result sets" that exist only during the execution of that query. They make your SQL readable, debuggable, and, honestly, much more professional.
<aside> 🤓 Curious Geek: WITH arrived late
The WITH keyword that powers CTEs only joined the SQL standard in 1999, nearly 30 years after SQL itself. Before that, analysts nested subqueries inside subqueries. PostgreSQL has shipped CTEs since version 8.4 (2009), and they can even be recursive, which lets a single query walk a tree or a chain of references.
</aside>
The pattern:
WITH cleaned_trips AS (
SELECT * FROM nyc_taxi.raw_trips
WHERE fare_amount > 0 AND trip_distance > 0
),
trips_with_boroughs AS (
SELECT
ct.*,
z.borough
FROM cleaned_trips ct
JOIN nyc_taxi.raw_zones z ON ct.pickup_location_id = z.location_id
)
SELECT
borough,
COUNT(*) as trip_count
FROM trips_with_boroughs
GROUP BY 1 -- Shortcut for "first column in SELECT"
ORDER BY 2 DESC;
<aside> 💡 Pro-tip: If you see a query with nested subqueries (queries inside queries inside queries), refactor it into CTEs. Your future self (and your teammates) will thank you.
</aside>
Ever written a query that never finishes, or suddenly returns billions of rows? You likely created a Cartesian Join (or Cross Join). The callout below explains why.
<aside> 🤓 Curious Geek: The Cartesian Product (The Explosion)
A Cartesian product happens when you join two tables but forget the ON clause, or use a join condition that isn't unique. Postgres will try to match every row of Table A with every row of Table B. If both tables have 1,000 rows, you just created a 1,000,000 row result set. In our taxi data, a mistake like this could crash your session!
</aside>
If you are worried a join might be too heavy or hang your session, you can prefix the query with EXPLAIN to preview the database planner's execution steps and estimated row counts without actually running the query.
For example, running EXPLAIN on a Cartesian product:
EXPLAIN SELECT *
FROM nyc_taxi.raw_trips t
CROSS JOIN nyc_taxi.raw_zones z;
Returns a query plan that looks like this:
Nested Loop (cost=0.00..189020.01 rows=14986015 width=156)
-> Seq Scan on raw_trips t (cost=0.00..1688.51 rows=56551 width=118)
-> Materialize (cost=0.00..6.98 rows=265 width=38)
-> Seq Scan on raw_zones z (cost=0.00..5.65 rows=265 width=38)
Read this output from the deepest indented step upward:
Seq Scan on raw_zones: The planner scans the lookup table (265 rows).Materialize: It caches those 265 rows in memory so it doesn't read them off disk again and again.Seq Scan on raw_trips: It scans the trips table (56,551 rows).Nested Loop (Top Node): It loops through and matches every trip with every zone. The resulting estimated row count is rows=14986015 ($56,551 \times 265$). Seeing a number this large on a simple join is your warning sign that you created a Cartesian product!When a JOIN or GROUP BY error stumps you, an LLM can decode the message fast.
<aside>
💡 Using AI to help: Paste your query and its error message into an LLM and ask what went wrong (⚠️ Ensure no PII or sensitive company data is included!). The NYC taxi data here is public, so sample rows from nyc_taxi.raw_trips are safe to share, but never paste real customer data from a job.
</aside>
pickup_location_id that doesn't exist in nyc_taxi.raw_zones?)vendor_id. Which aggregate function do you use, and which column goes into the GROUP BY?sum(my_list)?Want to check your understanding before moving on? Try the interactive quiz for this chapter.
<aside> 🚀 Try it in the widget: Interactive Quiz: Joins, CTEs, and Aggregations
</aside>
https://lasse.be/simple-hyf-teach-widget/mcq.html?bank=week_9_ch2_joins_ctes_aggregations_quiz&embed=1
If the join diagrams above did not click the first time, this short visual walk-through covers them from a different angle.
<aside> 🎬 Struggling with this concept? Watch this beginner-friendly video:
</aside>
https://www.youtube.com/watch?v=9yeOJ0ZMUYw
Ready to apply what you just read?
<aside> ⌨️ Hands on: Practice with Exercise 1: Join trips to zone names, Exercise 2: Trips and average fare per borough, Exercise 3: Find the busiest day with a CTE, Exercise 4: Refactor a nested subquery into CTEs, and Exercise 5: Compare a cartesian join to a filtered join, where you practice writing joins, CTEs, and profiling query plans.
</aside>
Next up: Data Validation Queries, where you write defensive SQL to catch duplicates, NULLs, and orphaned keys before they reach a dashboard.