Project 1: BigQuery's First Heist

Mission Briefing

Welcome to your first major challenge at Startup, Inc.! Dr. Gru needs the next big opportunity to invest in. You're going to pick a big public dataset, ask three sharp questions a business owner or CEO would ask, and answer them with SQL in a Colab notebook. Along the way you'll also hand-draw two debug tables on paper to trace how your queries actually execute, row by row. More on that below.

Dataset Inspiration

Past favorites: COVID-19 spread, StackOverflow users, GitHub repos, NYC taxi tips, crypto blockchain, climate change, election ads.

Ambitious example: one student asked "When is the best time to get away with murder?" and joined Chicago crime (1.3 GB), EPA temperature (1.8 GB), and GDELT news (204 GB). Dataset scale on its own isn't graded, but that level of curiosity tends to produce the best projects.

Requirements at a Glance

Dataset: 3+ base tables you query, collectively at least 500 MB. Prove it with a metadata query in your notebook: SELECT table_id, row_count, size_bytes FROM `your_dataset.__TABLES__` (or INFORMATION_SCHEMA.TABLE_STORAGE).
Schema write-up: briefly document your tables in the notebook. Include the keys, how they relate, and NULL handling.
Queries: 10+ total. Start with 2 correlated subqueries, 2 CTEs, and 3 window functions. One window query should compare RANK vs. ROW_NUMBER (see FAQ). Finally, answer 3+ central, decision-worthy questions using 2+ JOINed tables and a WHERE or HAVING filter (2 + 2 + 3 + 3 = 10). Show output for each, LIMITed to 10–20 rows.
Debug tables: two paper debug tables. One traces a window function (to show the tie handling in RANK or ROW_NUMBER). One traces a correlated subquery (to show the inner query re-evaluating for each outer row). Both hand-drawn, photographed, and embedded in the notebook. Shorthand is fine. See the FAQ for examples.
AI Takeaways: a short reflection at the end of the notebook. Write 2 or 3 examples of how you used AI, at least one a surprise, plus a plain-English walkthrough of one complex query. Jot examples as they happen so you can write them up later.
Work: Individual (no partners).
Submission: Colab notebook as .ipynb to Gradescope, due Oct 16.

AI Policy and Honor Code

AI: Draft each query yourself first and see where it lands. Then you are welcome to bring in AI as a second opinion to brainstorm, explain, or debug. As we saw in query equivalence, one question has many equivalent queries, each with tradeoffs, and some look-alikes that are not equivalent. Compare your draft with the LLM's: are they equivalent, and how do their tradeoffs differ? That builds your intuition for the next query. Drafting it yourself is the best practice you will get. If AI gets in the way of that, you are not helping yourself for the final or job interviews.
Honor Code: Submit only code you wrote personally. Do not consult prior solutions or post yours online. Discuss approaches with classmates, but do not share SQL.

A check for yourself: could you walk a friend through each query with the chat window closed? If not, rewrite the query.

Prerequisites & Resources

BigQuery setup: see Ed #project1 announcements.
BigQuery: Sandbox guide, Public datasets.
Module pages to review: Correlated Subqueries, CTEs, Window Functions, JOINs.
Project 1 template notebook: cs145_project1_template_fa26.ipynb in the course Drive folder. Pre-structured with section headers, the TOC, debug-table placeholders, and BigQuery auth scaffolding.

Estimated Work-Back Schedule

Three weeks of work, roughly paced to the class lectures. Basic SQL lands in Week 1. Intermediate SQL (CTEs, window functions, subqueries) arrives by Week 2. Each week has a punchline; the rest is detail. Jot AI surprises as they happen.

Week 1 · Pick a dataset worth three weeks

4 to 5 hours · explore and schema

Pick a dataset that actually excites you. Confirm it meets the 500 MB / 3-table bar. Star it in the BigQuery console. Sketch 3 draft questions. Run a handful of SELECT … LIMIT 20 probes to learn the schema.

Week 2 · Write the queries

7 to 8 hours · CTEs, subqueries, joins

