How to use this companion
Open the Module 10 notebook and this companion at the same time. Work through the companion from top to bottom and run the matching notebook cells as you go. The notebook is the authoritative source for the lab sequence, code and required work. This companion explains what each step means, what to notice in the output and why the step matters.
Read aloud: controls and directions
Before you start: choose a reading mode, speed, and voice below. Select Read Aloud from Beginning to hear the full companion, or use Read Aloud from This Section beside any major section to continue from that point. While audio is playing, a floating control bar remains visible at the bottom of the page. Use Pause to pause temporarily, Resume to continue, and Stop to end the current reading.
Study mode reads explanations, results, tables, and notes while skipping exact Python code. Full mode also reads Python code and verbalizes common programming symbols.
Ready to read.
The read-aloud feature uses your browser's speech engine. A screen reader can access the page independently of these controls.
What is in the notebook, and what this companion adds
- Notebook: the exact Python code, the Your Turn tasks, the final practice, the offline assignment and the written reflections.
- Companion: plain-language explanations, the results you should expect (taken from a real run of the notebook), sample solutions for the Your Turn tasks (folded, so you try first), common mistakes and how to fix them.
- If anything ever seems inconsistent: follow the current notebook.
This companion is designed for keyboard and screen-reader access, but it is not a separate version for students who need accessibility features. It is the same learning guide for everyone. You can read it visually, use a screen reader, or use the built-in read-aloud controls while you work in the notebook.
Use either course environment
- GitHub Codespaces (main path): open your own repository in Codespaces, open the Module 10 notebook and use the default Python kernel.
- Local VS Code (optional, for experienced students): clone your repository, use the
baseconda environment, installrequirements.txt, open the repository folder in Visual Studio Code and open the notebook. - In either environment: the notebook and this companion are the same. Keep the repository structure unchanged so the notebook paths and output folders work.
Module 10 Learning Goals
In Module 9 you queried one table at a time. In this lab you combine tables in SQL and shape the result into an Analytic Base Table (ABT): a table in which every row matches the unit of analysis of a business question. By the end of the lab you should be able to:
- Explain what makes a good ABT: a clear row meaning, key columns, features, a target if you model, and a documented build.
- Write
INNER JOINandLEFT JOINqueries with table aliases, and check the row count after every join. - Use a CTE (
WITH ... AS) to break a longer query into named, readable steps. - Aggregate detail rows to the ABT unit first, then join lookup information, and check the key for duplicates.
- Spot features that would leak information that is not known at prediction time.
- Know the two ways to run SQL on your own project data for Milestone 2 (the Part 2 notebook).
The main idea: decide what one row means first; then join, aggregate and check until every row means exactly that.
Before You Start: Repository and Setup
Module 10 uses the same repository as Module 9, your copy of ban6003-week-09-10-sql-abt-template. Do not create a new one.
- Open your Week 9-10 repository in GitHub Codespaces (reopening an existing Codespace is fast; a new one takes about 4–5 minutes the first time). Optional for experienced students: local Visual Studio Code with the
baseconda environment. - Open
notebooks/module10_sql_joins_abt_guided_lab.ipynb. This is the graded lab. The second notebook,module10_part2_project_sql_options.ipynb, is not graded (see Section 11). - Run the cells from the top. If you restart the kernel, run from the top again.
Section 0: Connect to Neon
The lab uses the same .env file and the same course database as Module 9. If Module 9 worked in this Codespace, nothing needs to change. In a new Codespace or a fresh clone, .env is empty again: the connection cell creates it and stops, you paste the string from the Canvas page “Module 9 Database Connection” after DATABASE_URL=, save, and run the cell again.
The install line (local only)
# Codespaces: these packages are installed from requirements.txt.
# Local only: if the imports below fail, uncomment and run this line:
# %pip install -q sqlalchemy psycopg2-binary "psycopg[binary]" python-dotenv pandas
Create or read .env and build the engine
import os
import pandas as pd
from dotenv import load_dotenv
from sqlalchemy import create_engine
from pathlib import Path
# Find the top folder of the repository (the folder that contains README.md).
repo_root = Path.cwd()
if not (repo_root / "README.md").exists() and (repo_root.parent / "README.md").exists():
repo_root = repo_root.parent
# Create the .env file the first time. Git ignores this file, so it never goes to GitHub.
env_path = repo_root / ".env"
if not env_path.exists():
env_path.write_text(
'# Paste the connection string from the Canvas page "Module 9 Database Connection"\n'
"# right after DATABASE_URL= (no spaces, no quotes). Then save this file.\n"
"# Git ignores this file. Never commit it.\n"
"DATABASE_URL=\n"
)
print("Created the file .env in the top folder of your repository.")
load_dotenv(env_path, override=True)
database_url = os.getenv("DATABASE_URL")
if not database_url:
print("DATABASE_URL is empty. To fix it:")
print("1. Open the file .env in the top folder of your repository (next to README.md).")
print('2. Paste the connection string from the Canvas page "Module 9 Database Connection" right after DATABASE_URL=')
print("3. Save .env and run this cell again.")
raise RuntimeError("DATABASE_URL is not set. Follow the steps printed above.")
engine = create_engine(database_url)
print("Connection engine created. For safety, we do not print the connection string.")
The run_query() helper
def run_query(query, engine):
"""Run a SQL query when it has been filled in; keep blank practice cells runnable."""
if not query or not query.strip():
print("This practice cell is waiting for your SQL. Add your query inside the triple quotes, then run it again.")
return pd.DataFrame()
return pd.read_sql(query, engine)
Section 1: Quick Database Check
List the course tables
query = '''
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;
'''
run_query(query, engine)Section 2: What Is an ABT?
An ABT is the table you hand to an analysis or a model. Section 2 of the notebook lists what a good ABT has: a clear unit of analysis, one row per entity at that unit, useful features, a target if you model, and documented assumptions about how it was built. The question behind all of them: what does one row mean?
Sections 3–4: INNER JOIN with Table Aliases
A join combines rows from two tables on a key: a column, or set of columns, whose values match in both tables. INNER JOIN keeps only rows whose key appears in both tables; LEFT JOIN keeps every row of the left table.
4.1 Flights with airline names
query = '''
SELECT
f.year,
f.month,
f.day,
f.carrier,
a.name AS airline_name,
f.flight,
f.origin,
f.dest,
f.dep_delay,
f.arr_delay
FROM flights AS f
INNER JOIN airlines AS a
ON f.carrier = a.carrier
LIMIT 10;
'''
run_query(query, engine)Your Turn 1: Flights with Destination Airport Names
Try first
Start from cell 14. Change the joined table, its alias and the key.
- Inner join
flightswithairportsonflights.dest = airports.faa. - Return the date columns,
carrier,flight,origin,dest, the airport name asdest_airport_nameandarr_delay; limit to 10 rows.
Show sample solution for Your Turn 1
Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.
# Your Turn 1
query = '''
SELECT
f.year,
f.month,
f.day,
f.carrier,
f.flight,
f.origin,
f.dest,
ap.name AS dest_airport_name,
f.arr_delay
FROM flights AS f
INNER JOIN airports AS ap
ON f.dest = ap.faa
LIMIT 10;
'''
run_query(query, engine)There is no ORDER BY, so the 10 rows may differ from your neighbor's, and even from run to run. Check the columns and the names instead of the exact rows.
Section 5: LEFT JOIN and the Row-Count Check
5.1 Keep all flights
query = '''
SELECT
f.year,
f.month,
f.day,
f.carrier,
f.flight,
f.origin,
f.dest,
ap.name AS dest_airport_name,
f.dep_delay,
f.arr_delay
FROM flights AS f
LEFT JOIN airports AS ap
ON f.dest = ap.faa
LIMIT 10;
'''
run_query(query, engine)5.2 Count the rows before and after
query = '''
SELECT COUNT(*) AS flight_rows
FROM flights;
'''
run_query(query, engine)query = '''
SELECT COUNT(*) AS joined_rows
FROM flights AS f
LEFT JOIN airports AS ap
ON f.dest = ap.faa;
'''
run_query(query, engine)query = '''
SELECT COUNT(*) AS inner_join_rows
FROM flights AS f
INNER JOIN airports AS ap
ON f.dest = ap.faa;
'''
run_query(query, engine)
5.3 Which destinations have no match?
query = '''
SELECT
f.dest,
COUNT(*) AS flight_count
FROM flights AS f
LEFT JOIN airports AS ap
ON f.dest = ap.faa
WHERE ap.faa IS NULL
GROUP BY f.dest
ORDER BY flight_count DESC;
'''
run_query(query, engine)
Your Turn 2: Flights with Aircraft Details
Try first
Copy the left join pattern of cell 19.
- Left join
flightswithplanesontailnum. Return the date columns,carrier,flight,tailnum,manufacturer,modelandseats; limit to 10 rows.
Show sample solution for Your Turn 2
Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.
# Your Turn 2
query = '''
SELECT
f.year,
f.month,
f.day,
f.carrier,
f.flight,
f.tailnum,
p.manufacturer,
p.model,
p.seats
FROM flights AS f
LEFT JOIN planes AS p
ON f.tailnum = p.tailnum
LIMIT 10;
'''
run_query(query, engine)Again no ORDER BY, so your 10 rows may differ. Check the columns and the join, not the exact rows.
Section 6: CTEs with WITH
A CTE (Common Table Expression) gives a name to one step inside a query. The notebook calls it a “named temporary result”; it is not a table saved in the database, and it exists only while that one query runs.
6.1 Select columns first, then join
query = '''
WITH flight_base AS (
SELECT
year,
month,
day,
carrier,
flight,
origin,
dest,
dep_delay,
arr_delay,
distance,
air_time
FROM flights
)
SELECT
fb.*,
a.name AS airline_name
FROM flight_base AS fb
LEFT JOIN airlines AS a
ON fb.carrier = a.carrier
LIMIT 10;
'''
run_query(query, engine)Your Turn 3: A CTE for JFK Flights
Try first
Start from cell 32. Add a filter inside the CTE and rename it.
- Create a CTE called
jfk_flightsthat keeps flights withorigin = 'JFK', join it toairlinesfor the airline name, and return 10 rows.
Show sample solution for Your Turn 3
Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.
# Your Turn 3
query = '''
WITH jfk_flights AS (
SELECT
year,
month,
day,
carrier,
flight,
origin,
dest,
dep_delay,
arr_delay
FROM flights
WHERE origin = 'JFK'
)
SELECT
jf.*,
a.name AS airline_name
FROM jfk_flights AS jf
LEFT JOIN airlines AS a
ON jf.carrier = a.carrier
LIMIT 10;
'''
run_query(query, engine)No ORDER BY, so the rows may differ. Every row should show origin = JFK and an airline name.
Section 7: Aggregate Before Joining
7.1 One row per carrier-month
query = '''
SELECT
carrier,
month,
COUNT(*) AS flight_count,
ROUND(AVG(dep_delay)::numeric, 2)::float AS avg_dep_delay,
ROUND(AVG(arr_delay)::numeric, 2)::float AS avg_arr_delay,
ROUND(AVG(distance)::numeric, 2)::float AS avg_distance
FROM flights
GROUP BY carrier, month
ORDER BY carrier, month;
'''
carrier_month_summary = run_query(query, engine)
carrier_month_summary.head()7.2 Then add the airline name
query = '''
WITH carrier_month AS (
SELECT
carrier,
month,
COUNT(*) AS flight_count,
ROUND(AVG(dep_delay)::numeric, 2)::float AS avg_dep_delay,
ROUND(AVG(arr_delay)::numeric, 2)::float AS avg_arr_delay,
ROUND(AVG(distance)::numeric, 2)::float AS avg_distance
FROM flights
GROUP BY carrier, month
)
SELECT
cm.*,
a.name AS airline_name
FROM carrier_month AS cm
LEFT JOIN airlines AS a
ON cm.carrier = a.carrier
ORDER BY cm.carrier, cm.month;
'''
carrier_month_abt = run_query(query, engine)
carrier_month_abt.head()7.3 Light ABT check
carrier_month_abt[["carrier", "month"]].duplicated().sum()carrier_month_abt.isna().sum()Your Turn 4: A Route Summary
Try first
Copy the structure of cell 40: aggregate in the CTE, then join.
- One row per
originanddestwith the number of flights and the rounded average departure delay, arrival delay and distance. - Left join
airportsondest = faafordest_airport_name. Then fill in the check cell below it.
Show sample solution for Your Turn 4
Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.
# Your Turn 4
query = '''
WITH route_summary AS (
SELECT
origin,
dest,
COUNT(*) AS flight_count,
ROUND(AVG(dep_delay)::numeric, 2)::float AS avg_dep_delay,
ROUND(AVG(arr_delay)::numeric, 2)::float AS avg_arr_delay,
ROUND(AVG(distance)::numeric, 2)::float AS avg_distance
FROM flights
GROUP BY origin, dest
)
SELECT
rs.*,
ap.name AS dest_airport_name
FROM route_summary AS rs
LEFT JOIN airports AS ap
ON rs.dest = ap.faa
ORDER BY rs.origin, rs.dest;
'''
route_abt = run_query(query, engine)
route_abt.head()What it prints:
origin dest flight_count avg_dep_delay avg_arr_delay avg_distance \
0 EWR ALB 439 23.62 14.40 143.0
1 EWR ANC 8 12.88 -2.50 3370.0
2 EWR ATL 5022 15.50 13.23 746.0
3 EWR AUS 968 11.51 -0.47 1504.0
4 EWR AVL 265 8.62 8.80 583.0
dest_airport_name
0 Albany Intl
1 Ted Stevens Anchorage Intl
2 Hartsfield Jackson Atlanta Intl
3 Austin Bergstrom Intl
4 Asheville Regional Airport Show sample solution for the Your Turn 4 check
Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.
# Your Turn 4 check
# Check whether each origin-dest pair appears once.
print("Rows:", len(route_abt))
print("Duplicate origin-dest pairs:", route_abt[["origin", "dest"]].duplicated().sum())
print("Routes without a destination name:", route_abt["dest_airport_name"].isna().sum())What it prints:
Rows: 224
Duplicate origin-dest pairs: 0
Routes without a destination name: 7Section 8: A Preliminary Flight-Level ABT
8.1 One row per flight, with three lookups
query = '''
WITH flight_base AS (
SELECT
year,
month,
day,
carrier,
flight,
origin,
dest,
dep_delay,
arr_delay,
distance,
air_time
FROM flights
)
SELECT
fb.*,
a.name AS airline_name,
dest_ap.name AS dest_airport_name,
origin_ap.name AS origin_airport_name
FROM flight_base AS fb
LEFT JOIN airlines AS a
ON fb.carrier = a.carrier
LEFT JOIN airports AS dest_ap
ON fb.dest = dest_ap.faa
LEFT JOIN airports AS origin_ap
ON fb.origin = origin_ap.faa
LIMIT 1000;
'''
flight_level_abt = run_query(query, engine)
flight_level_abt.head()flight_level_abt.shapeflight_level_abt.isna().sum()Section 9: Data Leakage
Leakage means an ABT includes information that would not be known when the prediction is made. A leaky model looks very accurate in testing and fails in real use.
Quick reflection (graded written answer)
This is one of the three graded written answers, so the companion does not answer it. Write which columns should not be predictors if the target is arr_delay, and why. The note above gives you the test to apply; say for which prediction time your answer holds.
Section 10: Optional Demo: ROW_NUMBER()
Optional and not graded, but useful if your project uses SQL heavily. ROW_NUMBER() numbers the rows inside each group, a ranking within a group.
10.1 Each carrier's most delayed departure
query = '''
WITH ranked_flights AS (
SELECT
carrier,
year,
month,
day,
flight,
origin,
dest,
dep_delay,
ROW_NUMBER() OVER (
PARTITION BY carrier
ORDER BY dep_delay DESC NULLS LAST
) AS rn
FROM flights
)
SELECT *
FROM ranked_flights
WHERE rn = 1
ORDER BY dep_delay DESC;
'''
run_query(query, engine)
Try first
Optional extension from the lab session: rank months instead of flights. You need two steps, so use two CTEs.
- For each carrier, find the month with the highest average departure delay. First aggregate to carrier-month, then number the months inside each carrier.
Show sample solution for the worst-month extension
Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.
query = '''
WITH carrier_month AS (
SELECT
carrier,
month,
COUNT(*) AS flight_count,
ROUND(AVG(dep_delay)::numeric, 2)::float AS avg_dep_delay
FROM flights
GROUP BY carrier, month
),
ranked_months AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY carrier
ORDER BY avg_dep_delay DESC NULLS LAST
) AS rn
FROM carrier_month
)
SELECT carrier, month, flight_count, avg_dep_delay
FROM ranked_months
WHERE rn = 1
ORDER BY avg_dep_delay DESC;
'''
run_query(query, engine)What it prints:
carrier month flight_count avg_dep_delay
0 OO 1 1 67.00
1 HA 1 31 54.39
2 YV 6 49 42.79
3 FL 7 263 41.16
4 F9 5 58 35.95
5 VX 7 489 35.26
6 9E 7 1494 31.40
7 WN 6 1028 30.51
8 EV 12 4307 27.89
9 B6 7 4984 24.90
10 MQ 6 2178 20.84
11 DL 7 4251 20.58
12 UA 6 4975 20.27
13 AS 12 54 18.02
14 AA 6 2757 14.63
15 US 6 1736 10.4016 rows, one per carrier. Look at the top row: OO's “worst month” is January with a single flight. As in Module 9, add HAVING COUNT(*) >= 100 inside the first CTE if small months should not count.
Section 11: SQL on Your Own Project Data (Part 2, Not Graded)
Milestone 2 requires at least one SQL query on your own project data, inside your project notebook. The notebook module10_part2_project_sql_options.ipynb shows both ways to do it. It is not graded and not part of the Module 10 submission; you use it for the project. Pick one path:
- Path A, your own Neon database (recommended): closest to real work. You create a free database, upload your project table(s) and query them with the same SQL as in this lab.
- Path B, DuckDB (a valid fallback, no points lost): runs SQL directly on Pandas DataFrames or CSV files, with no account. After
import duckdb,duckdb.sql(query)can use a DataFrame's variable name as the table name.
Section 12: Final Practice: Build a Preliminary ABT
Complete independently
The final practice is graded, so the companion does not solve it.
Before coding: choose the unit (carrier-month, route, or flight with a filter) and write down, in words, what one row means and which columns form its key. Then follow the pattern of Section 7: build the rows in a CTE, join one lookup for a descriptive field, and select at least three features.
What to check: fill in the three check cells (shape, missing values, duplicate keys on your key columns). The duplicate count must be 0. Explain every missing value you find. For a flight-level ABT, the WHERE filter (one origin airport and one month) is required: the export cell refuses more than 50,000 rows.
output_dir = repo_root / "data" / "processed"
output_dir.mkdir(parents=True, exist_ok=True)
if my_abt.empty:
print("Complete the Final Practice query before exporting.")
elif len(my_abt) > 50000:
print(f"Your ABT has {len(my_abt):,} rows, so it was not saved.")
print("For a flight-level ABT, add a WHERE filter (for example, one origin airport and one month) and run the query again.")
else:
final_output_path = output_dir / "preliminary_abt_module10.csv"
my_abt.to_csv(final_output_path, index=False)
print(f"Saved {len(my_abt)} rows to: {final_output_path}")Final reflection
Write 5–7 sentences: the unit of analysis, the keys, the tables you joined, whether and why you aggregated before joining, the checks you ran, and what you would still document before using the ABT.
Section 13: Offline Assignment: Route-Month Operations ABT
Complete independently
This assignment is intentionally not solved in the companion. Follow the six steps and the suggested structure in the notebook cell.
Before coding: one row is one route in one month, so the key has three columns. Decide which steps belong inside the CTE (grouping, the 100-flight rule) and which come after it (the two airport joins, the sort). airports plays two roles here, as in Section 8, so it needs two aliases.
What to check: the validation cell prints the shape, the duplicate route-month keys (must be 0) and the missing values. If some airport names are missing, check which airports they are before you call them an error (Section 5). The cell saves data/processed/route_month_abt_module10.csv.
if route_month_abt.empty:
print("Complete the offline ABT query before validating and exporting.")
else:
print("Shape:", route_month_abt.shape)
print(
"Duplicate route-month keys:",
route_month_abt[["origin", "dest", "month"]].duplicated().sum()
)
print("Missing values by column:")
display(route_month_abt.isna().sum())
offline_output_path = output_dir / "route_month_abt_module10.csv"
route_month_abt.to_csv(offline_output_path, index=False)
print(f"Saved {len(route_month_abt)} rows to: {offline_output_path}")
ABT recommendation
Write 5–6 sentences: the unit and key, why aggregation comes before the airport joins, what the 100-flight threshold does, what your validation numbers show, and one limitation or leakage concern to document before modeling.
Section 14: Optional Challenge (Not Graded)
For students who already know SQL. Optional and not graded: finish the final practice and the offline assignment first. Each challenge needs one SQL idea beyond the module; the hints in the notebook name it.
Try first
Use the hints in the notebook. An empty challenge cell still runs without an error.
- Challenge 1: weather with a two-column key, and delays by visibility group (
CASE WHEN). - Challenge 2: which carriers lose the most flights without an aircraft record (
COUNT(*) FILTER (WHERE ...)). - Challenge 3: a carrier-month ABT with next month's delay as the target (
LEAD).
Show sample solution for Challenge 1
Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.
# Challenge 1 (optional)
query = '''
SELECT
COUNT(*) AS weather_rows,
COUNT(DISTINCT (origin, time_hour)) AS distinct_origin_hours
FROM weather;
'''
display(run_query(query, engine))
# Step 2-4: join on both key columns and group by visibility
query = '''
WITH flight_weather AS (
SELECT
f.dep_delay,
CASE
WHEN w.visib IS NULL THEN 'no weather record'
WHEN w.visib < 1 THEN 'under 1 mile'
WHEN w.visib < 5 THEN '1 to 5 miles'
ELSE '5 miles or more'
END AS visibility_group
FROM flights AS f
LEFT JOIN weather AS w
ON f.origin = w.origin
AND f.time_hour = w.time_hour
)
SELECT
visibility_group,
COUNT(*) AS flight_count,
ROUND(AVG(dep_delay)::numeric, 2)::float AS avg_dep_delay
FROM flight_weather
GROUP BY visibility_group
ORDER BY avg_dep_delay DESC;
'''
run_query(query, engine)What it prints:
weather_rows distinct_origin_hours
0 26115 26115
visibility_group flight_count avg_dep_delay
0 under 1 mile 3975 29.72
1 1 to 5 miles 15040 23.47
2 no weather record 1556 13.43
3 5 miles or more 316205 11.95The key is unique (26,115 = 26,115), so the join cannot add rows; the four groups add up to 336,776. Low visibility goes with longer delays, an association and not proof of a cause.
Show sample solution for Challenge 2
Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.
# Challenge 2 (optional)
query = '''
SELECT
f.carrier,
COUNT(*) AS flights_without_plane_record,
COUNT(*) FILTER (WHERE f.tailnum IS NULL) AS missing_tailnum
FROM flights AS f
LEFT JOIN planes AS p
ON f.tailnum = p.tailnum
WHERE p.tailnum IS NULL
GROUP BY f.carrier
ORDER BY flights_without_plane_record DESC;
'''
run_query(query, engine)What it prints:
carrier flights_without_plane_record missing_tailnum
0 MQ 25397 2
1 AA 22558 84
2 UA 1693 686
3 9E 1044 1044
4 B6 830 0
5 US 699 663
6 FL 187 0
7 DL 110 0
8 F9 50 3
9 WN 38 30The column adds up to 52,606. MQ and AA lose the most flights, and almost all of them do have a tailnum: the planes table simply lacks most of their aircraft. An inner join with planes would drop about 48,000 of their flights and make a carrier comparison unfair.
Show sample solution for Challenge 3
Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.
# Challenge 3 (optional)
query = '''
WITH carrier_month AS (
SELECT
carrier,
month,
COUNT(*) AS flight_count,
ROUND(AVG(dep_delay)::numeric, 2)::float AS avg_dep_delay,
ROUND(AVG(arr_delay)::numeric, 2)::float AS avg_arr_delay
FROM flights
GROUP BY carrier, month
)
SELECT
carrier,
month,
flight_count,
avg_dep_delay,
avg_arr_delay,
LEAD(avg_arr_delay) OVER (PARTITION BY carrier ORDER BY month) AS next_month_avg_arr_delay
FROM carrier_month
ORDER BY carrier, month;
'''
next_month_abt = run_query(query, engine)
next_month_abt.head(12)What it prints:
carrier month flight_count avg_dep_delay avg_arr_delay \
0 9E 1 1573 16.88 10.21
1 9E 2 1459 16.49 8.28
2 9E 3 1627 13.41 2.03
3 9E 4 1511 13.57 5.47
4 9E 5 1462 22.67 10.42
5 9E 6 1437 28.95 22.51
6 9E 7 1494 31.40 23.76
7 9E 8 1456 17.30 5.31
8 9E 9 1540 7.75 -7.14
9 9E 10 1673 9.33 -1.35
10 9E 11 1595 7.56 -0.03
11 9E 12 1633 19.78 14.05
next_month_avg_arr_delay
0 8.28
1 2.03
2 5.47
3 10.42
4 22.51
5 23.76
6 5.31
7 -7.14
8 -1.35
9 -0.03
10 14.05
11 NaN 185 rows; 16 have no target (each carrier's last month). For OO the next row is not always the next calendar month (January is followed by June): check with LEAD(month) ... - month and set the target to NULL when the gap is not 1. This month's average is known at the end of the month, before next month starts, so it is a fair feature for next month's target.
Common Problems
If a cell fails, read the last line of the error message first. Then find it below.
Still stuck? Post the error message and the cell number in the course Q&A discussion. Never post a connection string, and do not post your full answers.
Save and Submit
- Complete Your Turn 1–4, the final practice and the offline assignment. The optional demo and challenges are not required, and the Part 2 notebook is not part of this submission.
- Complete all three written answers: the data leakage quick reflection (Section 9), the Final Reflection (Section 12) and the ABT Recommendation (Section 13).
- Restart the kernel and run all cells from top to bottom. Confirm there are no errors.
- Confirm both files exist in
data/processed:preliminary_abt_module10.csvandroute_month_abt_module10.csv. - Confirm that
.envis not tracked by Git. - Save, then commit and push:
git add .,git commit -m "Complete Module 10 SQL joins and ABT lab",git push. Check on GitHub that the commit and both CSV files are there, and submit the repository link in Canvas.
Lab Wrap-Up
You built ABTs in SQL with one repeatable pattern:
- Decide the row: what one row means, and which columns form its key.
- Join on purpose: a left join to keep the main table, an inner join only when dropping unmatched rows is intended, and the row count checked after every join.
- Build in steps: CTEs for readable queries; aggregate to the ABT unit first, then add lookups.
- Check and document: duplicate keys, new NULLs, and features that would not be known at prediction time.
Next, Milestone 2 asks for at least one SQL query on your own project data (Part 2 notebook). Module 11 then turns your ABT into a documented hand-off: final checks, a data dictionary and a validation checklist.