11 ms·
SQL for data scientists in 100 queries
- andrscyv 3y agoWhy bother, just use ChatGPT
- deleted 3y ago[deleted]
- hobs 3y agoDon't forget the venerable SQLZoo - I have referred a zillion people to it over the years. https://www.sqlzoo.net/wiki/SQL_Tutorial https://www.sqlzoo.net/wiki/SQL_Tutorial Edit: also an inaccuracy that's minor but can bite you if you're not careful - they mention temporary tables are in memory not on disk - that's not true in almost all sql databases, they are just connection specific eg they don't persist after you disconnect. Some databases are optimized for a temp table to be a throwaway, but that can be a good or bad thing depending on the use case.
- vavooom 3y agoI am also a fan of Mode's SQL Tutorial: https://mode.com/sql-tutorial https://mode.com/sql-tutorial
- wanderingmind 3y agoWhere is the datasources to practice them?
- hobs 3y agoEach query has a built in data source/test but dbfiddle and friends are great if you want to do some setup.
- deleted 3y ago[deleted]
- vavooom 3y agoLearning outcomes: * Explain the difference between a database and a database manager. * Write SQL to select, filter, sort, group, and aggregate data. * Define tables and insert, update, and delete records. * Describe different types of join and write queries that use them to combine data. * Use windowing functions to operate on adjacent rows. * Explain what transactions are and write queries that roll back when constraints are violated. * Explain what triggers are and write SQL to create them. * Manipulate JSON data using SQL. * Interact with a database using Python directly, from a Jupyter notebook, and via an ORM.
- LegitShady 3y agothese does seem to be a fairly good curriculum for an introduction to SQL
- bigger_cheese 3y agoOne thing that I have noticed confuses a lot of people is "timeseries joins" (I don't know the real term for this) I'm talking about where there is no "one to one" match between keys in the two tables. I'm a non software type of engineer in my world a lot of tables are structured as timeseries data (such as readings from a device or instrument) which uses timestamp as a key. Then we have other tables which log event or batch data (such as an alarm start and end time, or Machine start/machine stop etc). So a lot of queries end up being of the form Select A.AlarmId, B.Reading, B.Timestamp from Alarms A, Readings B where A.StartTime >= B.Timestamp and A.EndTime < B.Timestamp A lot of people seem to have problems grasping these kinds of joins.
- mr_toad 3y agoYou mean slowly changing dimensions? It’s not something there is much literature on, especially for outer joins.
- davery22 3y agoCool use case. They're just called "non equi-joins" - because the join condition is an inequality. In general a join produces a row in the output table for each (left, right) pair of rows from the input tables that satisfies the join condition. It's just so common for joins to use a simple equality condition, where one or both sides is a unique id for its table, and people don't as often encounter joins where one input row can inform multiple output rows.
- petalmind 3y ago> left outer join > A join that is guaranteed to keep all rows from the first (left) table. Columns from the right table are filled with actual values if available or with null otherwise. This wording only works for identity equality join condition. It creates misleading mental model of left joins, and unfortunately is very common.
- erehweb 3y agoCan you elaborate with a gotcha example?
- hobs 3y agoI assume they mean that row multiplication can occur but otherwise not sure.
- nomilk 3y agoI'm not sure I understand, I think this definition still works for left outer joins on conditions other than identity equality, since joins on, say, inequalities or multiple conditions would still be "guaranteed to keep all rows from the first (left) table. Columns from the right table are filled with....".
- orlp 3y agoIt kind of implies that the left join process works by first taking the left table and then filling in the right table with a match if one exists, and otherwise null. That model obviously doesn't work because if there's more than one match as the matching left row is duplicated for each match. However I don't understand their point of this being a problem when you don't have a "identity equality join condition", since this can also occur for equality joins as long as you're not joining on a unique key.
- simonw 3y agoCan you clarify? Is the problem here that the "guaranteed to keep all rows from the left table" piece is accurate, but the section about the right table might not be a good mental model if the join condition is more complex than a simple equality?
- shubhamjain 3y agoShameless Plug: If anyone here wants to practice their SQL, they are welcome to try my Mac app: TextQuery [1]. I built it because I wanted to quickly import CSV datasets and run SQL queries on them. I don't think there could be a more fun way to learn SQL than to jump in and start analyzing thousands of public datasets. Sure, you can use CLI/Code as well, but GUI is often faster and easier. Currently, the app is in the beta period and free-to-use. When launched, you'll get to keep the latest beta version. [1]: https://textquery.app/ https://textquery.app/
- saadatq 3y agoThis is brilliant.
- xp84 3y agoSuper cool! Thanks for sharing.
- Atotalnoob 3y agoIsn’t this just a ui over SQLite?
- aeturnum 3y agoThere's clearly some non-SQLite functionality that seems really neat!
- Terretta 3y agoWhy do you need to harvest email addresses?
- friendlynokill 3y agoBeen meaning to learn SQL, so will check this out
- _kush 3y agoThis is a very interesting idea! Great work
- 7thaccount 3y ago
- deleted 3y ago[deleted]
- atseajournal 3y agoGlad to have learned about iif() from this!
- webdoodle 3y agoIt's really handy in conjunction with GROUP BY.
- deleted 3y ago[deleted]
- carabiner 3y agoAlso StrataScratch.com for leetcode for SQL. What really distinguishes an SQL master is working with queries hundreds of lines long, and query optimization. For example, you can often make queries faster by taking out joins and replacing them with window functions. It's hard to practice these techniques outside of a legit enterprise dataset with billions of rows (maybe a good startup idea).
- swasheck 3y agoone of the most performance-killing practices i’ve seen (outside of brutal orm-generated queries) is the sql hero who wants to do everything all at once in a single splat of sql. at a certain level of complexity, breaking things up into logical units and even forcing materialization (instead of hoping for some good intermediate materialization) can go a long way
- deleted 3y ago[deleted]
- deleted 3y ago[deleted]
- pama 3y agoI’ve found that chatGPT is excellent in helping with generating and testing SQL queries. This tutorial is likely helpful for its intended audience; I’d like to see a bit more discussion of query optimization, stored procedures, table design, and available options both in db and in bindings, but it’s hard to fit everything in one place and keep it clean, which is why LLMs will eventually win.
- cyral 3y agoI've found the same. Especially dealing with sorta complex JSON column migrations where the JSON structure needs to be changed. Can describe in plain english what I want to do and get a working query for it.
- nomilk 3y agoThe tutorial can be downloaded here: https://github.com/gvwilson/sql-tutorial/raw/main/sql-tutorial.zip https://github.com/gvwilson/sql-tutorial/raw/main/sql-tutori... (gives the penguins.db file necessary for the examples)
- dinkleberg 3y agoThanks for sharing this! Learn by example resources can be super helpful.
- nomilk 3y agoBefore flights with patchy/no wifi, I often download a long, single-page tutorial. This is perfect. Curious if anyone knows of any for other languages/tech (e.g. beyond SQL).
- adamiscool8 3y agoIf not familiar: https://learnxinyminutes.com/ https://learnxinyminutes.com/
- belter 3y agoFunny on that one, there is so much but not SQLite :-)
- Anon84 3y agoShameless Plug: - Not single page _per se_ but I have plenty of Jupyter Notebook based tutorials here: https://github.com/DataForScience/ https://github.com/DataForScience/ Self contained with slide decks and notebooks.
- blackhaj7 3y agoThese are great, thanks!
- beeburrt 3y agoZig docs are all one page: https://ziglang.org/documentation/master/ https://ziglang.org/documentation/master/
- nomilk 3y agoWow - gave me a goofy smile. Also cool 'Zig Zen': https://ziglang.org/documentation/master/#toc-Zen https://ziglang.org/documentation/master/#toc-Zen
- nbbaier 3y agoGot any other favorite tutorials you've used for this kind of flight?
- SilverBirch 3y agoThe queries are solid, but I really appreciate throwing in the worlds most confusing diagrams here and there. It keeps me alert trying to find where to even start with them. So to be clear, tables must have a name, and must have rows and the rows must have the same name as the table, yes?
- QAFred123 3y ago[flagged]
- dang 3y agoCould you please stop doing this?
- PheonixPharts 3y agoNot to detract from the article, but: Wow the meaning of the term "data scientist" has changed since the days of "sexiest job". From the article description: - Rachel has a master’s degree in cell biology and now works in a research hospital doing cell assays. - She learned a bit of R in an undergrad biostatistics course and has been through the Carpentries lesson on the Unix shell. - Rachel is thinking about becoming a data scientist and would like to understand how data is stored and managed. Data Scientists, back in the day, were largely people with both a fairly strong quantitative background and a strong software engineering background. The kind of people who could build a demo LSTM in an afternoon. Usually there was a bit of a trade-off between the quant/software aspects (really mathly people might be worse coders, really strong coders might need to freshen up on a few areas of mathematics), but generally they were fairly strong in each area. In many orgs it's been reduced to "over paid data analysts" but I wouldn't even hire "Rachel" for a role like that.
- carabiner 3y agoNo that's MLE. A DS rarely gets asked leetcode algos questions, an MLE would.
- PheonixPharts 3y agoYou must have worked at different places from me. Nearly every DS job I had (before wisely apparently) leaving that area had leetcode style algo questions during the interviews. Again, things have apparently changed.
- KRAKRISMOTT 3y agoDepends on the company, a research level MLE would be asked to derive loss functions and perform partial differentiation on pen and paper. You have to answer questions like what Kullbeck Leiber divergence is and how it can be utilized etc.
- defrost 3y agoWould that be a lesser known cousin of the better known Kullback–Leibler separation measure for distributions?
- AlbatrossAA 3y ago[dead]
- user3939382 3y agoI don't see how this is "for data scientists" it looks like a good summary of SQL in general.
- swasheck 3y agoagreed. and sqlite specifically. i’d really like to see an authoritative resource that shows me how to use sql and relational algebra to do some basic and intermediate data analysis. i can find like 8 different ways to calculate skew and kurtosis, but is there a trusted resource that can show me how to do it? what other interesting data analysis can i do using sql?
- OJFord 3y agoAccording to the article itself it's explicitly not anyway - it's for instructors (of data scientists potentially, sure).
- BangbokAsin 3y ago[flagged]
- ddgflorida 3y agoNice! It could be better if the queries were interactive.
- blagie 3y agoThat's what would change this from a lousy resource to a brilliant one. Little SQLer ("Little Squealer")
- selimthegrim 3y agoI’m sure MIT Press is working the phone lines right now
- worik 3y agoWhat is a data scientist, if they are not a statistician? I am a grumpy old man, fed up with newspeak
- blitzar 3y agoWhats this newspeak? Why cant people just talk properly like we did before the war? Not that silly little war, the actual war, WW1. I am a grumpy old man, fed up with all this newfangled nonsense.
- collyw 3y agoConstantly evolving language and job titles. I consider myself a programmer. I market myself as a software engineer in LinkedIn. I am a generalist who uses mostly python / Django. I like SQL and feel that the database should be leveraged to do the heavy lifting. But software engineers plug APIs together these days.
- kakaz 3y ago[dead]
- Linda231 3y ago[dead]
- bilsbie 3y agoDoes anyone remember some kind of adventure or mystery novel that you progress through by solving sql challenges? I remember seeing it once and I can never find it now.
- proamdev123 3y agoThere was one about space exploration based on Postgres, but I don’t remember the name.
- Tomte 3y agoMastery with SQL. A paid course that‘s excellent.
- simonw 3y agoSounds like https://mystery.knightlab.com/ https://mystery.knightlab.com/ SQL Murder Mystery by Northwestern University Knight Lab.
- fbdab103 3y agoSelect Star SQL? https://selectstarsql.com/ https://selectstarsql.com/ It opens up with analyzing death row inmates, so significantly more real than classifying flowers.
- orthoxerox 3y agoIt's a different area of SWE, but https://deadlockempire.github.io/ https://deadlockempire.github.io/ is great except the final level.
- throwaway99989 3y agoReally excellent concise SQL guide, and great teaching by example. At most universities in the US this content gets taught over an entire semester (and probably shouldn't be). This guide is complete enough for 99% of industry SWE jobs outside of database optimization. It's a great service when someone takes the time to document knowledge on a single page with quality examples, and trust the reader to follow along. Reminds me of the Rudin analysis book.
- flaie 3y agoExactly this, bookmarked.
- 7thaccount 3y agoThere is a W3Schools online tutorial that is excellent. I had all the new engineers do it before they started running queries at work. This guide is great too.
- rqtwteye 3y agoItem 70 doesn't look right.
- deleted 3y ago[deleted]
- mvdtnz 3y agoA lot of these queries are only valid on SQLite (or maybe not "only" but certainly not generally available across DBMS's), including cases where the author doesn't point this out. For one example filtering on aggregates is not supported by MySQL, MS SQL, Oracle or most other DBMS's.
- Izkata 3y ago> For one example filtering on aggregates is not supported by MySQL, MS SQL, Oracle or most other DBMS's. HAVING is one of the standard clauses, I use it on mysql all the time and a quick search shows it exists for the others.
- mvdtnz 3y agoI'm not talking about HAVING, I'm talking about item #25: filter aggregate inputs. Queries like select sex, round( avg(body_mass_g) filter (where body_mass_g < 4000.0), 1 ) as average_mass_g from penguins group by sex;
- aussieguy1234 3y agoAlso, remember ChatGPT can help you write queries like a data scientist, without actually being one. Just tell it what you want in plain english. I've used this to get all sorts of useful metrics like conversion rates, messaging click through rates, etc...
- jkrubin 3y agoConversely, it’s very helpful at dissecting a 3k line insanity query and explaining it.
- aussieguy1234 3y agoYes. It'd be interesting to see what it makes of some of those ugly ORM generated queries.
- mdekkers 3y ago> 3k line insanity query *laughs in PTSD*
- collyw 3y agoI have always found SQL to be one of the easiest languages to read, despite not being the easiest to write. Though I have never had to read 3k of SQL, maybe a page or two
- jkrubin 3y agoI totally agree but some of these are tsql which is less than easy to read imo.
- blitzar 3y ago> ChatGPT can help you write queries like a data scientist Badly then? Personally I want my SQL queries to be written like a database professional.
- StarlaAtNight 3y agoDon’t have time to do it myself, but might be good to redo this guide but with DuckDB (more likely used by DS’es nowadays than SQLite)
- thehours 3y ago> full outer join (also called cross join) constructs their cross product Full outer joins and cross joins are different types of joins. A cross join returns the Cartesian product of both tables, while a full outer join is like a combination of a left and right join. Better explanation here: https://stackoverflow.com/questions/3228871/sql-server-what-is-the-difference-between-cross-join-and-full-outer-join https://stackoverflow.com/questions/3228871/sql-server-what-...
- collyw 3y agoCould have done with a visual representation of the starting tables and resulting tables to be a good description.It was 20 years ago that I was learning about cartesian joins. When I do SQL I visualize an excel like structure in my head.
- deleted 3y ago[deleted]
- Exuma 3y agoFantastic summary, saving for just general tips when trying to show people SQL
- deleted 3y ago[deleted]
- rgovostes 3y agoThis is from Greg Wilson (https://third-bit.com https://third-bit.com), co-editor of "The Architecture of Open Source Applications" and many other superb references. Edit: He wrote about this project here: https://third-bit.com/2024/02/03/sql-tutorial/ https://third-bit.com/2024/02/03/sql-tutorial/
- _diyar 3y agoAs mentioned by others, the author Dr. Greg Wilson has written/compiled many books/tutorials which I can recommend. I would especially laud Software Design by Example [1][2], The Architecture of Open Source Applications [3] and Teaching Tech Together [4]. [1] https://third-bit.com/sdxpy/ https://third-bit.com/sdxpy/ (python version) [2] https://third-bit.com/sdxjs/ https://third-bit.com/sdxjs/ (js version) [3] https://aosabook.org/ https://aosabook.org/ [4] http://teachtogether.tech/ http://teachtogether.tech/
- dragonelite 3y agoI would expect someone that has the title of data scientists to at least be a master in the universal data query language SQL. But im so used to ORM these days anything more complex then a sql join is already going over my head if i didn't do a sql refresher. As far as i have skimmed the article it seems like a very good refresher for even a SWE. I will definitely put this tutorial on my todo list.
- AmazingTurtle 3y agoWatch out, you're only referring to MALE and FEMALE sex, that might trigger a few people here
- iNic 3y agoSorry for feeding the trolls, but: I feel like I observe way more people complaining about people getting triggered by M/F sex than I actually see people complain about it. That is why this person had to make up the triggered person, because you will not find them in this comment section.
- zubairq 3y agoSome useful SQL queries for me in this list of 100 queries, thanks!
- mulmen 3y ago> do not expect novices with no prior SQL experience to be able to learn from them Please suggest this entry level thing. SQL is enduring because it is logical and understandable. But there is still an initial vertical learning curve. I am trying to teach a friend of mine SQL but I’m not sure how to construct a lesson plan. Is there a canonical SQL 101?
- simonhaven 3y agoLots of great and very clear examples here. As someone who's been writing SQL for over 30 years, I wish I'd had something like this at the start. Obviously there's a lot more to learn about many of these areas to really make the most of them, but this is a really good launchpad.
- franole 3y agoThe diagrams at "check your understanding" sections are great.
- deleted 3y ago[deleted]
- pm2222 3y agoAt least on 3.45.1 double-quoted as data no longer works. sqlite> delete from work where person = "tae"; Parse error: no such column: tae delete from work where person = "tae"; error here ---^ sqlite> delete from work where person = 'tae'; sqlite>
- oars 3y agoVery useful guide to SQL, thanks!