Answer your 3 questions with JOINs, CTEs, and correlated subqueries. Most of the 10-query requirement lands here. Keep noting AI surprises, especially the times AI got something wrong and you caught it.

Week 3 · Trace it on paper, ship it

5 to 7 hours · windows, debug tables, submit

Add the 3 window queries, including the RANK vs. ROW_NUMBER comparison. Hand-draw your two debug tables (one window function, one correlated subquery), photograph them, embed them. Write the AI Takeaways section. Make sure every cell has run with output, TOC is visible, and submit to Gradescope.

Expected total: 16 to 20 hours solo.

Rubric (95 pts, plus the +5 insight bonus for 100)

Dataset & Schema 10 pts

5 pts. Dataset meets the size bar (at least 500 MB across 3+ tables), shown with a metadata query (__TABLES__ or INFORMATION_SCHEMA) in the notebook, not just asserted.
5 pts. Clear schema description covering keys, relationships, and NULL handling.

Questions & Queries 50 pts

20 pts. Questions answered with SQL. 3+ decision-worthy questions, stated in plain English and answered with SQL that joins 2+ tables. 10 pts for the questions (see what makes a good question): each is decision-worthy and clearly stated in English. 10 pts for at least 3 queries that answer them, each joining 2+ tables with a WHERE or HAVING filter, output shown for each.
10 pts. 2 correlated subqueries with scoped variable references.
10 pts. 2 CTEs chaining logic with WITH clauses.
10 pts. 3 window functions with OVER(...), including a RANK vs. ROW_NUMBER comparison.

Paper Debug Tables 15 pts

Two hand-drawn traces, photographed and embedded in the notebook next to the queries they trace. Shorthand is fine (3 to 5 rows plus "…N more rows"). Show the edge cases that matter.

8 pts. Window function trace. One of your 3 window queries, traced to show the tie handling. If you pick a RANK query, the trace should clearly show RANK skipping numbers after ties (1,1,3). If ROW_NUMBER, show it not skipping (1,2,3). Make the partition and order visible.
7 pts. Correlated subquery trace. One of your 2 correlated subqueries, traced to show the inner query re-evaluating for each outer row. Make the scoped variable reference visible in the trace so we can see the correlation.

AI Takeaways 10 pts

A short, informal reflection at the end of your notebook: a few real examples of how you used AI on this project, and one query explained in plain English.

6 pts. Examples. Share 2 or 3 short examples of how AI helped, a few sentences each: a concept it explained well, a bug it helped you find, a question it helped you sharpen, or a prompt that worked much better than another. At least one should be a surprise, good or bad. Be specific: what you asked, what AI did, and what you did next. For instance, "My first draft of the correlated subquery returned duplicates. AI suggested I trace a COUNT(*) on each side, and that showed I had joined on the wrong key."
4 pts. Explain one query in plain English. Pick a complex query you built (CTE, window, 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.

Notebook Hygiene 10 pts

Clear section headers and a visible Table of Contents.
All cells run, outputs visible, no broken queries in the submission.
Queries have comments in your own words, not raw AI responses pasted in.
Consistent naming and readable formatting. The notebook should read like a finished deliverable.

Base Grading Model

Quality work gets 95/100: meet every requirement with working, commented SQL and a clean notebook. This is a high A and what most students should target.

Above 95: CAs will pick the top 5% of submissions in the class, based on quality, for these 5 points.

Frequently Asked Questions

Questions & SQL

Q: What makes a good question?

State it in plain English, specific and decision-worthy: [ACTION VERB] + [SPECIFIC METRIC] + [COMPARISON/CONDITION] + [TIMEFRAME/SEGMENT]. A strong question is one whose answer could change a decision (that earns the 10 question points). The query that answers it earns the 10 join-query points by joining 2+ tables and filtering with WHERE or HAVING.

Good

IDENTIFY drivers WHO earn less on rainy days DESPITE driving more hours
FIND neighborhoods WHERE crime drops WHEN temperature > 90°F
DETECT StackOverflow users WHO ask 80% of questions BUT never answer

Bad

How many crimes happened?
What is the average trip distance?
List all users with >100 posts

Q: Can one query count for multiple requirements?

No. Aim for at least 10 distinct queries: 2 correlated subqueries, 2 CTEs, and 3 window functions, then at least 3 that use 2+ JOINed tables to answer your questions. One query can combine techniques (a CTE with a window in the same SELECT) but counts once, so make sure you end up with at least 10 distinct.

Q: Do the 3 questions and the 10+ queries overlap?

Yes. The 10+ queries are how you answer your 3 questions, not a separate assignment. At least 3 use a JOIN to answer the questions directly; the 2 correlated subqueries, 2 CTEs, and 3 window functions dig deeper into those same questions. Together they are your 10 or more.

Q: What counts as a "window function"?

Anything with an OVER(...) clause. Plain AVG(col) without OVER does not count. Example: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date). To make RANK vs. ROW_NUMBER differ, order by a non-unique column so ties exist: RANK skips numbers after ties (1,1,3), ROW_NUMBER doesn't (1,2,3).

