Show HN: I made a SQL game to help people learn / challenge their skills | Hacker News Reader

Show HN: I made a SQL game to help people learn / challenge their skills

lost-at-sql.therobinlord.com

547 pointsrobinLord117 comments

Open articleView on HN

Great idea, SQL is an undervalued skill

Feedback:

I did this exercise:

https://lost-at-sql.therobinlord.com/challenge-page/case

The specification says:

> Clownfish are between 3-7 inches in length, weigh around half a pound, and live in the coral reef.

around half a pound is meaningless, the spec should be exact.

It was annoying having to scroll from the input at the bottom of the page to the specification at the top of the page to refer to it.

The test cases are insufficient. I only wrote this:

> select *, CASE WHEN species_name = "clownfish" AND length NOT BETWEEN 3 AND 7 AND weight != .5 AND habitat_type != "coral reef" THEN "imposter" ELSE "not imposter" END imposter_status from marine_life;

And passed the check at the end.

I didn't like that it kept track of the number of syntax errors and how long it took me to finish, that doesn't seem conducive to learning/practicing.

There seemed to be a lot of preamble to get to the challenge page. It seems like those should be linked directly from the homepage.

The format button didn't work on my code above.

Syntax highlighting seems broken for some functions, like IIF.

It would be nice if multiple SQL dialects were supported, forcing SQLite makes this more of an exercise in 'translate the dialect you know into SQLite'.

I didn't love the challenge I did overall, it was a single CASE statement, which seems to be testing logic more than any SQL knowledge. Maybe because it's a warmup?

Mh. The sarcastic, cynic admin in me is strongly advising against being good at SQL, understanding how SQL is executed in your specific database, how optimizations work and such.

Like, it is extremely impressive what a PostgreSQL server with some well-written queries can do. There is this running gag of how hard it is to replace a reporting solution based off of one database system and a bunch of smartly written queries with supporting indexes and it's honestly not wrong.

But that's also the problem - suddenly, you end up being the guy being paged for emergency "databases are on fire" situations, and "these queries are impossible to optimize to un-fire the database" and such. And once you get some time to think about these queries, you can kinda rewrite them to take 10 seconds instead of 10 hours by eliminating some subquery dependencies with some acrobatics.

So, imo: Do learn SQL and how it works, so my job gets easier. But keep it very, very secret that you know it well.

I also very much enjoy this game. It's very cute and fun.

I run a reporting & analytics team at a ~500 person SW company and I've never been paged to optimize a query because the database is on fire. That's like designing your sprinkler system while the building burns down. I've also never found a query that I can reduce from 10 hrs to 10 seconds. Maybe one that never finishes to actually running, but not the kind of magnitude improvement you reference.

My guess is that if you moved from reporting and analytics to a DBA role in web operations in a large scale shop you'd see something very different. It starts with programmers who don't know SQL (they use some ORM instead) and who don't know how to get or interpret a query plan and who aren't aware of indexes with the results you might imagine. These same developers tend not know how to get decent performance from a NoSQL database either. And since the code is in an ORM it can be difficult for DBAs to do a decent code review.

Also, there's a very big difference between the impact of performance requirements for reports which run once or even 20-30 times per day and something that runs in every transaction where that transaction runs 10s to 1000s of times per second. If you optimize a report to run 3% faster that's unlikely to make a difference. If you optimize a hot path transaction 3% on a large website that could mean you have a few less hosts/instances to provision and the impact is a direct budget impact of $5K/yr or more.

Oh and as an SRE I've found several queries where I've made 2 orders of magnitude performance improvements in one of those cases the process was beginning to fail regularly because the queries were so bad.

5k/yr? I've saved 5k+/mo on some clusters, by optimizing lots of 100-1000ms queries into the <5ms range!

It's really fun.

Also agree on the reporting vs transactional stuff. One reporting cluster I run I don't really look at queries that take 30 seconds. Optimizing them that much just doesn't matter compared to the queries ran interactively.

For a devs sake - and before I go ask my new AI overlord - what is a good rule of thumb re: indexing? My naive assumption has always just been that if its a column you utilize to filter a lot of your queries, and youre unhappy with current perf, index it. Examples would be datetime columns (if sorting serverside) and columns like TenantId, UserEmail, etc.

