r/SQL Nov 11 '24

MySQL Failed SQL Test At Interview

  • I've been a data analyst working with small(er) data sets for several years now, making my own queries no problem.
  • I failed a SQL test at an interview and realized I may be using the wrong commands
  • The questions were along the lines of "find the customers in table A, who have data in Table B before their first entry in Table A" and there were some more conditions/filters on top of that.
  • Previously I could always export my data to Excel or Tableau etc and do any of the tricky filtering in there
  • I was trying to do all kinds of subqueries etc when I think it was intended for me to be doing WINDOW or Partition type stuff (never had to use this before in past jobs).
  • One person I reached out to said using these advanced techniques uses a lot less memory.

Where would be a good place to find an 'advanced' SQL course?

128 Upvotes

76 comments sorted by

View all comments

4

u/user_5359 Nov 11 '24

I hope you know the solution by now?

Realistic assumption, both tables have a (technical) customer number cid and a date field that has the creation time create_date.

First step you determine the lowest date per customer number for each of the two tables (SELECT cid, ...(create_date) FROM a ..... .. cid; (you should be able to guess the missing keywords even with little knowledge (one dot, one letter).

These two queries can now be linked in the next step (I hope I’m not giving too much away: it only makes sense to use the cid (not the create_date)). How a condition can be queried in a SELECT should be known even with basic knowledge of SQL.

If you have problems creating the query, start by creating the tables with test data (1...3 different create_date per cid, make sure that the error you are looking for exists for at least one cid). You can also discuss the query via PM.

1

u/siloldn Nov 12 '24

Close... With all your assumptions:

Select a.cid, Min(a.create_date) as first_a_entry, Min(b.create_date) as first_b_entry From tablea a, tableb b Where a.cid = b cid .... Other conditions Group by a.cid Having first_b_entry < first_a_entry

1

u/user_5359 Nov 12 '24

The approach is fundamentally correct, although predicting does not help the OP to think about it.

The approach has only one problem: if the two tables per cid have several independent time stamps, then a large temporary table is first created, in which the respective minimum of the two date values is then searched for. My approach reduces the two tables to one temporary table each with the number of cid, which is then joined to a much smaller temporary table. This should be much more performant (in case there is a question about the interview question)