These exercises drill the SQL skills from this week against the same NYC Taxi data you used in the chapters: nyc_taxi.raw_trips (~57K green-taxi rides from January 2024) and nyc_taxi.raw_zones (265 location lookups). You read these tables from the shared, read-only nyc_taxi schema, and you create any views in your own assigned schema on the shared Azure PostgreSQL instance, never in nyc_taxi or public.
Work through them in order. Each exercise starts easy and builds toward the kind of audit-and-model work the assignment asks for: joins first, then aggregations, then CTEs, then validation, then views. A hint and a collapsible reference solution sit under each task. Try the query yourself before opening the solution.
<aside>
📝 These practice exercises are optional, but strongly recommended before you start the assignment. The assignment reuses the exact patterns drilled here: validating raw data and building vw_fact_trips and vw_dim_zones.
</aside>
Every exercise below also lives as a ready-to-run .sql file under data-track/week-9/ in HYF's Learning-Resources repo. Each folder ships a starter exercise.sql (the task with TODO markers) and a solutions/exercise.sql with the reference query plus -- WHY notes. Clone the folder once and open the files in any SQL client (psql, DBeaver, VS Code SQLTools):
git clone <https://github.com/HackYourFuture/Learning-Resources.git>
cd Learning-Resources/data-track/week-9
These are SQL files, not Python, so there is no environment to set up. Run each query on the shared Azure PostgreSQL: the raw tables are read from the nyc_taxi schema (already prefixed in every query). Only Exercise 7 writes anything, and it creates its views in your own assigned schema with CREATE OR REPLACE VIEW (safe to re-run).
| Exercise | Folder |
|---|---|
| 1. Join trips to zone names | exercise_1/ |
| 2. Trips and average fare per borough | exercise_2/ |
| 3. Find the busiest day with a CTE | exercise_3/ |
| 4. Refactor a nested subquery into CTEs | exercise_4/ |
| 5. Cartesian vs filtered join | exercise_5/ |
| 6. Validate the raw data | exercise_6/ |
| 7. Build views, then query them | exercise_7/ |
| 8. Detect and fix join fan-out | exercise_8/ |
<aside>
💡 Time-box yourself: try each query for 10 to 20 minutes before opening solutions/. The reference solutions are also inlined below each exercise as collapsible blocks, so you can stay in this page if you prefer.
</aside>
Each trip stores a pickup location as a number like 43, not a name. Join nyc_taxi.raw_trips to nyc_taxi.raw_zones so you can read the pickup zone name for each trip.
Show the pickup datetime, trip distance, fare amount, and the pickup zone name for the first 5 trips.
<aside>
💡 The bridge between the two tables is t.pickup_location_id = z.location_id. Use an INNER JOIN and LIMIT 5.
</aside>
Aggregate trips up to the borough level. For each pickup borough, count the trips and compute the average fare.
Which borough has the most trips?
<aside>
💡 Join to nyc_taxi.raw_zones to get borough, then GROUP BY z.borough. Every non-aggregated column in the SELECT must appear in the GROUP BY.
</aside>
Build a CTE that counts trips per calendar day, then query that CTE to find the single busiest day.
Which date had the most pickups?
<aside>
💡 Use pickup_datetime::date to drop the time part. Define the daily counts in a WITH block, then ORDER BY ... DESC LIMIT 1 over the CTE.
</aside>
The query below works, but the nested subqueries make it hard to read. Rewrite it using CTEs so each step is named and readable. The result must stay identical: the average fare per borough, for trips with a positive fare only.
SELECT
borough,
ROUND(AVG(fare_amount), 2) AS avg_fare
FROM (
SELECT
z.borough,
t.fare_amount
FROM (
SELECT *
FROM nyc_taxi.raw_trips
WHERE fare_amount > 0
) t
INNER JOIN nyc_taxi.raw_zones z
ON t.pickup_location_id = z.location_id
) joined
GROUP BY borough
ORDER BY avg_fare DESC;
<aside>
💡 Pull each subquery out into its own named step: one CTE to filter positive fares, one to join to zones, then a final SELECT that aggregates.
</aside>
A missing join condition produces a cartesian product: every trip matched to every zone. Use EXPLAIN to see how the planner treats the two queries differently, without actually running the computationally heavy (expensive) one.
Run EXPLAIN on both versions and compare the estimated row counts at the top node.
<aside>
💡 EXPLAIN shows the plan and its cost estimate without executing the query. (Note: database "cost" is a unitless planner metric representing estimated CPU and disk effort, not a financial charge). The cartesian version has no ON clause; the filtered version joins on pickup_location_id = location_id. Compare the rows= estimate on the top line of each plan.
</aside>
Raw data is rarely clean. Write three checks that the assignment's audit task expects you to run.
6a. Count trips with a NULL pickup_location_id.
6b. Find duplicate trips: rows that share the same vendor_id, pickup_datetime, and dropoff_datetime.
6c. Find orphaned pickup IDs: pickup_location_id values in nyc_taxi.raw_trips that do not exist in nyc_taxi.raw_zones.
<aside>
💡 For duplicates, GROUP BY the three columns and keep groups with HAVING COUNT(*) > 1. For orphans, a LEFT JOIN to nyc_taxi.raw_zones with WHERE z.location_id IS NULL surfaces the unmatched IDs.
</aside>
Wrap the cleaned-up logic in two views, then query them. This is exactly the star-schema deliverable the assignment asks for, scaled down to practice it once.
7a. Create vw_dim_zones from nyc_taxi.raw_zones and vw_fact_trips from nyc_taxi.raw_trips, excluding rows where fare_amount is negative.
7b. Using your views, find which borough had the highest total fare revenue.
7c. Using your views, find the top 5 pickup zones by trip count.
<aside>
💡 A view is a saved query: CREATE VIEW name AS SELECT .... The borough and zone names live in vw_dim_zones, so join vw_fact_trips.pickup_location_id = vw_dim_zones.location_id for any name-level breakdown.
</aside>
When a dimension table contains duplicate keys, joining it to a fact table duplicates matching fact rows. This is the fan-out trap: a metric like SUM(fare_amount) will count those rows multiple times, inflating your total.
For this exercise, you are given a CTE named duplicate_zones which simulates a zones table that has a duplicate row for location ID 43 (Central Park).
8a. Write a duplicate key check query to find duplicate location_ids in duplicate_zones.
8b. Write a query that deduplicates duplicate_zones in a CTE, joins it to nyc_taxi.raw_trips, and returns the correct SUM(fare_amount). The output must match the original sum from the clean nyc_taxi.raw_zones table (957,367.44).
<aside>
💡 Use GROUP BY and HAVING COUNT(*) > 1 on the join key to detect duplicates. In 8b, clean the table inside a subquery or CTE first by grouping on location_id, then join the deduplicated CTE to raw_trips.
</aside>
These do not need a database. Write down your answers, then check them against the toggles below.
nyc_taxi.raw_trips? In other words, what does a single row represent?nyc_taxi.raw_trips to nyc_taxi.raw_zones and forget the ON condition, how many rows come back, and why?pickup_location_id values before building a fact view that joins to the zone dimension?WITH syntax you practiced in Exercises 3, 4, and 8.Next up: Assignment, where you audit the raw taxi data and deliver a fact view and a dimension view as your Week 9 submission.
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.