Project Overview: your three decision-worthy questions
Dataset Analysis: schema, keys, NULL handling
Get Your Feet Wet: correlated subqueries, CTEs, window functions
Exploring Central Questions: join queries that answer each question
AI Takeaways: anecdotes + plain-English query
Wrap-Up: what you learned + name your insight
TODO: Explain your three decision-worthy questions you want to answer.
Dataset requirements (5 pts of the 10-pt Dataset & Schema block):
3+ base tables that you actually query.
Collectively at least 500 MB.
Star the dataset in your BigQuery console.
About the questions (10 pts):
Decision-worthy means the answer could change a real choice someone would make.
Stated in plain English and specific enough to answer with your data.
Each is answered in Section 4 by a query that joins 2+ tables (graded there).
Aiming for the +5 insight bonus? The +5 goes to the top 5% of submissions in the class, judged on the quality of analytical insight: a query result or writeup that genuinely changes how you would think about the data, the kind of thing worth telling someone about (not "I joined four tables"). Independent of the 95-pt quality bar; you earn it on top of your final score. Keep this in mind when you pick your three questions: at least one should be the kind whose answer could surprise you.
Describe your dataset:
Tables and sizes: list each table with row count and size on disk
Keys: which columns are primary/foreign keys; how the tables join
NULL-prone columns: which columns have meaningful NULLs and what they mean
SQL: Show table sizes, row counts.
SQL: Explore keys and joins.
SQL: Check for NULLs, duplicates.
Build the SQL muscles. Comments matter. Every query should have a one-line title and a brief description of what it does and why.
This section is your 2 correlated subqueries, 2 CTEs, and 3 window functions, output shown for each. Your central questions and the join queries that answer them come next in Section 4. Together that is 10+ queries; extras are welcome.
Two paper debug tables are required (15 pts total):
One traces a correlated subquery (showing the inner query re-evaluating per outer row).
One traces a window function (RANK or ROW_NUMBER, with the partition + order visible, and tie handling clear).
Photograph each hand-trace and paste the image into the marked cell below.
Two queries with scoped variable references from the outer query into the inner.
# write code here
%%bigquery --project $project_id
# YOUR QUERY HERE
Debug table (required, 7 pts): paste a photo of your hand-traced correlated subquery here. Show the inner query re-evaluating for each outer row, with the scoped variable reference visible.
# write code here
%%bigquery --project $project_id
# YOUR QUERY HERE
Two queries with WITH clauses chaining logic across steps.
# write code here
%%bigquery --project $project_id
# YOUR QUERY HERE
# write code here
%%bigquery --project $project_id
# YOUR QUERY HERE
Three queries with OVER(...). At least one comparison of RANK vs ROW_NUMBER so the tie-handling difference is visible.
# write code here
%%bigquery --project $project_id
# YOUR QUERY HERE
# write code here
%%bigquery --project $project_id
# YOUR QUERY HERE -- run the same data through RANK() and ROW_NUMBER() side by side.
Debug table (required, 8 pts): paste a photo of your hand-traced window function here. Make the partition and order visible. If you chose RANK, show ties producing 1,1,3. If ROW_NUMBER, show 1,2,3.
# write code here
%%bigquery --project $project_id
# YOUR QUERY HERE
One section per question from Section 1. Each question is decision-worthy and stated in plain English, answered by one or two queries that join 2+ tables and include a WHERE or HAVING filter, output shown, plus a short written analysis of what the result means and the decision it could change. These are the 3+ questions and join queries from the requirements.
Remember: every query must have inline comments in your own words.
SQL query and analysis.
SQL query and analysis.
SQL query and analysis.
A short reflection. Informal tone is fine. Two parts, both required (10 pts total in the rubric).
Share 2 or 3 short anecdotes about using AI on this project. A few sentences each. At least one should be a surprise: AI got something wrong and you caught it, or it nailed something unexpected, or a prompt worked way better than another.
Anecdote 1: [a few sentences]
Anecdote 2: [a few sentences]
Anecdote 3 (optional): [a few sentences]
Pick a complex query you built (CTE, window function, or correlated subquery). In 2 or 3 sentences, describe what it does in a way a reader who doesn't know SQL could follow. Explaining SQL clearly is a huge part of real data work.
Your plain-English explanation here.
Before you submit:
[ ] Table of Contents is visible at the top.
[ ] Every cell has been run and every output is visible.
[ ] Every query has a comment in your own words (not raw AI responses pasted in).
[ ] Consistent naming and formatting, so the notebook reads like a finished deliverable.
[ ] Submission is a single .ipynb uploaded to Gradescope.
One or two paragraphs: what did you learn, what surprised you, what would you do differently next time?
If any of your queries surfaced a finding that genuinely changed how you would think about the data (the kind of thing worth telling someone about), name it explicitly here. The +5 insight bonus goes to the top 5% of submissions, judged on exactly this kind of finding.