8 ms·

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

547 points by robinLord 3y ago

justinclift 3y ago
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:// https://" at the start, so it's getting wrongly turned into: https://lost-at-sql.therobinlord.com/www.therobinlord.com https://lost-at-sql.therobinlord.com/www.therobinlord.com
- justinclift 3y ago
  Hmm, are you open to other reports of weirdness? I'm noticing typos and similar in tutorial instructions.
  - taway789aaa6 3y ago
    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...
    - robinLord 3y ago
      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!
- robinLord 3y ago
  Thanks very much! Should have caught that
nhinck 3y ago
Looks cool, the CSS is a bit janky with multiple scrollbars at least on Firefox.
- ajsnigrutin 3y ago
  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 :)
  - robinLord 3y ago
    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 :-)
sour-taste 3y ago
Great idea, SQL is an undervalued skill Feedback: I did this exercise: https://lost-at-sql.therobinlord.com/challenge-page/case 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?
- justinclift 3y ago
  > 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-playground https://www.crunchydata.com/blog/learn-postgres-at-the-playg..., and also https://github.com/snaplet/postgres-wasm https://github.com/snaplet/postgres-wasm), so that might be an option. Not sure about others.
  - faxmeyourcode 3y ago
    duckdb-wasm could also be an option https://duckdb.org/2021/10/29/duckdb-wasm.html https://duckdb.org/2021/10/29/duckdb-wasm.html
  - 3y ago
duckqlz 3y ago
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.
- robinLord 3y ago
  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!
lbj 3y ago
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?
- robinLord 3y ago
  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 :-)
  - lbj 3y ago
    My apologies. My reworked comment is: You've done a great job, I'll give this a full playthrough! :)
butz 3y ago
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.
- chrisan 3y ago
  Ya, for example `select * from crew` where I typed that all out. At the end crew would try to autocomplete to staff_id
  - robinLord 3y ago
    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
leetrout 3y ago
You may also find the SQL Murder Mystery from Knight Lab interesting. https://mystery.knightlab.com/ https://mystery.knightlab.com/
- saulpw 3y ago
  A bit less introductory, but also Hanukkah of Data (2022). https://hanukkah.bluebird.sh https://hanukkah.bluebird.sh
febeling 3y ago
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.
- robinLord 3y ago
  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
brightball 3y ago
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-2023 https://blog.carolina.codes/p/call-for-speakers-is-now-open-...
- robinLord 3y ago
  Thanks very much! I'll definitely have a look!
smackeyacky 3y ago
I want to hate this. Trivialising serious work makes all our luves worse.
- anthomtb 3y ago
  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?
NickSingh 3y ago
This is super cool. Gonna add it to my running list of SQL games: https://datalemur.com/blog/games-to-learn-sql https://datalemur.com/blog/games-to-learn-sql
- bmau5 3y ago
  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 :)
CalRobert 3y ago
If you want to go pro you could head to Vegas and compete in Schemaverse at DEFCON. https://news.ycombinator.com/item?id=29375911 https://news.ycombinator.com/item?id=29375911
ogig 3y ago
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!
- robinLord 3y ago
  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
natemcintosh 3y ago
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 ..."
- robinLord 3y ago
  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 :-)
inportb 3y ago
Interesting idea. Didn't get very far in Firefox private browsing mode... https://imgur.com/LZBMPff https://imgur.com/LZBMPff
- dylan604 3y ago
  Just made a similar comment myself
  - robinLord 3y ago
    Thanks for the feedback! Will have a look!
iideamanai 3y ago
This is amazing!! Full support
- robinLord 3y ago
  Thanks so much!
dylan604 3y ago
"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
- inportb 3y ago
  Agreed. A good error message could help the user make it work.
- robinLord 3y ago
  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!
  - dylan604 3y ago
    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!
mmh0000 3y ago
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.
- cldellow 3y ago
  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.
kroltan 3y ago
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)
- robinLord 3y ago
  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
  - kroltan 3y ago
    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
    - robinLord 3y ago
      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! :-)
