Top SQL Interview Q&A for Data Scientists

Calculating ratios is one of the most frequently asked SQL questions in data science interviews. In this video, I’ll go over the two most common ways to compute a ratio, and feature two examples to demonstrate solving the problems in real-time: how to calculate the premium subscription rate, and the immediate order from customers’ order data.
🟢Get all my free data science interview resources
www.emmading.com/get-all-my-f...
🟡 Product Case Interview Cheatsheet www.emmading.com/product-case...
🟠 Statistics Interview Cheatsheet www.emmading.com/statistics-i...
🟣 Behavioral Interview Cheatsheet www.emmading.com/behavioral-i...
🔵 Data Science Resume Checklist www.emmading.com/data-science...
✅ We work with Experienced Data Scientists to help them land their next dream jobs. Apply now: www.emmading.com/coaching
// Comment
Got any questions? Something to add?
Write a comment below to chat.
// Let's connect on LinkedIn:
/ emmading001
====================
Contents of this video:
====================
00:00 Introduction
00:38 Ratios
01:10 Calculating Ratios
01:39 Two Methods
02:07 Example: Subscription Rate
05:00 Example: Immediate Order
08:58 Outro

[@zijiaoli906]

Great summary! One minor correction is necessary I think for the last solution: in the window function of the 1st CTE, we should add pref_delivery_date to the order by clause as 1 user could have 2 orders with pre_delivery_date J and J+3 on order date J (first day) for example, without pref_delivery_date in the order by clause, it could randomly assign order with delivery date J+3 as row no 1 and thus not consider this as immediate order whereas it should have been !

[@rezarafieirad]

great video, please share some insights on how to answer Guesstimates

[@jasonsun7527]

We should be very careful when using AVG(CASE WHEN...), because it will count duplicate values for denominator and it could be a problem when total count has duplicates but the question doesn't want duplicates in the denominator. For example, we are dealing with order table (duplicate values for customer column) and the question is asking for the percentage of certain customers.

[@jasonsun7527]

A great solution, as shown by Emma, is using window function and order_rnk=1 to guarantee denominator having unique values ( distinct customers).

[@davidskarbrevik]

Good point, although in the second example it’s a requirement to not have duplicates since a customer can only have 1 first order

[@chrisanderson4387]

Use sum, div0 and distinct

[@emma_ding]

Many of you have asked me to share my presentation notes, and now… I have them for you! Download all the PDFs of my Notion pages at www.emmading.com/get-all-my-free-resources. Enjoy!

[@AMohanKumar031]

Thanks a Ton Emma Ding.

[@pratikmad4u]

customer_id 3 also had an immediate delivery right? since the dates are same in both columns of (order_date and pref_delivery_date) = 2019-10-12 or did i miss something?

[@user-wb7vz8fx9k]

No, because imediate delivery also means the dates of the columns is equal on their first delivery, but in the case of customer id 3, it is his second delivery.

[@pratikmad4u]

@@user-wb7vz8fx9k Thanks , I didn't notice that 2019-10-12 was customer_id 3's second order, first being on 2019-10-09