I'd say it depends a bit on your access pattern, the query frequency and the time you have for each query, and beyond that on your insert performance requirements. I'm kinda referring to PostgreSQL here since that's what we use at work. In there, I have like 2-3 rules of thumb by now:

A remarkable query pattern treats tables like hashmaps. You have a query, you expect 0 or 1 results, and you want those results as fast as possible, even more so than usual. This is usually accompanied with a low to very low write frequency in relationship to reads against this. Examples are - like you say - a login: Give me the account for this mail - generally, you'll have many more logins than registrations. Give me the current chat-room for a user - they enter a chatroom once every few hours and then look into it a lot. This usually warrants an index without much analysis. Often, foreign-key relationships also enter this pattern, but imo, that would require evidence through query plans to look further into it and normally, the primary key should handle these.

After that, it becomes a bit weird until you think about it, because you get into something like index sizes and selectiveness. The interesting part there is: Postgres on a decently quick core can iterate linearly through a few ten thousand to a low number of hundred thousand rows in a few micro- to milliseconds, per parallel worker thread. This is weighed against the cost of traversing a potentially large index - and an index cutting the dataset into too many very tiny pieces will be large. This might cause postgres to stop using an index even it if it matches conditions in the query, because just chugging through the dataset brute force in parallel is faster. And yes, I was apprehensive at first, but the optimizer tends to be right there.

So yeah, your approach sounds right there. Unless you really know this index is really good for the DB and you can make specific points about rows inspected per query, just don't bother. Once a query starts running slow - or classes of queries start running slow, throw the whole bus of EXPLAIN ANALYZE and something like a query plan visualizer[1] at it to identify the slow part. This will generally point to a slow sequential scan or a slow parallel sequential scan, or a crazy sized join. And then you can start thinking about creating some index for the conditions used in the query to chop the dataset into some c * 10k sized blocks for c being like 5-25ish usually.

The nature of that c is more about your performance requirements and the nature of these queries, as well as the write load. If this is a web app, with loads of reads and few inserts, c should probably be on the smaller side. Tenant-ID tends to be a good one, CreatedDate for some sequential data stream like an audit log or some tracking/reporting stuff, IDs transactions tend to reference this thing by. If you're looking at a write-heavy reporting setup, it might be better to be a bit coarser for quicker inserts, because who cares if a reporting query is chugging for 5 seconds? Though 5 seconds would be in that ugly uncanny valley - I can't just go and get coffee while the query runs in good faith, but it's too slow to be a good experience, lol.

And something to consider for the latter is also the general query patterns for the total application depending on the database schema. This, again, indicates that delaying index creating well into maturity of a codebase is a good idea. I've had quite a few applications and reporting-like solutions which ended up with like 6-8 different filter patterns throughout their queries. And sure, we could have created 6 different index structures to support all of those individually perfectly, but then we'd have ended up with a crazy amount of indexes which in turn wouldn't be great for insert-performance. Instead, since we waited for issues, we could identify 2 indexes that would support each individual query only to like 60% - 80% of the effect of the tailored index structure for this query, but these 2 indexes would support all queries across the board and - as we later saw - would support 90%+ of all queries created further down the line to a similar degree.

So, waiting until we had an understanding of the dataset and actual problems with actual data meant we could solve a lot of issue with a morning of analysis and a small touch of the system. And then performance issues were gone for a very long time.

1: https://explain.dalibo.com/

I’ve had more than a few queries I’ve been able to reduce from multiple minutes down to multiple seconds. 4 minutes to 15 seconds, for example. In some cases, I’d written the original, and knew it wasn’t great, but didn’t understand the impact enough to realize how bad it would get.

Never seen a 10 hour to 10 seconds, but do not doubt that it might have happened at some point. I did reduce a 25 hour ETL process down to 25 minutes, but it wasn’t solely down to queries.

> That's like designing your sprinkler system while the building burns down

Aptly, that's how this application was built over several years. And it's in the best shape out of all the old flagships at work.

> I've also never found a query that I can reduce from 10 hrs to 10 seconds. Maybe one that never finishes to actually running, but not the kind of magnitude improvement you reference.

And you never should, if you understand a bit about efficient SQL. This doesn't stop other people from having one or two levels of dependent subqueries in reporting code which passed test, as test doesn't contain reporting relevant data.

