4 ms·

Great article. I can't help but feel like SQL is a poor choice for some of this stuff, though. More often than not, I find it much easier to pull the raw data i

Great article. I can't help but feel like SQL is a poor choice for some of this stuff, though. More often than not, I find it much easier to pull the raw data into memory, and use a higher level language to do these sorts of queries. I am all for knowing the intricacies of SQL, as the cost for not can be very high, but I'm curious for your opinion here.

tfehring 6y ago
I work in R regularly and do most of my more complex data manipulation in R whenever I can, but most of these examples are simple enough that I think it makes sense to keep them in SQL. The only ones that struck me as really weird to do in SQL were the cumulative cash flows and the "histograms"/binning one, though the ones requiring window functions might also be slightly easier in R than in SQL. Also, a lot of these would need to be done in SQL in practice since the data wouldn't fit in memory. Any solution that requires loading the table [login (user_id, timestamp)] into memory probably won't scale very well!
nerdbaggy 6y ago
I use to think that too. This is a good talk about how smart SQL query engines are. It is an hour though https://youtu.be/wTPGW1PNy_Y https://youtu.be/wTPGW1PNy_Y
bdcravens 6y ago
For small datasets, pretty much any approach will work. Once you hit hundreds of millions of records (which isn't even that big of a dataset), SQL still performs well on pretty modest hardware.
- yen223 6y ago
  What do you mean by SQL? In my experience, once you're hitting hundreds of millions of records, implementation details of your database engine will start to matter. A database designed for transactional workloads like Postgres will start to choke on aggregate and window functions, often taking minutes to run instead of milliseconds. A columnar database like Redshift (which exposes a SQL interface) will breeze through it without a sweat.
  - bdcravens 6y ago
    Of course - I was comparing SQL to loading all the data in memory and then using one's programming language of choice to do the hard work. Even a poor schema in a row-based database will outperform what I described after a certain number of records.
- jmiserez 6y ago
  The first example "month over month" will never result in a large dataset if you just do count(distinct user_id) ... group by month order by month. There's only max 12 values per year. That query is much simpler, safer, faster, no self-join or windowing necessary, and you can properly handle missing months in your dataset in your higher-level language (which the provided solution doesn't do BTW). And you're still getting the performance boost from the DB indexes for grouping and sorting.
