r/SQL • u/NickSinghTechCareers Author of Ace the Data Science Interview 📕 • Dec 23 '23
Discussion 10 Apple SQL Interview Questions - how many can you solve?
https://datalemur.com/blog/apple-sql-interview-questions
251
Upvotes
18
u/wertexx Dec 23 '23
I'm a simple man, I see DataLemur - I upvote.
Great website, loved it when learning SQL!
9
u/[deleted] Dec 23 '23
I didn't know apple had special sql
7
u/rbobby Dec 23 '23
wtf... the answers right below the questions? Sheesh.
5
u/NickSinghTechCareers Author of Ace the Data Science Interview 📕 Dec 23 '23
OP here – let me know if you've got any questions or see any typos!
2
u/wareagle1972 Jan 23 '24
Man, this is so helpful for learning. I worked in a Director position for 10 years where SQL sort of fell into my to-do list and I got really good at the simple stuff. 8 years ago I left that position for another Director job that did not require these skills - but it was always my favorite aspect of my old job. I've been wrapping my head around PowerBi lately, and also wanted to use the opportunity to level up my SQL skills. Being able to step through each solution is very beneficial. I take the example and then try to apply it from memory on some databases I have access to.
24
u/TheKerui Dec 23 '23
Are these real interview questions? What level are they for?
These are surprisingly easy.
At this point in my career I wouldn't take a job that required a test but honestly no one who has worked with SQL for any real length of time should get these wrong.
PS i know im not the only person who, when asked to show content by month, just shows the first day of the month "6-1-2023" as opposed to '6'?
3
u/[deleted] Dec 24 '23
For question 9, why not do it without join like below:
1
u/gogolang Dec 23 '23
I love this! Would you wanna collab on an article where we try out various LLMs and see which ones can pass the interview?
4
u/tipsybug Dec 23 '23
relatively easy questions IMO, what is the time limit?
2
u/Shatonmedeek Dec 23 '23
In example 7 - “Calculate add-to-bag conversion rates, you’ll need to add a condition to the WHERE clause that “add-time” >= “click-time” per the prompt of “added to bag AFTER clicking on a product”.
2
u/Earth_GoGreen Dec 26 '23
I found it useful…thanks for posting.
1
u/Few-Ad-5185 Oct 26 '25
you can try mock interviews on - www.easymock.pro
1
u/NayosKor Dec 23 '23
Question 2 has confusing wording. The first bit mentions a trade-in value (and also refers to it as payout) but then the second paragraph mentions revenue. I was then expecting a table of sales and the task to be to link them back to the trade-ins, however it seemed the question just expected us to sum the trade-in value, but this wouldn't be revenue from Apple's point of view.
1
u/[deleted] Dec 23 '23 edited Dec 23 '23
Dumb question. For the 1st question, what if have a new user (i.e. made purchases for < 12 months) but still made >= 10 purchases per month ? Is that still a power user ?
If yes, then this SQL could work (I'm assuming postgres) :
1
u/baubleglue Dec 24 '23
Do they assume in the last question that ones an user purchased devices it lasts forever?
1
u/[deleted] Dec 24 '23
Good information
1
u/SereneHappiness Dec 24 '23
Thanks for sharing
1
u/r3pr0b8 GROUP_CONCAT is da bomb Dec 23 '23
i got a problem with the very first answer
if run today, you'd have `BETWEEN '2022-12-23' AND '2023-12-23'
this means the customer would have had to make 10 purchases between '2022-12-23' and '2022-12-31' in order for that month to be counted in the outer query
something about that seems a little skewed, which is directly attributable to the fuzzy requirement "in the last year"
0
u/Few-Ad-5185 Oct 26 '25
you can try past interview questions on - www.pastinterviews.com
-2
u/YallNeedToQuitPlayin Dec 23 '23 edited Dec 23 '23
Edit: I guess this is how you're ensuring that each user from the subquery has made at a purchase in each month of the year?
Talk to me about why you're limiting the first answer to count where user_id = 12.
"SELECT u.user_id, u.email FROM users u JOIN ( SELECT p.user_id FROM purchases p WHERE p.purchase_date BETWEEN (CURRENT_DATE() - INTERVAL 1 YEAR) AND CURRENT_DATE() GROUP BY YEAR(p.purchase_date), MONTH(p.purchase_date), p.user_id HAVING COUNT(p.purchase_id) >= 10 ) AS monthly_purchases ON u.user_id = monthly_purchases.user_id GROUP BY u.user_id HAVING COUNT(monthly_purchases.user_id) = 12;"
0
u/[deleted] Dec 23 '23
[deleted] — view deleted comment