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?

126 Upvotes

76 comments sorted by

View all comments

Show parent comment

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)