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.
However, students will learn not to trust the spec, so I suppose that's a valuable lesson.
Wondering if chatgpt would make sql irrelevant sometime in the future?
Make irrelevant? Probably not. Make more convenient? Probably.
If you're doing more involved stuff then writing a good prompt will probably need as much analytical and domain knowledge as writing SQL.
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
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.
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.
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.
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.
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.
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.
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.
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.
Why can your employer bother you outside your work hours? You should charge a lot of money for this service.
That’s my experience so far anyway.
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.
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?