[HN Gopher] Show HN: I made a SQL game to help people learn / ch...
___________________________________________________________________
Show HN: I made a SQL game to help people learn / challenge their
skills
Author : robinLord
Score : 387 points
Date : 2023-04-22 10:40 UTC (12 hours ago)
(HTM) web link (lost-at-sql.therobinlord.com)
(TXT) w3m dump (lost-at-sql.therobinlord.com)
| haruka_ff wrote:
| 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.
| Hackbraten wrote:
| 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.
| brightball wrote:
| 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-...
| butz wrote:
| 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 wrote:
| Ya, for example `select * from crew` where I typed that all
| out. At the end crew would try to autocomplete to staff_id
| justinclift wrote:
| 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
| justinclift wrote:
| Hmm, are you open to other reports of weirdness? I'm noticing
| typos and similar in tutorial instructions.
| taway789aaa6 wrote:
| 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...
| simlan wrote:
| 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 ?
| wdiamond wrote:
| 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.
| leetrout wrote:
| You may also find the SQL Murder Mystery from Knight Lab
| interesting.
|
| https://mystery.knightlab.com/
| saulpw wrote:
| A bit less introductory, but also Hanukkah of Data (2022).
|
| https://hanukkah.bluebird.sh
| kroltan wrote:
| 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)
| natemcintosh wrote:
| 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 ... "
| fredeerock wrote:
| Fun!
| smackeyacky wrote:
| I want to hate this. Trivialising serious work makes all our
| luves worse.
| anthomtb wrote:
| 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?
| iideamanai wrote:
| This is amazing!! Full support
| ericzawo wrote:
| Wow, I love this.
| nhinck wrote:
| Looks cool, the CSS is a bit janky with multiple scrollbars at
| least on Firefox.
| ajsnigrutin wrote:
| 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 :)
| spprashant wrote:
| 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 wrote:
| 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!
| nodakai wrote:
| Chapter 16: 'Recovered' vs 'Returned'
| flazawtz wrote:
| Looks good!
| lbj wrote:
| 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?
| weaksauce wrote:
| 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.
| inportb wrote:
| Interesting idea. Didn't get very far in Firefox private browsing
| mode... https://imgur.com/LZBMPff
| dylan604 wrote:
| Just made a similar comment myself
| dylan604 wrote:
| "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 wrote:
| Agreed. A good error message could help the user make it work.
| NickSingh wrote:
| This is super cool. Gonna add it to my running list of SQL games:
| https://datalemur.com/blog/games-to-learn-sql
| user3939382 wrote:
| 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 wrote:
| i like having a little area where i squirrel away notes on
| things like that
| ch33zer wrote:
| 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?
| eeiaeaa wrote:
| 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.
| 8n4vidtmkvmk wrote:
| 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.
| autoexec wrote:
| > 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.
| justinclift wrote:
| > 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.
| faxmeyourcode wrote:
| duckdb-wasm could also be an option
|
| https://duckdb.org/2021/10/29/duckdb-wasm.html
| victor106 wrote:
| > SQL is an undervalued skill
|
| Wondering if chatgpt would make sql irrelevant sometime in the
| future?
| incone123 wrote:
| 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.
| cmehdy wrote:
| 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).
| cellularmitosis wrote:
| 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.
| sysstemlord wrote:
| 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.
| throwaway049 wrote:
| 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.
| tragomaskhalos wrote:
| 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.
| tetha wrote:
| 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.
| skeeter2020 wrote:
| 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.
| tetha wrote:
| > 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.
| charrondev wrote:
| 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.
| vajrabum wrote:
| 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.
| winrid wrote:
| 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.
| matheusmoreira wrote:
| > 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.
| fn-mote wrote:
| Yeah, um... exactly. It's baked into your salary, though.
| doesnt_know wrote:
| 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.
| throwaway81523 wrote:
| 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.
| duckqlz wrote:
| 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.
| 8n4vidtmkvmk wrote:
| How do I see the table schema? It doesn't want to accept "show
| table" queries
| CalRobert wrote:
| If you want to go pro you could head to Vegas and compete in
| Schemaverse at DEFCON.
|
| https://news.ycombinator.com/item?id=29375911
| mmh0000 wrote:
| 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 wrote:
| 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.
| thebiglebrewski wrote:
| This is pretty awesome, nice job!
| febeling wrote:
| 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.
| grogenaut wrote:
| Nice just woke my partner up who has a cold with the music
| counttheforks wrote:
| Should've kept your laptop on mute
| grogenaut wrote:
| This was on the phone which is usually full muted but I had a
| servicefolk coming today
| ogig wrote:
| 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!
___________________________________________________________________
(page generated 2023-04-22 23:00 UTC)