Week 4. SQL as source of truth — natural language → queries on CloudNote data.
Problem statement
PM asks: “Are enterprise users generating more errors after the AI sidebar experiment?” You must translate that into precise definitions, then SQL on practice-env — no trusting prose from an LLM without a query.
Outcomes
- Natural language → metric definition → SQL on
cloudnote.db. - Awareness of joins, filters, row counts.
Deliverables
- Five English questions + matching SQL + one-line interpretation each (
sql_week04.md). - DB running locally.
Links
- Phase 2
- Drills: faang_style_drills.sql
- [ ] SQLite basics: SELECT, JOIN, WHERE, GROUP BY, date filters (any tutorial).
- [ ] Skim one article on text-to-SQL failure modes (wrong join, wrong window).
Optional: pandas read_sql overview.
Time: ~2 h.
Prerequisite: practice-env/data/cloudnote.db exists. Schema: see practice-env README.
Task A — Five questions
Write 5 business questions in English. For each:
- Metric definition (numerator/denominator/window if applicable).
- SQL that answers it (run and verify rows make sense).
- One-line takeaway.
Save as sql_week04.md.
Task B — Two drills
Complete 2 prompts from faang_style_drills.sql: English definition + SQL + interpretation.
Task C — Optional LLM
Paste schema excerpt + one question into an LLM; generate SQL; review before running. Document one mistake the model almost made.
Submit: my-submissions/week-04/sql_week04.md (+ optional LLM note).
45 min. May use SQLite + cloudnote.db + schema notes — no full solutions from prior weeks.
1. Definitions
What does “SQL is source of truth” mean for PM-led AI features?
2. Query writing
Using only dim_user, fct_product_event, write a query that counts error_boundary events per segment (smb, mid, enterprise) for events after 2025-01-01. Show column names in result.
3. Ambiguity trap
PM says “active users this month.” List three ambiguities you’d resolve before writing SQL.
Self-grade: Query runs; denominators discussed; compare Phase 2 checklist.
Concepts & curated links: read the Learn chapter SQL & data validation first (topic path, not calendar-only). This file is the weekly practice bank — extra drills and a checklist on top of Readings → Practice → Exam.
1. Practice bank
| # | Problem | Data / materials | Expected outcome |
|---|---|---|---|
| P1 | Write 5 business questions + working SQL + one-line insight each. | practice-env/data/cloudnote.db |
sql_week04.md |
| P2 | Complete 2 prompts from practice-env/sql/faang_style_drills.sql. |
Same DB + drill file | Metric definition + SQL + interpretation |
| P3 | Paste schema + one question into an LLM; catch one bad SQL suggestion before running. | Schema from practice-env/README.md |
Short write-up of the mistake |
2. Week checklist
- [ ] SQL primer + drills done
- [ ]
PRACTICE.mdcomplete - [ ]
EXAM.md