And yes, from this point it takes 1-2 questions or comments and we will go from "Your SQL query sucks" all the way to more or less bad development processes and how to do this better, CI, test data, test data management, reviews, reviews of database schemas and so on.

Obviously on top of all the other tasks and duties. Hence: Don't know SQL well, at least officially.

My experience as the performance “expert” at the company I work at isn’t necessarily that the a single query gets sped up that much absolutely, but that much relatively is possible when dealing with a query that executes very frequently.

In my experience, if one of our core queries regresses from taking 20ms to taking 600ms (which happened recently although only one certain customer databases with 100m+ rows in the table) that the query will suddenly be taking 5+ seconds.

The problems compounds as the IO and compute resources of the database gets consumed and everything starts getting even slower.

In my recent case the same query before and after was actually taking the same amount of time when the DB wasn’t “on fire”. The query had started using a lot more IO resources which became a bottleneck but you wouldn’t know by query time until suddenly we were IO constrained under load.

> you end up being the guy being paged for emergency

Why can your employer bother you outside your work hours? You should charge a lot of money for this service.

Yeah, um... exactly. It's baked into your salary, though.

Being on call is baked into your salary? I hope you are getting some serious "fuck you" money if you're effectively on call at all times.

Typically there’s an oncall rotation. So your oncall periodically (like 1 week out 4-6). It’s not something I love but I like the rest of the work. Also the oncall is just the L1 triage. For anything more complicated a lot more people get paged in until the thing is handled.

That’s my experience so far anyway.

Just no. If they want me available 24/7 they better compensate me really well for it. Even doctors won't do it without compensation. Better be some serious salary if being on call is baked into it.

That challenge was annoying because of the terrible spec. In addition to the "around 1/2 lbs" spec, it falsely claims that length and weight are metric (they are not), and it spells it "impostor" in the specified output, but expects "imposter". Both spellings are used in the spec. Frustrating.

However, students will learn not to trust the spec, so I suppose that's a valuable lesson.

> forcing SQLite

It might be the case that it's running SQLite via wasm. If so, then other database engines would need to be runnable in a browser too.

