How to use this companion
Open the Module 9 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 9 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 9 Learning Goals
Until now your data lived in CSV files and Pandas DataFrames. In this lab the data lives in a database: the course Neon database, an online Postgres database with the nycflights13 tables. You connect to it from your notebook and ask it questions in SQL. By the end of the lab you should be able to:
- Keep a database connection string private in a
.envfile and connect to Neon from Python. - List the tables in a database and look at their columns before you write a query.
- Write single-table queries with
SELECT,FROM,WHERE,ORDER BYandLIMIT. - Summarize with
COUNT,AVG,MIN,MAXandSUM, group withGROUP BY, and filter groups withHAVING. - Run simple validation checks in SQL: row counts, missing values, distinct categories and possible outliers.
- Combine SQL and Pandas in one workflow: summarize in the database, then continue in Python.
The main idea: when the data lives in a database, let the database do the heavy work, and bring back only the small result you need.
Before You Start: Repository and Setup
Modules 9 and 10 use a new repository. Create your own copy of ban6003-week-09-10-sql-abt-template with Use this template > Create a new repository, as in earlier modules.
- Open your new repository in GitHub Codespaces. The first start of a new Codespace takes about 4–5 minutes; reopening it later is much faster. Optional for experienced students: clone it and work in local Visual Studio Code with the
baseconda environment. - Open
notebooks/module9_neon_sql_fundamentals_guided_lab.ipynband choose the Python kernel (in local VS Code:base). - Keep the Canvas page “Module 9 Database Connection” open. It has the connection string you need in Section 1.
- Run the cells from the top. If you restart the kernel, run from the top again.
Section 0: Packages
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 pandas sqlalchemy python-dotenv psycopg2-binary "psycopg[binary]"
Imports
import os
import pandas as pd
from sqlalchemy import create_engine, text
from dotenv import load_dotenvSection 1: The connection string goes into .env
The connection string contains a username and a password. It must never appear in your notebook, because the notebook goes to GitHub. So you save it in a separate file, .env, and Python reads it from there.
Create .env and load the connection string
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.")
print("DATABASE_URL loaded. For safety, we do not print the connection string.")
Section 2: Create the engine and test the connection
Create the engine
engine = create_engine(database_url)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)
Test the connection
with engine.connect() as conn:
result = conn.execute(text("SELECT 1 AS connection_test;"))
print(result.fetchall())Section 3: Explore the Available Tables
Before you query, find out what is there. information_schema.tables is a built-in view that lists every table the database knows about.
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 4: SELECT, FROM and LIMIT
SELECT names the columns, FROM names the table, and LIMIT keeps the output small. Every query needs SELECT and FROM; LIMIT is optional.
4.1 All columns, five rows
query = """
SELECT *
FROM flights
LIMIT 5;
"""
run_query(query, engine)4.2 Selected columns
query = """
SELECT year, month, day, carrier, flight, origin, dest, dep_delay, arr_delay
FROM flights
LIMIT 10;
"""
run_query(query, engine)Your Turn 1: The airlines Table
Try first
Copy the pattern of cell 18 and change the table and the limit.
- Return the first 10 rows from the
airlinestable.
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 *
FROM airlines
LIMIT 10;
"""
run_query(query, engine)What it prints:
carrier name
0 9E Endeavor Air Inc.
1 AA American Airlines Inc.
2 AS Alaska Airlines Inc.
3 B6 JetBlue Airways
4 DL Delta Air Lines Inc.
5 EV ExpressJet Airlines Inc.
6 F9 Frontier Airlines Inc.
7 FL AirTran Airways Corporation
8 HA Hawaiian Airlines Inc.
9 MQ Envoy AirSection 5: Filtering Rows with WHERE
WHERE keeps only the rows that meet a condition. Text values need single quotes: dest = 'IAH'.
5.1 One condition
query = """
SELECT year, month, day, carrier, flight, origin, dest, arr_delay
FROM flights
WHERE arr_delay >= 120
LIMIT 10;
"""
run_query(query, engine)5.2 Two conditions with AND
query = """
SELECT year, month, day, carrier, flight, origin, dest, dep_delay, arr_delay
FROM flights
WHERE arr_delay >= 120
AND dep_delay <= 0
LIMIT 10;
"""
run_query(query, engine)5.3 A list of values with IN
query = """
SELECT year, month, day, carrier, flight, origin, dest
FROM flights
WHERE dest IN ('IAH', 'HOU')
LIMIT 10;
"""
run_query(query, engine)Your Turn 2: Late Departures from JFK
Try first
Combine two conditions with AND.
- Return flights that departed from
JFKwith a departure delay greater than 60 minutes. - Return
year,month,day,carrier,flight,origin,destanddep_delay, and 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 year, month, day, carrier, flight, origin, dest, dep_delay
FROM flights
WHERE origin = 'JFK'
AND dep_delay > 60
LIMIT 10;
"""
run_query(query, engine)The query has no ORDER BY, so it returns some 10 qualifying flights, and the 10 may differ from run to run. Check the conditions instead of the exact rows.
Section 6: Sorting with ORDER BY
6.1 Largest departure delays first
query = """
SELECT year, month, day, carrier, flight, origin, dest, dep_delay
FROM flights
WHERE dep_delay IS NOT NULL
ORDER BY dep_delay DESC
LIMIT 10;
"""
run_query(query, engine)6.2 Two sort columns
query = """
SELECT year, month, day, carrier, flight, origin, dest, dep_delay, arr_delay
FROM flights
WHERE dep_delay IS NOT NULL
AND arr_delay IS NOT NULL
ORDER BY dep_delay DESC, arr_delay DESC
LIMIT 10;
"""
run_query(query, engine)Your Turn 3: The Longest Flights
Try first
Sort by the column the question is about.
- Find the 10 longest flights by distance. Return
carrier,flight,origin,destanddistance.
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 = """
SELECT carrier, flight, origin, dest, distance
FROM flights
ORDER BY distance DESC
LIMIT 10;
"""
run_query(query, engine)What it prints:
carrier flight origin dest distance
0 HA 51 JFK HNL 4983
1 HA 51 JFK HNL 4983
2 HA 51 JFK HNL 4983
3 HA 51 JFK HNL 4983
4 HA 51 JFK HNL 4983
5 HA 51 JFK HNL 4983
6 HA 51 JFK HNL 4983
7 HA 51 JFK HNL 4983
8 HA 51 JFK HNL 4983
9 HA 51 JFK HNL 4983Section 7: Counting Rows with COUNT
7.1 Count all rows
query = """
SELECT COUNT(*) AS row_count
FROM flights;
"""
run_query(query, engine)7.2 Count non-missing values and compute the missing ones
query = """
SELECT
COUNT(*) AS total_rows,
COUNT(dep_delay) AS non_missing_dep_delay,
COUNT(arr_delay) AS non_missing_arr_delay,
COUNT(*) - COUNT(dep_delay) AS missing_dep_delay,
COUNT(*) - COUNT(arr_delay) AS missing_arr_delay
FROM flights;
"""
run_query(query, engine)Your Turn 4: Count the Planes
Try first
Copy cell 34 and change the table and the column.
- Count the total number of rows in the
planestable and the planes with a non-missingyear. Name the columnstotal_planesandplanes_with_year.
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 = """
SELECT
COUNT(*) AS total_planes,
COUNT(year) AS planes_with_year
FROM planes;
"""
run_query(query, engine)What it prints:
total_planes planes_with_year
0 3322 3252Section 8: Summary Statistics with Aggregate Functions
8.1 Average, minimum and maximum
query = """
SELECT
AVG(dep_delay) AS avg_dep_delay,
MIN(dep_delay) AS min_dep_delay,
MAX(dep_delay) AS max_dep_delay
FROM flights;
"""
run_query(query, engine)8.2 Rounded averages
query = """
SELECT
ROUND(AVG(dep_delay)::numeric, 2)::float AS avg_dep_delay,
ROUND(AVG(arr_delay)::numeric, 2)::float AS avg_arr_delay,
SUM(distance) AS total_distance
FROM flights;
"""
run_query(query, engine)Your Turn 5: Average Air Time and Distance
Try first
Start from cell 40 and keep the rounding pattern.
- Calculate the average air time and the average distance. Name the columns
avg_air_timeandavg_distance.
Show sample solution for Your Turn 5
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 5
query = """
SELECT
ROUND(AVG(air_time)::numeric, 2)::float AS avg_air_time,
ROUND(AVG(distance)::numeric, 2)::float AS avg_distance
FROM flights;
"""
run_query(query, engine)What it prints:
avg_air_time avg_distance
0 150.69 1039.91Section 9: Grouped Summaries with GROUP BY
9.1 One row per carrier
query = """
SELECT
carrier,
COUNT(*) AS flight_count,
ROUND(AVG(dep_delay)::numeric, 2)::float AS avg_dep_delay
FROM flights
GROUP BY carrier
ORDER BY avg_dep_delay DESC;
"""
run_query(query, engine)9.2 One row per carrier and month
query = """
SELECT
carrier,
month,
COUNT(*) AS flight_count,
ROUND(AVG(arr_delay)::numeric, 2)::float AS avg_arr_delay
FROM flights
GROUP BY carrier, month
ORDER BY avg_arr_delay DESC
LIMIT 15;
"""
run_query(query, engine)Your Turn 6: Summary by Origin Airport
Try first
Group by origin instead of carrier.
- For each origin airport, calculate the number of flights, the average departure delay and the average arrival delay.
- Sort by average departure delay from highest to lowest.
Show sample solution for Your Turn 6
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 6
query = """
SELECT
origin,
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 origin
ORDER BY avg_dep_delay DESC;
"""
run_query(query, engine)What it prints:
origin flight_count avg_dep_delay avg_arr_delay
0 EWR 120835 15.11 9.11
1 JFK 111279 12.11 5.55
2 LGA 104662 10.35 5.78Section 10: Filtering Groups with HAVING
WHERE filters rows before the groups exist. HAVING filters groups after GROUP BY made them, so it can use aggregates such as COUNT(*).
10.1 Carriers with at least 10,000 flights
query = """
SELECT
carrier,
COUNT(*) AS flight_count,
ROUND(AVG(arr_delay)::numeric, 2)::float AS avg_arr_delay
FROM flights
GROUP BY carrier
HAVING COUNT(*) >= 10000
ORDER BY avg_arr_delay DESC;
"""
run_query(query, engine)Your Turn 7: Carriers with Long Departure Delays
Try first
The condition uses AVG(), so it belongs in HAVING.
- Find the carriers whose average departure delay is more than 15 minutes. Return
carrier,flight_countandavg_dep_delay, from highest to lowest average.
Show sample solution for Your Turn 7
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 7
query = """
SELECT
carrier,
COUNT(*) AS flight_count,
ROUND(AVG(dep_delay)::numeric, 2)::float AS avg_dep_delay
FROM flights
GROUP BY carrier
HAVING AVG(dep_delay) > 15
ORDER BY avg_dep_delay DESC;
"""
run_query(query, engine)What it prints:
carrier flight_count avg_dep_delay
0 F9 685 20.22
1 EV 54173 19.96
2 YV 601 19.00
3 FL 3260 18.73
4 WN 12275 17.71
5 9E 18460 16.73Section 11: Basic Validation with SQL
The same checks you ran in Pandas work in SQL: row counts, missing values, distinct categories and possible outliers.
11.1 Row count and missing values
query = """
SELECT
COUNT(*) AS total_rows,
COUNT(*) - COUNT(dep_time) AS missing_dep_time,
COUNT(*) - COUNT(arr_delay) AS missing_arr_delay
FROM flights;
"""
run_query(query, engine)11.2 Distinct categories
query = """
SELECT COUNT(DISTINCT carrier) AS unique_carriers
FROM flights;
"""
run_query(query, engine)11.3 Possible outliers
query = """
SELECT year, month, day, carrier, flight, origin, dest, arr_delay
FROM flights
WHERE arr_delay > 600
ORDER BY arr_delay DESC;
"""
run_query(query, engine)Your Turn 8: Missing Tail Numbers
Try first
Filter the missing rows with WHERE, then count them.
- Count how many flights have a missing
tailnum.
Show sample solution for Your Turn 8
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 8
query = """
SELECT COUNT(*) AS missing_tailnum
FROM flights
WHERE tailnum IS NULL;
"""
run_query(query, engine)What it prints:
missing_tailnum
0 2512Section 12: SQL and Pandas, Compare and Combine
Business question: which carrier has the highest average departure delay? First answer it twice, once in each tool.
12.1 The SQL version
# SQL version
sql_query = """
SELECT
carrier,
ROUND(AVG(dep_delay)::numeric, 2)::float AS avg_dep_delay
FROM flights
GROUP BY carrier
ORDER BY avg_dep_delay DESC;
"""
sql_result = pd.read_sql_query(sql_query, engine)
sql_result.head()12.2 The Pandas version
# Pandas version
flights_df = pd.read_sql_query("SELECT carrier, dep_delay FROM flights;", engine)
pandas_result = (
flights_df
.groupby("carrier")
.agg(avg_dep_delay=("dep_delay", "mean"))
.reset_index()
.sort_values("avg_dep_delay", ascending=False)
)
pandas_result.head()Using SQL and Pandas together
12.3 Step 1 and 2: summarize in SQL, then add a column in Pandas
# Step 1 - SQL: summarize in the database (336,776 flight rows in, one row per carrier out)
carrier_summary = run_query("""
SELECT
carrier,
COUNT(*) AS flight_count,
ROUND(AVG(arr_delay)::numeric, 2)::float AS avg_arr_delay
FROM flights
GROUP BY carrier;
""", engine)
# Step 2 - Pandas: add a column, keep the large carriers, and sort
carrier_summary["pct_of_all_flights"] = (
100 * carrier_summary["flight_count"] / carrier_summary["flight_count"].sum()
).round(1)
large_carriers = (
carrier_summary[carrier_summary["pct_of_all_flights"] >= 5]
.sort_values("avg_arr_delay", ascending=False)
)
large_carriers
12.4 Step 3: use the Pandas value in the next SQL query
# Step 3 - Pandas to SQL: use the carrier code from the Pandas result in the next query
worst_carrier = large_carriers.iloc[0]["carrier"]
print("Large carrier with the worst average arrival delay:", worst_carrier)
monthly_query = text("""
SELECT
month,
COUNT(*) AS flight_count,
ROUND(AVG(arr_delay)::numeric, 2)::float AS avg_arr_delay
FROM flights
WHERE carrier = :carrier
GROUP BY month
ORDER BY month;
""")
worst_carrier_monthly = pd.read_sql(monthly_query, engine, params={"carrier": worst_carrier})
worst_carrier_monthly
12.5 Step 4: plot in Pandas
# Step 4 - Pandas: plot the SQL result
ax = worst_carrier_monthly.plot(
x="month",
y="avg_arr_delay",
kind="bar",
legend=False,
title=f"Average arrival delay by month: {worst_carrier}",
)
ax.set_xlabel("Month")
ax.set_ylabel("Average arrival delay (minutes)");
Your Turn 9: SQL or Pandas?
Write in your own words
This is a written answer, so the companion does not answer it for you. Choose one query from earlier in the notebook and say in 2–3 sentences whether you would rather do it in SQL or in Pandas, and why.
A strong answer names the query and gives a reason about where the data lives, how much data has to move, or which step is easier in which tool. “SQL is faster” alone is not a reason.
Section 13: Project Data at a High Level
So far you only read from the shared course database. For your project, the SQL requirement uses your own data. Section 13 of the notebook describes the workflow; there is no code to run.
Section 14: Final Practice
Complete independently
The final practice is graded, so the companion does not solve it. Follow the five steps in the notebook.
Business question: which destination airport had the highest average arrival delay among destinations with at least 500 flights?
Before coding: say the query in order: group by destination → count the flights → average the arrival delay with the rounding pattern → keep groups with at least 500 flights → sort from highest to lowest. Section 10 has every piece you need.
What to check: one row per destination; every flight_count is at least 500; the averages have two decimals; the first row has the largest avg_arr_delay. If you get an error about flight_count, see the note in Section 10.
output_dir = repo_root / "data" / "processed"
output_dir.mkdir(parents=True, exist_ok=True)
if destination_delay_summary.empty:
print("Complete the Final Practice query before exporting.")
else:
final_output_path = output_dir / "destination_delay_summary_module9.csv"
destination_delay_summary.to_csv(final_output_path, index=False)
print(f"Saved {len(destination_delay_summary)} rows to: {final_output_path}")
Final reflection
Write 4–6 sentences in the notebook: why SQL is useful when data lives in a database, which statement felt most useful, one way SQL is like Pandas, one way it is different, and how SQL could help your project.
Section 15: Offline Assignment: JFK Destination Reliability
Complete independently
This assignment is intentionally not solved in the companion. Use the exact instructions in the notebook.
- Keep only flights that departed from JFK and have a non-missing
arr_delay. - One row per destination with
flight_count,avg_dep_delay,avg_arr_delayandavg_distance, each average rounded. - Keep destinations with at least 300 of these flights.
- Sort from highest to lowest average arrival delay.
- Read the result into
jfk_destination_reliabilityand savedata/processed/jfk_destination_reliability_module9.csv.
Before coding: decide which conditions are about rows (JFK, a recorded arrival delay) and which one is about groups (at least 300 flights). Rows go in WHERE, groups in HAVING. The comments in the cell give the clause order.
What to check: every flight_count is at least 300; there is one row per destination; the cell prints “Saved … rows” and shows the first 10 rows.
Operations interpretation
Write 4–5 sentences: what one row represents, how the JFK filter and the 300-flight threshold shape the scope, which destination deserves attention (with its number from your result), and why this summary describes performance but does not explain the cause of delays.
Section 16: Optional Challenge (Not Graded)
These three challenges are for students who already know SQL. They are optional and not graded: finish the final practice and the offline assignment first. Each one needs one new SQL idea, which the hint in the notebook gives you.
Try first
Use the hints in the notebook. An empty challenge cell still runs without an error.
- Challenge 1: a delay profile per origin airport, with
SUM(CASE WHEN ... THEN 1 ELSE 0 END). - Challenge 2: each carrier's worst month, with
RANK()in an inner query. - Challenge 3: the 10 worst days and their 7-day moving average, with a window frame.
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
origin,
COUNT(*) AS flight_count,
SUM(CASE WHEN arr_delay <= 0 THEN 1 ELSE 0 END) AS on_time,
SUM(CASE WHEN arr_delay > 0 AND arr_delay <= 15 THEN 1 ELSE 0 END) AS small_delay,
SUM(CASE WHEN arr_delay > 15 THEN 1 ELSE 0 END) AS late_15_plus,
SUM(CASE WHEN arr_delay IS NULL THEN 1 ELSE 0 END) AS no_arrival,
ROUND(100.0 * SUM(CASE WHEN arr_delay > 15 THEN 1 ELSE 0 END) / COUNT(*), 1)::float AS pct_late_15_plus
FROM flights
GROUP BY origin
ORDER BY pct_late_15_plus DESC;
"""
run_query(query, engine)What it prints:
origin flight_count on_time small_delay late_15_plus no_arrival \
0 EWR 120835 67028 20129 29970 3708
1 JFK 111279 66194 17835 25050 2200
2 LGA 104662 61120 17410 22610 3522
pct_late_15_plus
0 24.8
1 22.5
2 21.6 The four counts add up to flight_count: for EWR, 67,028 + 20,129 + 29,970 + 3,708 = 120,835. 100.0 instead of 100 keeps the decimals.
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 carrier, month, flight_count, avg_arr_delay
FROM (
SELECT
carrier,
month,
COUNT(*) AS flight_count,
ROUND(AVG(arr_delay)::numeric, 2)::float AS avg_arr_delay,
RANK() OVER (PARTITION BY carrier ORDER BY AVG(arr_delay) DESC) AS month_rank
FROM flights
GROUP BY carrier, month
HAVING COUNT(*) >= 100
) AS ranked
WHERE month_rank = 1
ORDER BY avg_arr_delay DESC;
"""
run_query(query, engine)What it prints:
carrier month flight_count avg_arr_delay
0 FL 7 263 44.97
1 EV 12 4307 26.77
2 VX 6 480 26.68
3 WN 12 1099 23.97
4 9E 7 1494 23.76
5 B6 7 4984 23.35
6 MQ 6 2178 23.14
7 DL 7 4251 14.92
8 UA 12 4931 14.00
9 US 6 1736 11.31
10 AA 4 2722 8.2611 rows. HAVING COUNT(*) >= 100 stays in the inner query; the rank filter can only go in the outer query.
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 = """
SELECT
flight_date,
flight_count,
avg_dep_delay,
ROUND(AVG(avg_dep_delay) OVER (ORDER BY flight_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)::numeric, 2)::float AS avg_dep_delay_7day
FROM (
SELECT
MAKE_DATE(year::int, month::int, day::int) AS flight_date,
COUNT(*) AS flight_count,
ROUND(AVG(dep_delay)::numeric, 2)::float AS avg_dep_delay
FROM flights
GROUP BY year, month, day
) AS daily
ORDER BY avg_dep_delay DESC
LIMIT 10;
"""
run_query(query, engine)What it prints:
flight_date flight_count avg_dep_delay avg_dep_delay_7day
0 2013-03-08 979 83.54 21.26
1 2013-07-01 966 56.23 40.44
2 2013-09-02 929 53.03 17.28
3 2013-07-10 1004 52.86 24.82
4 2013-12-05 969 52.33 12.09
5 2013-05-23 988 51.14 20.51
6 2013-09-12 992 49.96 9.33
7 2013-06-28 994 48.83 33.00
8 2013-06-24 994 47.16 17.89
9 2013-07-22 1000 46.67 20.60The worst day, March 8, averaged 83.54 minutes, but its 7-day average is only 21.26: a one-day spike. July 1 (56.23 vs 40.44) was part of a bad week.
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 your connection string, and do not post your full answers.
Save and Submit
- Complete every Your Turn, the final practice, the offline assignment and the written answers. The optional challenges are not required.
- Restart the kernel and run all cells from top to bottom. Confirm there are no errors.
- Confirm that both files exist in
data/processed:destination_delay_summary_module9.csvandjfk_destination_reliability_module9.csv. - Confirm that
.envis not tracked by Git: it must not appear in the Source Control list or on GitHub. - Save the notebook, then commit and push:
git add .,git commit -m "Complete Module 9 Neon SQL fundamentals lab",git push. - Open your repository on GitHub and check that the latest commit and both CSV files are there, and that
.envis not. Submit the repository link in Canvas.
Lab Wrap-Up
You worked with data in a database through one repeatable pattern:
- Connect safely: the connection string in
.env, an engine fromcreate_engine(), andpd.read_sql()to run queries. - Explore first: list the tables and look at the columns before you write the real query.
- Ask in SQL:
SELECTandFROM;WHEREfor rows;GROUP BYwith aggregates for summaries;HAVINGfor groups;ORDER BYwithLIMITfor a meaningful top list. - Validate: row counts, missing values with
COUNT, distinct categories and possible outliers. - Combine: SQL for the steps that touch every row, Pandas for new columns, choices and charts.
Module 10 joins the five tables in SQL to build an analytic base table, and shows the two ways to run SQL on your own project data.