Q: How much output should I show?

10 to 20 rows per query with LIMIT. Show the full summary for aggregations. This is a SQL-only project. No Python or pandas analysis needed.

Datasets & BigQuery

Q: Which datasets do students typically use?

Popular BigQuery public datasets that meet the 500 MB and 3-tables bar. These are starting points. The best projects come from genuine curiosity.

StackOverflow

bigquery-public-data.stackoverflow

Tables: posts, users, comments, badges. About 50 GB.

NYC Taxi Trips

bigquery-public-data.new_york_taxi_trips

Tables: yellow and green trips. About 100 GB.

Chicago Crime

bigquery-public-data.chicago_crime

Tables: crime and socioeconomic data. About 1.5 GB.

GitHub Activity

bigquery-public-data.github_repos

Tables: commits, files, contents, languages. About 3 TB (use samples).

COVID-19

bigquery-public-data.covid19_*

Regional datasets; varies by region.

NOAA Weather

bigquery-public-data.noaa_*

Tables: gsod, stations, hurricanes. About 100 GB.

Q: Should I copy BigQuery tables into my own dataset?

No. Always query public datasets directly by reference (bigquery-public-data.stackoverflow). Star them in the console for easy access. This is how you'll work with production data in industry.

Q: How much will BigQuery cost?

Nothing. We use the BigQuery sandbox, which Google provides free: 1 TB of query processing per month and 10 GB of storage, with no billing account. Project 1 does not come close. Before running a query, check "This query will process X GB" in the top-right of the console. On big datasets like GitHub (3 TB), always filter by date, use TABLESAMPLE SYSTEM (10 PERCENT), and avoid SELECT *: one unfiltered scan of a 3 TB table would use up a month of free queries. Two sandbox limits to know: tables you create expire after 60 days, and INSERT/UPDATE/DELETE are not supported, so query public datasets by reference or build tables with CREATE TABLE ... AS SELECT.

Q: Which LLMs should I use? Do you provide credits?

No LLM credits are provided. Use free tiers: ChatGPT, Claude.ai, Gemini, or GitHub Copilot (free with a student account). Ask focused questions, not "write my whole project." If you hit rate limits, that's a feature. Practice without AI.

Debug Tables

Q: What are paper debug tables and how do I draw one for 100K+ rows?

A paper debug table is a row-by-row trace of how your SQL executes. It's the same format you saw in the SELECT-FROM-WHERE and debug lessons. You don't trace every row. Use shorthand:

Sample rows. Show 3 to 5 representative rows plus "…(10K more rows)".
GROUP BY shorthand. "genre='Rock': 3 rows shown + …(497 more) = 500 total".
Edge cases. Include MIN, MAX, NULL, and any ties that affect your logic.
Aggregation notation. "AVG(user_1) = (4.5+4.2+3.9)/3 = 4.2".

For RANK() on 1000 users, show top 3, ties if any, bottom 2. That's enough to verify logic.