PostgreSQL has been shown to work in the browser (eg https://www.crunchydata.com/blog/learn-postgres-at-the-playg..., and also https://github.com/snaplet/postgres-wasm), so that might be an option.

Not sure about others.

Thanks, yea as you say this was a matter of having a library that could reliably/realistically handle the SQL. I found some other libraries but they were a facsimile of SQL rather than behaving like any specific standard. Thanks for the links to those other libraries! I had to fight hard against scope creep to just get this out but I'll def add this to the list for updates!

duckdb-wasm could also be an option

https://duckdb.org/2021/10/29/duckdb-wasm.html

I'm only willing to spend so much time on this but the answer seems horribly broken.

The problem statement spells it "impostor" but the answer key says "imposter".

The question says length in meters, but then the table seems to assume inches.

Then the answer thingy says "order matters" but I can't find anything about that in the question.

> I didn't like that it kept track of the number of syntax errors and how long it took me to finish, that doesn't seem conducive to learning/practicing.

I wouldn't want that in an interactive tutorial or training program, but it sounds exactly like something a game would do. It means you can try to beat your previous times, decrease the number of errors next time, or even compare yourself against others. It provides a measure to gauge progress. I'm not usually the type to care about that sort of thing in games, but I know a lot of people are.

> SQL is an undervalued skill

Wondering if chatgpt would make sql irrelevant sometime in the future?

Partly. Not because SQL isn't working technologically, but because the hardest part is figuring out what to ask and how and gpt can figure out the how while having a discussion with you about the what and giving you examples dynamically. If I just need natural language to try things dynamically across tables and I can have a discussion faster than I can google specific stackoverflow issues, I'll pick the bot. It might be confidently wrong but my colleagues do that too, often enough. The difference is that it takes 3 minutes instead of two hours, and no Teams call where you have to appease egos. If you're just really good at SQL and you craft amazing requests or figure out schemas from business needs, you probably have a bit more time to enjoy being the go-to SQL person. Probably less and less as the simpler needs will be increasingly covered, leaving a niche of expert until major advances would happen to AI (unpredictable).

SQL is just a means of expressing what you're trying to do. For some use cases it will be easier to use cgpt but if you are already fluent then it's easier to write SQL rather than put your thoughts into natural language in the form of a detailed prompt.

You’d still need to be able to specify _exactly_ what you want from the database in order for gpt to provide the corresponding sql. You’ll still need to understand the database structure and be able to think in terms of relations and what’s possible via queries.

Make irrelevant? Probably not. Make more convenient? Probably.

I guess the usefulness would be from client software like plsql or any other, implement a natural language processing powered by chatgpt, since the client already has access to the structure then it can include it in the prompt, the user would only write something vague like i want all the clients who bought with more than 500$ this year.

For everyday stuff like that it's easier to put the NLP in a BI tool, and some of them already did that.

If you're doing more involved stuff then writing a good prompt will probably need as much analytical and domain knowledge as writing SQL.

This is a great thread! As you might imagine I put in a lot of thought about whether Chat GPT made learning this stuff obsolete. Conclusion I came to is that those tools make this all more accessible (in terms of helping people to avoid things like frustrating single character mistakes) but that kind of makes knowing some SQL MORE valuable because it makes it much easier to know what direction to move in, and helps a person more easily debug confidently incorrect suggestions.

A big problem with SQL is that sometimes the answers can LOOK right if a person doesn't know the pitfalls of, say, table joins, but can in fact be quite off the rails. I also think that there's still space for highly skilled experts doing the REALLY advanced stuff but for the average user - just knowing the basics probably helps them get even more out of LLM helpers

When crafting more complex SQL queries it's quite easy to make a subtle mistake, mess up cardinality etc; I'm sure chatGPT would be excellent at mirroring those kinds of error unfortunately.

I tried to get ChatGPT do sparql, to query wikidata and I can never get my head around queries in that language. It did not go well at least beyond basics.

As someone who does mostly infrastructure admin, and occasionally has to touch both PostgresDB and MySQL databases, I found this to be a fun set of challenges.

I would like to suggest an improvement to the user experience. The scenario text is currently displayed character-by-character, which significantly prolongs the time it takes to complete the "easy" scenarios. In my case, it took 10 minutes, with 8 of those minutes spent waiting for the text to appear. A faster display of the text would enhance the overall experience.

For anyone else who runs into this: this is configurable via the cog icon in the upper left corner - you can disable the typewriter effect altogether, or speed it up.

I love the production, it's artistic and lovable, and avoids perfectionism.

The game also reminds me that the most frustrating aspect of working with SQL is navigating the results. This is mostly a UI issue, and I don't think it's solved. Scrollbars are bad, terminal output is clunky, hard to flip between table output (not helpful for cols with long content) and sections (makes comparison and overview hard), and the fact that changes to it require lots of changes in the query (shortening, mapping), just for exploring. This is made worse by the necessity to edit complex multiline statement in an interactive shell with poor editing support. Personally, I still prefer using Emacs on a file in SQL mode, in combination with an iSQL shell buffer. You collect various queries in a file, and copy the statement under the cursor to the shell for execution. An easy way to keep a collection of queries like a library or tool belt, in a way that can also be persisted and versioned.

Thanks for the kind words! All good points about the clunkiness, most of the UX improvements I added came from my frustrations just while developing the game and testing correct answers but I agree it's far from fluid.

TBH eventually this just became an exercise in me resisting further scope creep, haha

Great idea, its rare to see an FMV intro for a tech learning game. UI could be made more compact, to remove vertical scrolling and focus on task at hand, e.g. by displaying tutorial and story text in separate dismissable windows. And query autocomplete is a bit wonky: write "malfunctions", there's still autocomplete visible, and pressing Enter will choose highlighted item, although that's probably not what you wanted.

Ya, for example `select * from crew` where I typed that all out. At the end crew would try to autocomplete to staff_id

Thanks for the feedback! Yea I wanted to give people a way to select columns from a table (or see columns in a table) if they didn't know them or couldn't remember them but I think as a few people have said I probably need to think about an easy table viewing window as a future update

This is super cool. Gonna add it to my running list of SQL games: https://datalemur.com/blog/games-to-learn-sql

Thanks for putting this list together! I'm non-technical and I had stumbled upon this a couple months ago and it was very helpful for me :)

"ERROR Error: Uncaught (in promise): GetDBFromStore: No available storage method found."

Yes, there's a banner at the top that says "this site uses cookies" blah blah, but if cookies are blocked, a silent error to console without any notice to the user makes the site look broken. only as a nerdy dev willing to open up the console to see what might be causing the screen to not work would see this. a normal user would just think the thing is broken and move on. then again, a normal user would allow cookies without question, so there's that. that still does not mean the UX of a site should cause the user to think it is the site that is broken rather than the user's setup causing the site from performing

Edit: just to add that this is a real shame, as just watching the intro it is obvious that there has been a significant amount of effort put into this, and I really thought it was pretty clever. Enough so that I might allow my browser to leave lock down mode to continue checking it out

Thanks for the feedback! This was a definite learning project for me (main motivation was to learn something about JS web dev) so I've clearly missed that - will have a look at fixing it or as you say at least giving a more informative error.

Thanks for sharing the feedback and giving it a second look!

I'm glad you took that in the spirit it was intended. I'm always afraid of coming across as an asshat, and really tried to not be that way for this comment.

I'll also add, that this is one of my biggest weaknesses as a dev in providing error message in the UX in the exact manner. The code traps for them, but doing anything more than letting the dev know is where I stop as well. Maybe that's why I was keen to go looking for it, as I just finished updating one of my projects in this exact manner. I probably spent just as much time in error handling as I did in writing the code. Others will probably all nod and say "yup". ;-)

