4 ms·
The SQL question (mentioned in the thread) was: "It was very benign and was probably the most practical question I asked him! Let's say you want to randomly
by wenc 1y ago
The SQL question (mentioned in the thread) was:
"It was very benign and was probably the most practical question I asked him! Let's say you want to randomly sample 1,000 users out of a table with a user_id column, e.g. so you can get a list of IDs to send a customer feedback form to. How would you do that?"
To me, that's not a dumb question. I mean, there is a simple answer but there are also complicated answers (especially if your table size is large, and performance matters):
1. SELECT user_id FROM table LIMIT 1000 -- this gives unordered results, but is not truly random (it's dependent on database retrieval patterns)
2. SELECT user_id FROM table ORDER BY RANDOM() LIMIT 1000 -- this is uniform random, but it's expensive for big tables.
3. SELECT user_id USING SAMPLE reservoir(1000 ROWS) -- this is a fast and memory-efficient streaming method, i.e. Reservoir Sampling [1] in which n is treated as large and unknown (DuckDB supports this sampling method and others: https://duckdb.org/docs/stable/sql/samples.html https://duckdb.org/docs/stable/sql/samples.html) Reservoir sampling (https://en.wikipedia.org/wiki/Reservoir_sampling https://en.wikipedia.org/wiki/Reservoir_sampling) was a hot topic among data scientists on Quora circa 2011.
Sometimes simple questions have deep implications.
- krackers 1y agoCould it be construed as a dumb question because the details depend on the specific engine you're using? If you have TABLESAMPLE, then the question is trivial. The question doesn't really test anything general other than "are you aware that your engine probably already has a feature for this"
- wenc 1y agoI don’t think that in itself is dumb. It’s an application question that tests knowledge on sampling and doing it within the constraints of a database language and performance envelope of a large data store. The spirit of the question isn’t about the syntax but knowing the trade offs when applying sampling on a database.
- bravesoul2 1y agoI like the question. Not sure how id do it. As a one off of the table is not too big id get all the IDs then write a program to pick 1000 at random. I assume randomness is more important than expense but that can be a clarifying question to the interviewer. If this query had to be done regularly for A B testing or whatever I might suggest a salted hash. Given an id you hash and do a Bitcoin style "is it lower than X". Figure out X based on total user count and desired sample. This way you can tell if a user is in the set without looking at other records. You can also handle the user count growing or shrinking. But the trade off is rough numbers over exact numbers.