Writing Debug Tables: Your Logic Verifier
Core Concept
- Example: LLM claims 4 users, manual trace shows 1 - debug tables expose semantic errors.
Debug tables are your SQL logic's truth serumβcrucial for debugging and acing those whiteboard interviews.
Why This Matters
LLMs and Semantics
- Syntax: 95%+ accurate
- Semantics: 16-82% accurate per BIRD benchmarks
- See LLM Debug
Whiteboard Interviews and Tests
- Filter out expensive LLM cut-and-pasters
- "Is this query correct?" - common question
- Logic = what gets you hired
- See Learning Outcomes
Actually Faster
- Avoid reprompting loop: 30 minutes
- Drawing one debug table: 5 minutes
- Build intuition
Example 1: Users with Multiple Genres
Step-by-Step Debug Table:
Step 1: FROM + JOIN (All listen records with song info)
| user_id | name | song_id | genre | rating | |
|---|---|---|---|---|---|
| 1 | Mickey | 1 | Pop | 4.5 | |
| 1 | Mickey | 2 | Pop | 4.2 | |
| 1 | Mickey | 6 | Classic | 3.9 | |
| 2 | Minnie | 2 | Pop | 4.7 | |
| 2 | Minnie | 7 | Classic | 4.6 | |
| 2 | Minnie | 8 | Classic | 3.9 | |
| 3 | Daffy | 1 | Pop | 2.9 | |
| 3 | Daffy | 2 | Pop | 4.9 | |
| 3 | Daffy | 6 | Classic | NULL |
Step 2: GROUP BY user_id, genre (Create groups)
| user_id | genre | Notes: songs_in_group | Notes | |
|---|---|---|---|---|
| 1 | Pop | 2 rows | β Keep (β₯2) | |
| 1 | Classic | 1 row | β Filter out | |
| 2 | Pop | 1 row | β Filter out | |
| 2 | Classic | 2 rows | β Keep (β₯2) | |
| 3 | Pop | 2 rows | β Keep (β₯2) | |
| 3 | Classic | 1 row | β Filter out |
Step 3: HAVING COUNT(*) >= 2 (Final output)
| user_id | genre | song_count | |
|---|---|---|---|
| 1 | Pop | 2 | |
| 2 | Classic | 2 | |
| 3 | Pop | 2 |
Shortcuts for Big Data
Nobody Traces 100,000 Rows!
Instead of This:
Use This Shorthand:
Simple Notation Tricks
| Scenario | Full Trace | Smart Shorthand |
|---|---|---|
| 1000 rows after JOIN | Show all 1000 | "First 3... middle ties... last 2" |
| GROUP BY with counts | List every row | "Rock: ~500, Pop: ~300" |
| Window function ranks | Show all ranks | "Top 3, any ties, bottom 2" |
| Complex aggregation | Calculate exactly | "AVG β 4.2" (close enough!) |
Quick Verification Checklist
- JOINs: Expected row count? (not accidentally cartesian? INNER? LEFT? OUTER?)
- NULLs: Handled correctly? (IS NULL vs = NULL?)
- Groups: Right granularity? (user vs user+genre?)
- Aggregates: Makes sense? (COUNT(*) vs COUNT(column)?)
- Windows: Partitions correct? (RANK vs ROW_NUMBER?)
Remember: It's YOUR Tool
- Tool for correctness, not busy work - skip obvious parts, focus on confusing bits
- Like multiplication tables in school - use them often early to build intuition
- You'll naturally start skipping easy steps as your SQL brain develops
- Use simple conventions that interviewers and graders can follow (for partial credit!)
- Approximate liberally - "~500 rows" is perfectly fine
For Interviews, PSETs, Tests
β Cryptic Hieroglyphs
π β π π = 2 π£
πΆ β π πΏ = 1 β
π
± β π π = 2 π£
β΄ π£ = {ππ, π
±π}
"Trust me, π means Mickey and π£ means keep..."
β Clear Convention
User 1, Pop: 2 songs β Keep β
User 2, Pop: 1 song β Filter β
User 3, Pop: 2 songs β Keep β
Final: {(1,Pop,2), (3,Pop,2)}
Interviewer: "I can follow this!" (+partial credit)