FYI, I did try your game in a non-locked down version of my browser, and really enjoyed the creativity of slogging up some SQL. I'm no DBA, and I did learn a few things that I don't typically deal with in my limited usage, so thumbs up all around!

Yea I totally get it - I face a similar challenge when it's something I'm more clued up on.

That is brilliant news that you gave it a try and enjoyed it, even better that you learned something! Thanks again for the time you took to feedback, and the time to follow up!

Agreed. A good error message could help the user make it work.

Hey all! Thanks so much for giving the game a try and for the feedback! This was a big learning project for me and I really appreciate the time.

Quick things that might help anyone who is yet to give it a go;

- You can switch off/speed up the typewriter and the tv effects in the menu

- You can skip the intro video (which plays music) by clicking the radio button in the first menu

- I have tried to make it mobile friendly but it is still MUCH easier to use on desktop, particularly with the ability to expand the SQL panel to full screen. I would really welcome suggestions about mobile friendliness though I ended up reasoning that SQL is best written on a bigger screen anyway because of the necessity to see long queries and results simultaneously

- The story mode is for learning, the challenges section will let you jump in to test your SQL skills if you already know, I need to add more detail in the menu about how "challenging" each chanterelle is but "Pudding" is a more challenging one if you're looking for it. There are a couple bits in there specifically to trip up GPT by requiring someone to look at and understand the tables because they have some classic silly data processing mistakes in them

- When you type a table name the autocomplete will suggest the full table name but ALSO all the columns of that table. I added that so that people could see the column list/ not have to remember exactly what columns are in what table/ avoid frustrating spelling errors. Appreciate it's not the same as having a window of the full table but honestly I had to fight hard against the instinct to keep working on this to just get it live!

When I get back to my laptop I'm gonna try to make some of the suggested improvements as quickly as possible, in particular making the spec for "case" better and a couple of the other bug fixes that I should have caught - thanks to those who took the time to point them out and sorry for any frustration there.

It will take longer to add things like other dialects because I'm relying on libraries to run SQL in the browser (similar with the syntax highlighting/ formatting) and I'm limited by my ability to make changes/add other options but they are added to the list!

Update!

- Have made spec for "Case" clearer and added difficulty ratings in the challenge listing page - Have fixed some silly typos (thanks for that) including calling the captain Sir regardless of name/profile pic chosen - Have improved semi-colon handling in levels that use steps and "with" to build up a query in the background

I've made a bunch of notes of the other stuff, it'll take a bit longer to get to because I've been neglecting a bunch of other things for this game, but I'm not ignoring the feedback and please keep it coming if stuff isn't working for you.

Thanks again for the feedback!

I love this game.

One thing that bothers me a little is that while the game asks for the player character’s name up front, the first officer keeps addressing the captain as “sir” throughout the game. From the player’s point of view, it might feel more inclusive to let them choose between “sir” or “ma’am” or just “captain,” or alternatively hard-code the latter.

Thank you! Appreciate that :-)

Really good point, I should have caught that! Adding it to the list to update

