u/Plane_Big_5912

say you have customers on the left and orders on the right.

a LEFT JOIN keeps every customer, even if they have no matching order. for those rows, the columns from orders are NULL.

that creates a small gotcha which i saw a few players falling for in their queries:

-COUNT(*) counts the joined row.

-COUNT(o.id) only counts rows where o.id isn't NULL.

so this:

customer | order_id

Alice | NULL

Bob | 123

Bob | 456

becomes:

COUNT(*) | COUNT(o.id)

Alice 1 | 0

Bob 2 | 2

I made a 43 sec chalkboard animation showing the join happen visually:

https://www.youtube.com/watch?v=zxSZFMtyheA

is this helpful? i could try making a few more then.

I wanted some feedback on some parts of a course I’ve been building since I officially have tunnel vision from doing this.

1. What dirty data problems do you wish beginners had practised earlier? I already have string prices, inconsistent enums and messy joins, but I’m sure I’m missing common production failures.
2. The grader runs each query and a reference query across several generated datasets, then compares the results using exercise specific ordering and decimal rules. Does that sound sane? What edge cases would you expect it to get wrong?
3. I’m adding a placement test so experienced users can skip ahead. It hasn’t been tested on too many non beginners yet. What would feel like a fair bar for placing someone into joins, CTEs or window functions?

It’s a browser course wrapped in a visual novel.

https://tsunderesql.itch.io/tsunderesql

This had been sitting in my pipeline on and off for a year.

I had been prepping for some mid-senior SQL interviews recently, got tired of bouncing between tutorials and decided to finally finish the whole thing.

It runs entirely in the browser using DuckDB. There are 208 hands on exercises where you write SQL, run it, and get graded.

I wrapped it in an anime visual novel as retention mechanism

1. Your first instructor is a tsundere - she grades your queries, not like she really wants to. Wrong answers get scolded and right ones get reluctant praise.
2. solving problems raises affection with the instructors and unlocks story - the plot progresses through SQL.
3. There's a gacha wardrobe and 2 more instructors to unlock and cast of quirky side characters.

It’s free and needs no signup: TsundereSQL

Happy to answer questions about the course or how the browser-side grading works.