thebiglebrewski 3y ago
This is pretty awesome, nice job!
user3939382 3y ago
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.
- weaksauce 3y ago
  i like having a little area where i squirrel away notes on things like that
ericzawo 3y ago
Wow, I love this.
grogenaut 3y ago
Nice just woke my partner up who has a cold with the music
- counttheforks 3y ago
  Should've kept your laptop on mute
  - grogenaut 3y ago
    This was on the phone which is usually full muted but I had a servicefolk coming today
    - counttheforks 3y ago
      There's a different media volume and call volume.
8n4vidtmkvmk 3y ago
How do I see the table schema? It doesn't want to accept "show table" queries
- robinLord 3y ago
  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!
  - 8n4vidtmkvmk 3y ago
    Showing the schemas as part of the question would probably be a good idea then.
nodakai 3y ago
Chapter 16: 'Recovered' vs 'Returned'
Hackbraten 3y ago
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.
- robinLord 3y ago
  Thank you! Appreciate that :-) Really good point, I should have caught that! Adding it to the list to update
weaksauce 3y ago
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.
- robinLord 3y ago
  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
flazawtz 3y ago
Looks good!
wdiamond 3y ago
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.
fredeerock 3y ago
Fun!
spprashant 3y ago
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.
- trollmasterd 3y ago
  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!
  - robinLord 3y ago
    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! :-)
haruka_ff 3y ago
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.
simlan 3y ago
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 ?
- robinLord 3y ago
  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 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)
throwaway81523 3y ago
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.
robinLord 3y ago
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!
- robinLord 3y ago
  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!
LegitShady 3y ago
If you're playing sound on a website, you need an easy way to disable it or adjust the volume.
- robinLord 3y ago
  Thanks! Adding to the list of updates
k_roy 3y ago
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.
- robinLord 3y ago
  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
reraos 3y ago
For the first case, everywhere says "impostOr" on the page, but you expect people to use "impostEr"in your sql :/
- robinLord 3y ago
  Thank you! I'm pushing a fix for this shortly!
arisAlexis 3y ago
Isn't this so much easier with LLMs now ?
- robinLord 3y ago
  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=V17_ex1Vnb1YHTsT_Z8WNQ&s=19 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
bni 3y ago
The fonts are too small.
hddqsb 3y ago
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.
a_lesanka 3y ago
wow I'll defenetly try this BTW incrediable web-design
deleted 3y ago
[deleted]

547 points by robinLord 3y ago

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:// https://" at the start, so it's getting wrongly turned into: https://lost-at-sql.therobinlord.com/www.therobinlord.com https://lost-at-sql.therobinlord.com/www.therobinlord.com

justinclift 3y ago
Hmm, are you open to other reports of weirdness? I'm noticing typos and similar in tutorial instructions.
- taway789aaa6 3y ago
  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...
  - robinLord 3y ago
    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!
robinLord 3y ago
Thanks very much! Should have caught that

nhinck 3y ago

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

ajsnigrutin 3y ago
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 :)
- robinLord 3y ago
  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 :-)

sour-taste 3y ago

Great idea, SQL is an undervalued skill Feedback: I did this exercise: https://lost-at-sql.therobinlord.com/challenge-page/case 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?

justinclift 3y ago
> 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-playground https://www.crunchydata.com/blog/learn-postgres-at-the-playg..., and also https://github.com/snaplet/postgres-wasm https://github.com/snaplet/postgres-wasm), so that might be an option. Not sure about others.
- faxmeyourcode 3y ago
  duckdb-wasm could also be an option https://duckdb.org/2021/10/29/duckdb-wasm.html https://duckdb.org/2021/10/29/duckdb-wasm.html
- 3y ago

duckqlz 3y ago

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.

robinLord 3y ago
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!

lbj 3y ago

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?

robinLord 3y ago
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 :-)
- lbj 3y ago
  My apologies. My reworked comment is: You've done a great job, I'll give this a full playthrough! :)