More than halfway through the story mode, and there are some really annoying issues around and I can't find a way to provide feedback, so I'll leave them here:

1. There should be a way to inspect the schema. As one commenter mentioned, `show create table` doesn't work, and you'll have to `select *` which will count as incorrect answer, making the error counter almost meaningless. I know there are hints and most of the times they works, but there are situations that you would want to directly look at column names (see below).

2. You can't edit the previous steps in multi-step chapters, and the answer checker is not catching some errors. For example, the step 1 of chapter 19 has the hint "watch out for column ordering", but because of point 1, there is no way to know the table structure beforehand, and I decided to just do it blindly without adjusting the order to see how it goes. The answer is obviously incorrect, but the game accepted it as the correct answer and made the textarea readonly. Now I can't fix that and have to fix it again after all the steps are completed, but some intermediate steps will be using the wrong answer.

3. (slight spoiler?) There is a logic error in step 5 of chapter 19: considering the lifts are denoted by unique names (as you are asked to group by it), the answer checker expected lifts with inoperable malfunctions to be "usable", while the hint indicates otherwise.

4. The TV effect consumes a lot of CPU power, at least on Firefox (didn't test on other browsers).

Still a great one overall, looking forward to try the challenge mode after I finish the story.

If you want to go pro you could head to Vegas and compete in Schemaverse at DEFCON.

https://news.ycombinator.com/item?id=29375911

Very creative game. Pretty basic sql but that’s probably good anything heavier would be enraging. really wonderful production putting it all together. I enjoyed it.

FWI On iOS you must refresh, or click the show/ hide the answer input section whenever the keyboard disappears. This usually happens if you use a suggestion rather than typing it out.

Thanks! Really appreciate that!

Hmm that is frustrating, you can't just tap the text area? I think I do need to have another look about how the autosuggest places the cursor back in the box. Good feedback I'll add it to the list!

Great. At mid game I noticed how cool the artwork looked for a casual game. Could it be that it was done with AI? Sure it is, the portrait of the girl with the phone on the first levels has one of those creepy hands, the ultimate tell. Good job!

Thanks yea Midjourney was a game changer for this! Really opened up the option to double down on a theme. If I had more time I would have liked to do more prompt engineering to have specific characters reoccur but as is it I already had wild scope creep, ha ha

Looks cool, the CSS is a bit janky with multiple scrollbars at least on Firefox.

yep, and the text is too slow. Maybe give an option to show all the text at once.

Otherwise i really like the game and the concept, so it's just a minor complaint :)

Thanks for the feedback! Honestly didn't realise this had got traction so only getting to it now but you can switch off or speed up the typewriter in the menu :-)

You've done an incredible job buddy, so you get my upvote. As a bit of friendly feedback, the game feels like it's moving much too slow. Perhaps allow for a click to finish typing out the task instantly?

Thank you so much! Really appreciate it :-)

Funnily enough I got that feedback with the last game I made (regex one called Slash\Escape) so I added in the option to switch off the typewriter or change the speed in the menu :-)

My apologies. My reworked comment is: You've done a great job, I'll give this a full playthrough! :)

Cool! :)

Btw, there's a busted link in the "What is Lost at SQL?" section, where it says "A SQL learning game by Robin Lord". The link on "Robin Lord" seems to be missing "https://" at the start, so it's getting wrongly turned into:

https://lost-at-sql.therobinlord.com/www.therobinlord.com

Hmm, are you open to other reports of weirdness? I'm noticing typos and similar in tutorial instructions.

Not to mention in the story itself -- they aren't severe typos, but definitely noticeable. Wondering if English perhaps not OP's first language?

Chapter 1

> You awake to an ear-splitting screetch.

Chapter 4

> the internal radio has falled to the ground and is just out of your reach

fwiw, I'm enjoying it so far! But these typos get caught by the HN editor, so...

Definitely open to feedback in that regard! Somewhat embarrassingly In both English and have a degree in English... but I had massive scope creep on this and was usually smashing out bits on planes, trains, and automobiles so tried to catch silly things but clearly missed some!

Thanks very much! Should have caught that

You may also find the SQL Murder Mystery from Knight Lab interesting.

https://mystery.knightlab.com/

A bit less introductory, but also Hanukkah of Data (2022).

https://hanukkah.bluebird.sh