thomzi12 6y ago
Good q! +1 to the other replies on this comment. I have two points worth mentioning: (1) The flavor of SQL I use at work supports macros, which are functions that can take in parameters like a function in R/Python might. So, the SQL is "turbo-charged" in that sense and some of the value-added of switching over to Python/R is diminished. (Big Query has UDFs, which seem similar: https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions https://cloud.google.com/bigquery/docs/reference/standard-sq...) (2) Like I mentioned in the doc, I personally use the SQL in these practice problems for ETLs on dashboards showing trends. AFAIK, much easier/efficient to write metrics for daily ETLs in SQL than R or Python, especially if these are top-line metrics like MAU.
karatestomp 6y ago
That's fine for tiny amounts of data, but it's also how you end up with (for example) Ruby taking four minutes to do what a carefully-crafted SQL query can do in four seconds (yes, this stuff happens a lot in the wild)
- vajrabum 6y ago
  Yah, somebody handed me a ruby script that looped over a query and was causing a whole bunch of other processes to fail because the result wasn't available in a timely way. My boss was impressed by my having reduced the run time from 22 minutes to 5 by pushing that join into the database.
tragomaskhalos 6y ago
These are interview questions first and foremost, so always going to be in an artificial context. But for real-world work I would use SQL plus the relevant amount of aggregation/joining to pull rows into memory, then do the remaining manipulation in the higher-level lang. "The relevant amount of aggregation/joining" is obviously a moveable judgement call based on experience, but one thing I have found is that the more complex yr SQL becomes the easier it is for cardinality errors to creep in, and of course in general you can't easily see the intermediate results, so dropping into a high-level language can also be safer and less error-prone.
eyelidlessness 6y ago
Besides the many responses about data size, the premise of your question is baffling to me. This is exactly the kind of stuff SQL is for, it's designed specifically for doing this kind of work. Even on a smaller dataset, the "higher level" languages you speak of will certainly be able to produce the same results, but their implementations will almost certainly be more complex, with more points of failure, a great deal less flexible (what if the dataset grows?), and a whole lot more verbose... and all you get for all of those downsides is a more familiar language.

tfehring 6y ago

I work in R regularly and do most of my more complex data manipulation in R whenever I can, but most of these examples are simple enough that I think it makes sense to keep them in SQL. The only ones that struck me as really weird to do in SQL were the cumulative cash flows and the "histograms"/binning one, though the ones requiring window functions might also be slightly easier in R than in SQL. Also, a lot of these would need to be done in SQL in practice since the data wouldn't fit in memory. Any solution that requires loading the table [login (user_id, timestamp)] into memory probably won't scale very well!

nerdbaggy 6y ago

I use to think that too. This is a good talk about how smart SQL query engines are. It is an hour though https://youtu.be/wTPGW1PNy_Y https://youtu.be/wTPGW1PNy_Y

bdcravens 6y ago

For small datasets, pretty much any approach will work. Once you hit hundreds of millions of records (which isn't even that big of a dataset), SQL still performs well on pretty modest hardware.

yen223 6y ago
What do you mean by SQL? In my experience, once you're hitting hundreds of millions of records, implementation details of your database engine will start to matter. A database designed for transactional workloads like Postgres will start to choke on aggregate and window functions, often taking minutes to run instead of milliseconds. A columnar database like Redshift (which exposes a SQL interface) will breeze through it without a sweat.
- bdcravens 6y ago
  Of course - I was comparing SQL to loading all the data in memory and then using one's programming language of choice to do the hard work. Even a poor schema in a row-based database will outperform what I described after a certain number of records.
jmiserez 6y ago
The first example "month over month" will never result in a large dataset if you just do count(distinct user_id) ... group by month order by month. There's only max 12 values per year. That query is much simpler, safer, faster, no self-join or windowing necessary, and you can properly handle missing months in your dataset in your higher-level language (which the provided solution doesn't do BTW). And you're still getting the performance boost from the DB indexes for grouping and sorting.

thomzi12 6y ago

Good q! +1 to the other replies on this comment. I have two points worth mentioning: (1) The flavor of SQL I use at work supports macros, which are functions that can take in parameters like a function in R/Python might. So, the SQL is "turbo-charged" in that sense and some of the value-added of switching over to Python/R is diminished. (Big Query has UDFs, which seem similar: https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions https://cloud.google.com/bigquery/docs/reference/standard-sq...) (2) Like I mentioned in the doc, I personally use the SQL in these practice problems for ETLs on dashboards showing trends. AFAIK, much easier/efficient to write metrics for daily ETLs in SQL than R or Python, especially if these are top-line metrics like MAU.

karatestomp 6y ago

That's fine for tiny amounts of data, but it's also how you end up with (for example) Ruby taking four minutes to do what a carefully-crafted SQL query can do in four seconds (yes, this stuff happens a lot in the wild)

vajrabum 6y ago
Yah, somebody handed me a ruby script that looped over a query and was causing a whole bunch of other processes to fail because the result wasn't available in a timely way. My boss was impressed by my having reduced the run time from 22 minutes to 5 by pushing that join into the database.

tragomaskhalos 6y ago

These are interview questions first and foremost, so always going to be in an artificial context. But for real-world work I would use SQL plus the relevant amount of aggregation/joining to pull rows into memory, then do the remaining manipulation in the higher-level lang. "The relevant amount of aggregation/joining" is obviously a moveable judgement call based on experience, but one thing I have found is that the more complex yr SQL becomes the easier it is for cardinality errors to creep in, and of course in general you can't easily see the intermediate results, so dropping into a high-level language can also be safer and less error-prone.

eyelidlessness 6y ago

Besides the many responses about data size, the premise of your question is baffling to me. This is exactly the kind of stuff SQL is for, it's designed specifically for doing this kind of work. Even on a smaller dataset, the "higher level" languages you speak of will certainly be able to produce the same results, but their implementations will almost certainly be more complex, with more points of failure, a great deal less flexible (what if the dataset grows?), and a whole lot more verbose... and all you get for all of those downsides is a more familiar language.