Экстракт Знаний

Data Science SQL Interview Question Walkthrough (real interview style) | SQL Sundays #7

11:58

Data Science SQL Interview Question Walkthrough (real interview style) | SQL Sundays #7

Tina Huang 19.10.2020 8 493 просмотров 212 лайков обн. 18.02.2026

Смотреть на YouTube

Поделиться Telegram VK Бот

Транскрипт Скачать .md

Анализ с AI

Описание видео

Hi friends! Welcome back to SQL Sundays! A weekly SQL data science interview questions and answers walkthrough. This is another data science SQL interview question and answer done from beginning to end in a real interview style. 🔗Affiliates ======================== My SQL for data science interviews course (10 full interviews): https://365datascience.com/learn-sql-for-data-science-interviews/ 365 Data Science: https://365datascience.pxf.io/WD0za3 (link for 57% discount for their complete data science training) Check out StrataScratch for data science interview prep: https://stratascratch.com/?via=tina 📲Socials ======================== instagram: https://www.instagram.com/hellotinah/ linkedin: https://www.linkedin.com/in/tinaw-h/ discord: https://discord.gg/5mMAtprshX 🤯Study with Tina ======================== Study with Tina channel: https://www.youtube.com/channel/UCI8JpGrDmtggrryhml8kFGw How to make a studying scoreboard: https://www.youtube.com/watch?v=KAVw910mIrI Scoreboard website: scoreboardswithtina.com livestreaming google calendar: https://bit.ly/3wvPzHB 🎥Other videos you might be interested in ======================== SQL Sundays Playlist: https://www.youtube.com/playlist?list=PLVD3APpfd1tuXrXBWAntLx4tNaONro5dA How I learned SQL from Scratch in 11 Days to Pass my FANNG SQL Interview: https://www.youtube.com/watch?v=vaD3ZFFNwhM How I consistently study with a full-time job: https://www.youtube.com/watch?v=INymz5VwLmk How I would learn to code (if I could start over): https://www.youtube.com/watch?v=MHPGeQD8TvI&t=84s 🐈‍⬛🐈‍⬛About me ======================== Hi, my name is Tina and I'm a data scientist at a FAANG company. I was pre-med studying pharmacology at the University of Toronto until I finally accepted that I would make a terrible doctor. I didn't know what to do with myself so I worked for a year as a research assistant for a bioinformatics lab where I learned how to code and became interested in data science. I then did a masters in computer science (MCIT) at the University of Pennsylvania before ending up at my current job in tech :) 📧Contact ======================== youtube: youtube comments are by far the best way to get a response from me! linkedin: https://www.linkedin.com/in/tinaw-h/ email for business inquiries only: hellotinah@gmail.com ======================== Some links are affiliate links and I may receive a small portion of the sales price at no cost to you. I really appreciate your support in helping improve this channel! :) Music Credit: TheFatRat & Laura Brehm - We'll Meet Again https://www.youtube.com/watch?v=hJqYc62NCKo

Оглавление (3 сегментов)

0:00 Segment 1 (00:00 - 05:00) 468 сл.
5:00 Segment 2 (05:00 - 10:00) 419 сл.
10:00 Segment 3 (10:00 - 11:00) 61 сл.

Segment 1 (00:00 - 05:00)

and we're back with another sequel sunday let's jump into it okay so this question that we have here today is from airbnb find the minimum average and maximum rental price for each review qualification category the review qualification category is a classification applied to each rental property based on the number of reviews the property has okay so it's defined below like this cool okay let's look at the table here so okay airbnb search details this is a big table uh lots of things going on let's see so we want to look at the review qualification so we're looking at the number of reviews the property has so we're basing these um classification on that so let's see if one of these columns gives us a hint about the number of views okay bathroom blah blah oh okay cool so number of views is literally here this is really nice i actually thought that it would be more complex since you know maybe it would be like each review and you had to aggregate it but it has a number of views here for us that's great so let us have a plan and see what we're gonna do so finally we want our expected output to look like so reviews qualification oops slice spelling area there reviews qualification um let's comment this out main price average price and max price cool all right so what assumptions do i have okay so first assumption is that all the values are going to be an integer so i'm going to assume that we don't have any null values here um yeah so i'm going to assume that it's gonna be positive integers for this what other things do i have i think that's about it cool all right we can go on to right what we're gonna do so first thing we're gonna do is do the classification and to do this we're going to use case 1 statements should be pretty straightforward and then after we do that i am going to um just do aggregation so group by the rental price oops not rental price um group by qualification category and then aggregate min average and maximum price cool all right let's do this query so from airbnb search details a case when what is it called again number of reviews is equal to zero then no one number of reviews is oops nice between one and five then few when number of reviews between 6 and 15 then number of reviews between 16 and 40 than many when number of reviews actually i can just do this else a lot because we're assuming since um it's not going to be null and it's going to be positive so this should catch everything else

Segment 2 (05:00 - 10:00)

that's more than 40 reviews okay so end as what is it called again oh yeah reviews qualification cool um so now we can do like a sub query for this uh like nested but i always prefer to use ct's because i just think it's a lot clearer for the interviewer to read and also for you to not get confused oh oops one thing one thing uh we also need a price right because we are aggregating by price cool all right so let's call this with um class with reviews as here and we can do from reviews now we're going to group by qualification category so let's write the select statement first select reviews qualification um what is it minimum so minimum of price as bin price as price as average price and max price x max price as uh max price cool and then we're gonna group by one let's see looks good to me fingers crossed ran our code oh no what happened oh my god i forgot to write the select statement whoops okay rookie mistake okay this should work now yay okay good we got this right all right is there any other way that we can optimize things you could use a sub query like just nest it and not save it as a table so that would optimize actually no i don't would that even optimize things um that's a good point let's check that out so let's see like ct versus sub query performance okay so they're actually the same since both provide the same information to query optimizer use more than once could be easily identifying calculators out could then be stored okay so they're actually the same times performance so i don't think we can actually optimize it based upon that and i don't think that we can optimize it in any other way either okay so they did us a sub query here um yeah it's pretty much the same solution it's just the subquery i generally just prefer using cts as opposed to subqueries we're done with this episode and feel free to check out strata scratch i will link it below as well alright see you guys next time suddenly you're nowhere to be found i turn around and everything has changed looking for a way to work it out i'm trying to find some peace is again everything we wanted turned to gold the past we chose the future on our side

Segment 3 (10:00 - 11:00)

and the writing on the can you hear me we'll meet again dark for the sunrise clouds for a blue sky space for the traveling star strong from the inside you're still my lifeline i feel you wherever you the oak are where i met you and the writing on the statue i still remember happy words follow me again you

Ещё от Tina Huang

How I would learn to code (if I could start over)

Tina Huang | 25.04.2021 | 13 сегм. | 3 449 134

How I consistently study with a full time job (9 months in and still going strong)

Tina Huang | 09.10.2021 | 5 сегм. | 2 888 742

How to learn to code FAST using ChatGPT (it's a game changer seriously)

Tina Huang | 02.03.2023 | 8 сегм. | 2 387 940

How to self study technical things

Tina Huang | 04.04.2021 | 10 сегм. | 2 322 419

Financial Literacy In 63 Minutes

Tina Huang | 01.01.2025 | 15 сегм. | 2 083 145

Google's 9 Hour AI Prompt Engineering Course In 20 Minutes

Tina Huang | 09.12.2024 | 10 сегм. | 1 552 364

Ctrl+V

Экстракт Знаний в Telegram

Транскрипты, идеи, методички — всё самое полезное из лучших YouTube-каналов.

Подписаться