Some feedback:

There are several ambiguities in the "pudding" challenge.

- Unclear if offenders are defined as taking >2 puddings or >=2 puddings (conflicting wording; note that the placeholder comment also refers to this, and should also say 'snr_manager_id' instead of 'senior_manager_id').

- Unspecified how top 5 offenders are chosen if there are ties for 5th place.

- There are cases where multiple puddings are taken at the same instant (probably shouldn't be allowed, because it would be unclear who took the last pudding).

I'm also pretty certain that the model answer is incorrect (regardless of how these ambiguities are interpreted). Please check this; I'd be happy to send you my code.

If the focus is on coming up with the SQL queries rather than getting all the fine details correct, consider adding (optional) intermediate stages to help users check their intermediate results, because debugging based on the final result is difficult.

It would be nice if a model solution was shown after solving the challenge, to compare and learn.

Finally, I think you should ask the user for permission before publishing their score on the leaderboard.

The problem with the complex edges of SQL are like regex for me: the problem isn’t whether I can figure it out once, it’s retaining it when you only need it once every 1-3+ months. For those uses I just accept that I won’t and don’t need to remember it, and will look it up when I need it.

There is something to be said for learning the edges at least once so you can “know what you don’t know” when you forget.

i like having a little area where i squirrel away notes on things like that

This would make for a great talk at the polyglot Carolina Code Conference in August in my opinion. You should consider submitting a talk. Ideal for a mixed audience where SQL is such an important constant.

https://blog.carolina.codes/p/call-for-speakers-is-now-open-...

Thanks very much! I'll definitely have a look!

There is a minor issue on Chapter 16.

The comment in the Answer section, uses the value "Recovered" but the value of interest is "Returned". This is correct in the actual story description, but wrong in the Answer comment.

Took me a few tries to figure it out.

Came here to post this, as well. It frustrated me for a few tries until I looked closer at what was actually in the data. Maybe it's meant to teach players to look at the data more critically. ;) Also noted that the "Learn" section on this chapter seems to end prematurely.

Other than this frustration, it's been fun so far!

A charitable read! Just a screw up on my part. A lot of this ended up quite hard-coded so when I updated tables sometimes I missed updating some instructions. Thanks for spotting!

And very glad you've been enjoying it aside from that! :-)

I guess future games will work like this, just with more dolls on screen and maybe with natural language (but in fact sql). The range of actions is huge.

I got up to chapter 12, but on step 2 there seems to be some error in the preamble, I always get:

    Error: Query failed: SelectSQL: queryAll: near "With": syntax error

Even if my query is a trivial

    select * from crew

(Which is obviously not a correct solution, but should be valid SQL)

Thanks for this! Hmm might be something odd coming in with the way I'm smushing together the steps using with.

Could you share what you wrote for step 1? I thought I had a fix in place for anything ending in ; but there could be other things that are causing errors when put into with

I lost the contents because I closed the window since then, but it was something like

    select staff_name, pod_group, weight_kg
    from crew
    where status != "deceased";

Interestingly, running this now gives me a different error on step 2, which definitely did not happen before:

    Error: Query failed: SelectSQL: queryAll: near ";": syntax error

My step 2 is:

    select
      staff_name,
      pod_group,
      case
        when weight_kg > 10 then weight_kg
        else weight_kg * 10
      end as fixed_weight
    from filtered_crew

Erk, the most frustrating of bugs - an inconsistent one! I think you're hitting errors because in the background I'm adding the queried together in "with" statements and "with" can't have a ; at the end inside the brackets... but the thing is I came across that issue in testing and now I automatically strip out any trailing ; when I pro ess...

Just tested it, even added a bunch of spaces and line breaks at the end to see if that was confusing things but I still seemed to get through ok.

I want to fix this but for now could you try the steps without putting a semicolon at the end? Hopefully your patience/enthusiasm hasn't totally worn out at this point but even if you don't feel like reporting back I'm keen for you to be able to get past the frustration if you're still interested! :-)

No, it's alright!

Yeah, it works if I omit the semicolon.

Hmmm, now that I think about it, I don't think I deleted the pre-filled comment when writing my original query, could it be that I thus fooled your semicolon eraser? Something like

    select * from crew;
    /* blablabla */

HN has a reply chain limit, so if you want further help you can email "hn at myusername period com".

