4 ms·

Seems to me to be a solid test, though I might include a small amount of sample data to nudge them in the direction of some of the potential issues (empty depar

Seems to me to be a solid test, though I might include a small amount of sample data to nudge them in the direction of some of the potential issues (empty departments and so on) - maybe I'm just kind like that. ;-) In interviews I've always been amazed how few people who claim to know SQL can use GROUP BY, HAVING and aggregate functions (or depending on the question self joins or sub queries that will allow them to achieve some of the same things). My normal question is to present them a table with a level of duplication ask them to write something in SQL that identifies the duplications and something that removes them which covers much of the same syntax.

jcampbell1 13y ago
Removing duplicates is dependent on the underlying database because the behavior of deleting while selecting varies. I know MySQL doesn't allow it in most cases, so you need a temporary table. Identifying them is a fine question.
- VLM 13y ago
  You can get a list of dupes to eat without a temp table with something like: select min(id), count(*) as dupecount from yer_table group by some_hash_identifier_or_whatever having dupecount > 1 And then just iterate thru delete from yer_table where the id = the min(id) as fetched above. Or maybe your business logic is to keep the oldest record and zap the newest. Or based on some column data rather than simply age. Now the really interesting discussion is how often this happens (like once-off, or every 10 seconds, or), and how scalable you need it to be. Are you talking about 100 records or 100e6 records. Also literal duplicates as in "two" works pretty well but not so good if there's 50 duplicates and you need to delete 49 of them. Of course for 49 duplicates you could select the identifier hash and the lone lowest ID and delete all entries with the same identifier hash where the id isn't the lowest id for that hash...
- Tyrannosaurs 13y ago
  I give a basic single table schema with sample data so they have all the information they need to work with.

jcampbell1 13y ago

Removing duplicates is dependent on the underlying database because the behavior of deleting while selecting varies. I know MySQL doesn't allow it in most cases, so you need a temporary table. Identifying them is a fine question.

VLM 13y ago
You can get a list of dupes to eat without a temp table with something like: select min(id), count(*) as dupecount from yer_table group by some_hash_identifier_or_whatever having dupecount > 1 And then just iterate thru delete from yer_table where the id = the min(id) as fetched above. Or maybe your business logic is to keep the oldest record and zap the newest. Or based on some column data rather than simply age. Now the really interesting discussion is how often this happens (like once-off, or every 10 seconds, or), and how scalable you need it to be. Are you talking about 100 records or 100e6 records. Also literal duplicates as in "two" works pretty well but not so good if there's 50 duplicates and you need to delete 49 of them. Of course for 49 duplicates you could select the identifier hash and the lone lowest ID and delete all entries with the same identifier hash where the id isn't the lowest id for that hash...
Tyrannosaurs 13y ago
I give a basic single table schema with sample data so they have all the information they need to work with.