No video :(

Top SQL Interview Q&A for Data Scientists

Подписаться 56 тыс.

Просмотров 9 тыс.

50% 1

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

Опубликовано:

3 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 11

@zijiaoli906 7 месяцев назад

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 !

@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

@rezarafieirad Год назад

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

@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 8 месяцев назад

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