FYI, in the learning mission, it often asks something like "Get all of the columns from the "pods_list" table where status is 'functioning', and range is more than 1500."

I think it really means "Get all of the rows from ..."

Thanks! TBH I'm specifying all columns because the output will be expecting certain columns and even if the where clause is correct the test will fail unless the columns are right :-)

Love it. Played through chapter 10 without even stopping.

One complaint. The weight vs weight_kg. In the text for the task, it just references weight. But the column name is named weight_kg. This caused me to error out and scratch my head more than once.

That's wonderful news! So glad you've had fun.

I've tried to hit a bit of a balance between english-sounding instructions and being clear on table columns, will definitely note this down to think more about

I love it! Super cute execution with the styling and scrolling story mode. Did not make it super far because on mobile ... But really fun. Seems way to elaborate for a one off side project did you do something like this before ?

Thank you! Really appreciate the kind words!

Funnily enough I did something kind of like this a while back, WAY less involved; https:https://www.therobinlord.com/projects/slash-escape

I have been thinking SQL is getting more and more important for people to learn so wanted to do something for that and I decided if I was gonna do it I wanted it to be an impressive attempt (plus I wanted to learn JS web dev, and midjourney image creation gave me the chance to really step up the visuals)

Interesting idea. Didn't get very far in Firefox private browsing mode... https://imgur.com/LZBMPff

Just made a similar comment myself

Thanks for the feedback! Will have a look!

good job on creating this! one thing i'd improve is to have a way to jump to the harder sections without doing the easy ones if you are already good at sql. one other thing is probably showing a sampling of the data so you know what's in the table column names and data formats without being "penalized" by doing a `select * from crew` initially to see the lay of the land. I do that when crafting real world queries all the time to get antiquated with it.

Thanks for this!

My broad aim was that people who want to learn could do story mode and the people who already know it can do the challenges. "Pudding" in particular is designed as a PITA test.

Yea maybe I add something that allows through simple select statements without incrementing that count. My reasoning was if everyone is getting penalised the same way is kind of fair and the autohint gives SOME idea of column names but you're right it's still necessary to look at the tables

For the first case, everywhere says "impostOr" on the page, but you expect people to use "impostEr"in your sql :/

Thank you! I'm pushing a fix for this shortly!

If you're playing sound on a website, you need an easy way to disable it or adjust the volume.

Thanks! Adding to the list of updates

How do I see the table schema? It doesn't want to accept "show table" queries

Yea sorry, unfortunately not something the SQL library I used supports, select * is the best for now but I'm adding "table preview" to the list for future updates!

Showing the schemas as part of the question would probably be a good idea then.

This looks like too much javascript and bling. I'd like to get better at SQL and some challenge exercises would be great for that. Why not just give those, and chuck the game? It took much clicking around pretty pictures and the "why do this" intro to even get to anything SQL related.

Nice just woke my partner up who has a cold with the music

Should've kept your laptop on mute

This was on the phone which is usually full muted but I had a servicefolk coming today

There's a different media volume and call volume.

Chapter 16: 'Recovered' vs 'Returned'

Isn't this so much easier with LLMs now ?

Yep! I have put a bit of thought into why this still matters and I'm not attempting to plug a twitter thread but I laid out a lot of my logic for why we should still bother to know this stuff here; https://twitter.com/RobinLord8/status/1647903067013013505?t=...

Essentially I think the fact that LLMs can do some of this makes it MORE valuable to know how to read it. That's because we're less likely to get into frustrating situations where we know the answer but are off by a character but simultaneously because LLMs can't understand the tables like we can it is valuable for us to be able to direct the investigation/ check the results

This is pretty awesome, nice job!

Fun!

wow I'll defenetly try this BTW incrediable web-design

The fonts are too small.

Wow, I love this.

Looks good!

This is amazing!! Full support

Thanks so much!

I want to hate this. Trivialising serious work makes all our luves worse.

Maybe its just me but the only way I can make "serious work" interesting is by gameifying it in some way.

Eg, can I get this code to compile and pass the test case on the first shot (less of thing with the quality of developer tools these days)? Can I weave our team name into the first letter of each title slide for this boring presentation? Instead of this busted, ancient, Python 2.4 script can I just do this in shell?

Reply on news.ycombinator.com