butz 3y ago

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.

chrisan 3y ago
Ya, for example `select * from crew` where I typed that all out. At the end crew would try to autocomplete to staff_id
- robinLord 3y ago
  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

leetrout 3y ago

You may also find the SQL Murder Mystery from Knight Lab interesting. https://mystery.knightlab.com/ https://mystery.knightlab.com/

saulpw 3y ago
A bit less introductory, but also Hanukkah of Data (2022). https://hanukkah.bluebird.sh https://hanukkah.bluebird.sh

febeling 3y ago

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.

robinLord 3y ago
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

brightball 3y ago

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-2023 https://blog.carolina.codes/p/call-for-speakers-is-now-open-...

robinLord 3y ago
Thanks very much! I'll definitely have a look!

smackeyacky 3y ago

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

anthomtb 3y ago
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?

NickSingh 3y ago

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

bmau5 3y ago
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 :)

CalRobert 3y ago

If you want to go pro you could head to Vegas and compete in Schemaverse at DEFCON. https://news.ycombinator.com/item?id=29375911 https://news.ycombinator.com/item?id=29375911

ogig 3y ago

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!

robinLord 3y ago
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

natemcintosh 3y ago

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 ..."

robinLord 3y ago
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 :-)

inportb 3y ago

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

dylan604 3y ago
Just made a similar comment myself
- robinLord 3y ago
  Thanks for the feedback! Will have a look!

iideamanai 3y ago

This is amazing!! Full support

robinLord 3y ago
Thanks so much!

dylan604 3y ago

"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

inportb 3y ago
Agreed. A good error message could help the user make it work.
robinLord 3y ago
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!
- dylan604 3y ago
  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!

mmh0000 3y ago

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.

cldellow 3y ago
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.

kroltan 3y ago

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)

robinLord 3y ago
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
- kroltan 3y ago
  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
  - robinLord 3y ago
    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! :-)

thebiglebrewski 3y ago

This is pretty awesome, nice job!

user3939382 3y ago

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.

weaksauce 3y ago
i like having a little area where i squirrel away notes on things like that

ericzawo 3y ago

Wow, I love this.

grogenaut 3y ago

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

counttheforks 3y ago
Should've kept your laptop on mute
- grogenaut 3y ago
  This was on the phone which is usually full muted but I had a servicefolk coming today
  - counttheforks 3y ago
    There's a different media volume and call volume.

8n4vidtmkvmk 3y ago

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

robinLord 3y ago
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!
- 8n4vidtmkvmk 3y ago
  Showing the schemas as part of the question would probably be a good idea then.

nodakai 3y ago

Chapter 16: 'Recovered' vs 'Returned'

Hackbraten 3y ago

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.

robinLord 3y ago
Thank you! Appreciate that :-) Really good point, I should have caught that! Adding it to the list to update

weaksauce 3y ago

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.

robinLord 3y ago
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

flazawtz 3y ago

Looks good!

wdiamond 3y ago

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.

fredeerock 3y ago

Fun!

spprashant 3y ago

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.

trollmasterd 3y ago
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!
- robinLord 3y ago
  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! :-)

haruka_ff 3y ago

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.

simlan 3y ago

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 ?

robinLord 3y ago
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 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)

throwaway81523 3y ago

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.

robinLord 3y ago

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!

robinLord 3y ago
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!

LegitShady 3y ago

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

robinLord 3y ago
Thanks! Adding to the list of updates

k_roy 3y ago

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.

robinLord 3y ago
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

reraos 3y ago

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

robinLord 3y ago
Thank you! I'm pushing a fix for this shortly!

arisAlexis 3y ago

Isn't this so much easier with LLMs now ?

robinLord 3y ago
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=V17_ex1Vnb1YHTsT_Z8WNQ&s=19 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

bni 3y ago

The fonts are too small.

hddqsb 3y ago

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.

a_lesanka 3y ago

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

deleted 3y ago

[deleted]