Real SQL Interviews: Amazon's TOP 2021 question REVEALED?

DISCLAIMER: I never use Whatsapp. If anyone solicits you to a private messaging platform, it is NOT me. Avoid scammers if you care about your tendies. 🔥 It's cold on the moon. Gear up with MERCH 🔥 Stop by every week for new limited edition shirts, mugs, and more space upgrades.
👉🏽 www.andrewmomoney.com/ Breaking into FAANG? It's dangerous to go alone, take this.
My name is Andrew Mo, and I'm here to help you find your Six Figure Dream Job. As a 25-year-old in Silicon Valley that built 9 streams of passive income in 2020, I want to help you find your own path to 6 figures in 6 months. Let's leap together.
Subscribe. Your friends do it, and they're wondering where you are. kzbin.info...
⚠️ LIMITED TIME: Get 4 Free Stocks on WeBull (Valued up to $1600 when you deposit $100): act.webull.com/kol-us/share.h...
Hey you! Buy me a coffee!
► / andrewmomoney
► $50 to invest in anything for FREE. No kidding. www.sofi.com/share/invest/250...
► Try Audible and Get Two Free Audiobooks: amzn.to/3kyUTVF
► Your chance at free Microsoft stock ($216.86) join.robinhood.com/andrewm2365
► How I make my videos amzn.to/3iRvk0K
► One time donations paypal.me/andrewcoin
💰 FREE Discord: / discord
► Follow Me On Instagram: / andrewmomoney
► The first step to a $100K+ Salary by reading a SQL book: amzn.to/3aCiNvr
How to Make a Six Figure Salary in Six Months • How to Make a 6 Figure...
The $0 Data Science Bible • The $0 Data Science Bi...
Pandas vs SQL • Pandas vs. SQL - Which...
► My Gear
Sony ZV-1 Camera: amzn.to/3iRvk0K
Blue Yeti USB Mic: amzn.to/2PCyWag
My keyboard: amzn.to/2VCIQfh
#AndrewMoMoney
* Hey one more thing: I use affiliate links on the products I linked above. These give me a small commission at no cost to you. They help support me, so thank you for your part! The video is accurate as of the posting date but may not be accurate in the future. Not a lawyer or financial advisor! All opinions expressed are mine alone and for entertainment only.

[@AndrewMoMoney]

Thanks for watching to the end! Subscribe for Chrisy P Bacon kzbin.info ⚠️ LIMITED TIME: Get 4 Free Stocks on WeBull (Valued up to $1600 when you deposit $100): act.webull.com/kol-us/share.html?hl=en&inviteCode=uXju4KmEXjXO Hey you! Buy me a coffee! ► www.patreon.com/AndrewMoMoney ► $50 to invest in anything for FREE. No kidding. www.sofi.com/share/invest/2503183 ► Try Audible and Get Two Free Audiobooks: amzn.to/3kyUTVF ► Your chance at free Microsoft stock ($216.86) join.robinhood.com/andrewm2365 ► How I make my videos amzn.to/3iRvk0K ► One time donations paypal.me/andrewcoin 💰 FREE Discord: discord.gg/KF4QhFYgxb ► Follow Me On Instagram: instagram.com/AndrewMoMoney ► The first step to a $100K+ Salary by reading a SQL book: amzn.to/3aCiNvr How to Make a Six Figure Salary in Six Months kzbin.info/www/bejne/a4eXe4SEes-gptU The $0 Data Science Bible kzbin.info/www/bejne/ZouTaJ-KpbVsp5I Pandas vs SQL kzbin.info/www/bejne/bonQp56PhLCkrK8

[@kandarajs]

With cute as ( Select Name, Salary, Dense_rank() over(partition by department_id Order By Salary Desc) as Rank, department_id From Employees ) Select * From cute as c Inner Join Departments as d On c.Department_Id = d.department_id Where Rank

[@TheWealthPrince]

Yess love this, nice content man. Thank you for sharing!

[@AndrewMoMoney]

Cheers, friend! Thanks for dropping a comment and watching :)

[@CruiserPup]

I absolutely love having your doggo as part of your videos! Thanks for sharing your furbaby along with your knowledge with us ;D

[@Teayele19]

I need more videos, the way how you approached and processed your thoughts on data, Shows that you are an excellent resource person for Mastering SQL

[@AndrewMoMoney]

I appreciate that!

[@hemanginimalhotra9269]

CTE is a recursive solution and Subquery isn't. Yes, we can use CTE in this case as below: WITH RESULT AS ( SELECT id, SALARY, d_name, DENSE_RANK() OVER (PARTITION BY D_NAME ORDER BY SALARY DESC) AS DENSERANK FROM EMPLOYEE ) SELECT * FROM RESULT WHERE DENSERANK

[@TheManincognito]

This is partially correct as the join to get the department name is missing to match the final expected output

[@sir-cute]

shouldn't this be a rank() rather than dense_rank. Lets say the salaries are 9000,8000,8000,7000 - rank will have this marked as 1,2,2,3. That will correctly list the top 3 salaries through two of them repeated. but dense_rank will rank it 1,2,2,4 - thus only the 9000,8000,8000 will be pulled in. technically speaking the employee with sal 7000 is the 3rd most highest sal in his department and he should be considered too.

[@bobfar]

@@sir-cute depends to the questions , I saw this question somehow asked from this 9000,8000,8000,7000 only show 9000,8000,8000 , btw, I am also going with rank

[@debtfreeF]

Just found your channel and I love the aesthetics and thumbnails!

[@AndrewMoMoney]

Yay! Thank you!

[@DrsGanandMo]

This is actually something we where talking about the other day...but in terms of google and facebook, but this is such a great a video! Just sub'd for more content!

[@AndrewMoMoney]

Thank you, doctors!

[@ThreeTwentyTech]

Can't miss another of Mr. Mo's videos! Random question but what font did you use in the beginning of your video that said 'Chrisy P Bacon'? 😂

[@AndrewMoMoney]

That is the classic bebas font for KZbinrs (only) 😂

[@ThreeTwentyTech]

@@AndrewMoMoney How did you get it have that white border?

[@AndrewMoMoney]

@@ThreeTwentyTech go into borders in premier/photoshop

[@ThreeTwentyTech]

@@AndrewMoMoney any ideas if I can use the same border effect in final cut pro?

[@AndrewMoMoney]

@@ThreeTwentyTech I bet! But I've only ever used adobe products #spoiled

Great video my man!!

[@AndrewMoMoney]

MY MAN

[@lotannaagu2693]

Hey, would we be required to actually create the table during the interview? Btw.. great video

[@sayeedraza3199]

Hi Nice video. I have a question Can it be done via rank analytic function(partitioning by dept order salary desc) and selecting the 3 ranks via outer sub query where rank

[@AndrewMoMoney]

Yes, absolutely! You're thinking ahead aren't ya!

[@sayeedraza3199]

@@AndrewMoMoney haha thanks it means a lot

[@shrutijain1628]

Loved this video 💯💯

[@AndrewMoMoney]

Thanks a mil, Shruti!

[@hectorelizondo8443]

Great explanation! I was able to find one of the three bugs, I'll try to spot all of them next time ;) Thanks for sharing! :)

[@AndrewMoMoney]

Hehe gotta catch 'em all

[@WhyofAI]

Great video Andrew!

[@AndrewMoMoney]

Means a lot, Alex!

[@felixamaladhas9272]

Hello @Andrew, You are spot on, Man. I got 2 out of these questions in my SQL assessment round.

[@unorthodoxsavings9552]

This video was fun to watch!

[@AndrewMoMoney]

Muuuch love

[@ravintrivedi]

Love your videos man, keep'em coming.. Another way of doing this can be: WITH e AS ( SELECT *, DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) as Rnk FROM Employee ) SELECT d.Name as Department, e.Name as Employee, e.Salary as Salary FROM e JOIN Department d ON e.DepartmentId = d.Id WHERE e.Rnk

[@AndrewMoMoney]

Appreciate it! Thanks for the alternate code :)

[@FinanceAP]

Awesome!

[@AndrewMoMoney]

Thanks friend!

[@productivewallace]

It was gonna say where the hell the coolest intro, well second coolest ;)

[@AndrewMoMoney]

I know, right? What's the coolest tho?

[@lotannaagu2693]

Hi, Great video, why do you not use top 3 command or limit 3. Would those also work Btw... thanks for your content, have an Amazon interview in a few days

[@tripviewers6540]

Hi Lotanna, how was the interview? can you share a bit about how was the SQL test? What type of problems you have to resolve, functions used, is it live coding? Thank you

[@asalfad1337]

Hey Lotanna- can you share the questions asked during the interview?

[@hahah0811]

Except for the difference in syntax, there no other significant difference for this case. I consider CTEs like functions, unlike subqueries they can be used recursively.

[@AndrewMoMoney]

NICELY said Surbhi :)

[@sanikasharma4712]

For this , you could have used row number as well ? & Row number

[@AndrewMoMoney]

Great suggestion!

[@TheManincognito]

@@AndrewMoMoney Row Num would not work in case of duplicates

[@RahulRaj-tl2ly]

Select () From emp e1 Where e1.sal in ( select sal from emp e2 where e1.deptid = e2.deptid order by desc limit 3 ) Will this work? If I only select columns from employee table?

[@vyna9824]

Love this video

[@AndrewMoMoney]

Appreciate it Vy!

[@RajAryan1003]

I was thinking from the beginning why joining by id why why why 😄 This is a tricky answer when asked to avoid rank function 👍🏻

[@andrewnow9937]

So well thought out in the outline! Just dropping a comment before I get to the end :)

[@AndrewMoMoney]

Aww thanks Dianne

[@miguksimin]

I have interview within a week, and thanks for your video. Although i didnt try, i thought it would be easily done by using partition and dense rank, and just filter it with rank less than 4.

[@thambichettan]

How did the interview go? I have mine this week. 😁

[@miguksimin]

@@thambichettan The test I got was mainly about algorithm test. Although I didn't get passed to the second round, but I found exactly same problems in Leetcode, so make sure study those. Best of lucks

[@thambichettan]

@@miguksimin thanks!!

[@mayankbhardwaj1487]

easy one : with one as ( select *, dense_rank() over(partition by departmentid order by salary desc) as rnk from team ) select one.name, one.salary,d.name from one join department d on one.departmentid=d.id where rnk

[@carolbanks1333]

A common table expression is my preference of choice :) it is cleaner and I believe more efficient than subqueries

[@AndrewMoMoney]

Thanks for the submission!

[@Teayele19]

I Loved this video, it is valuable

[@AndrewMoMoney]

Thanks, friend! How's your weekend so far?

[@hessamharandi7954]

Hey nice video. I think it should be E2.Salary

[@privateaccount5917]

Hey Andrew, Sorry but i really don't understand WHERE 3> How is it filtering top 3? Where statement you mixed e1 and e2 From where i can get more tutorial about this thank you :D

[@AndrewMoMoney]

Hey I linked the answer in the description I believe!

[@nenaGarciaable]

this is what i got: SELECT Department.Name, Employee.Name, Employee.Salary FROM Employee LEFT JOIN Department ON Employee.DepartmentId = Department.Id ORDER BY Employee.Salary DESC LIMIT 3;

[@DonellGrace]

Before Watching the solution I tried this problem myself on Leetcode, my very first problem attempt in Leetcode and I'm glad I got this right.May not be the most efficient solution though. select Department,Employee,Salary from( select dept.Name as Department,emp.Name as Employee,emp.Salary as Salary,DENSE_RANK() OVER (PARTITION BY emp.DepartmentID ORDER BY emp.Salary desc) as salary_rank from Employee emp inner join Department dept on emp.DepartmentID=dept.Id )emp_details where salary_rank

[@AndrewMoMoney]

Nicely written! Speed is definitely one of the important pieces to acing an interview

[@mini7430]

This is what i wrote and it worked: with cte AS (SELECT employee.id, employee.name as employee, dept.name as Dept, salary, row_number() OVER (PARTITION BY dept.name ORDER BY salary desc) AS row_number FROM employee LEFT JOIN dept ON employee.dept_id = dept.id ) SELECT * FROM cte where row_number IN (1, 2, 3);

[@AndrewMoMoney]

Well implemented! Any idea how it performs?

[@krishnarao8290]

Doggo looks like he or she's fall asleep and down the bed any moment now

[@MyHarshitgola]

can we expect this level of difficulty or more complex problems for a BI Engineer position?

[@madeehakhan2711]

is there one for 2022?

[@AnkitKshatriya]

2:10 Why does the output table show 4 IT salaries when only top 3 are required from each department?

[@AndrewMoMoney]

Seems like because Randy and Joe are tied, and they are counting distinct salary values, not rows

[@SamairaJain11]

Why not window function?

[@honguyen9364]

A CTE would have been just as good I believe, although you would have to do a single liner select statement in the outer query too...I think?

[@AndrewMoMoney]

Thanks for the submission!

[@marymishka6841]

Christmas came early :0

[@AndrewMoMoney]

D'aaw shucks

[@snownankong885]

I got confused, why don't we just use the rank() function?? It should be simple enough to solve this question select department, employee, salary from ( select employee, salary, name as department, rank() over(partition by departmentid order by salary desc) as rk from employee left join department on employee.departmentid = department.id ) where rk

[@petradorkin5121]

A cte goes above the outer query with a “with” but there’s no other difference!

[@AndrewMoMoney]

Thanks for your submission :)

[@rollinOnCode]

too cute doggo

[@powertrading-smallaccountc4605]

Wow.. I was preparing for tomorrows onsite ..and i ended up solving the same question just before watching your video using window function dense_rank. Great Coincidence !

[@AndrewMoMoney]

WHOA that means it's catching on!! Thanks for sharing

[@domtheentrepreneur5131]

Chrisy P lookin thic! After listening to the rest of it I like to think I will eventually catch on 😂

[@AndrewMoMoney]

Ahahaha THICC

[@nainaalex9053]

Thanks for this, I did use the window functions though. Primarily because it's easier for me to visualize. Here's my approach: # Write your MySQL query statement below #1. PARTITIONED INTO DEPARTMENTS #2. RANK BASED ON SALARIES #3. LIMIT THE OUTPUT TO TOP 3 Select Department, Employee, Salary from (SELECT Department.name AS Department, Employee.name AS Employee, Employee.salary AS Salary, dense_rank() OVER(PARTITION BY Employee.departmentId ORDER BY Employee.salary desc) 'ORDER' FROM Employee LEFT JOIN Department on Employee.departmentId = Department.id) T where T.ORDER

[@myho2260]

select d.Name_depart as Department, e.Name as Employee, e.Salary from Department as d inner join (select *, dense_rank() over(partition by DepartmentId order by Salary desc) as ranking from employee) as e on d.Id = e.DepartmentId where ranking

[@yuddhveersingh710]

I am late today but i am consistent that is more important 😀

[@AndrewMoMoney]

That's the key, EVERY day

[@yuddhveersingh710]

@@AndrewMoMoney yes sir

[@user-zl3jd9dq8x]

I am just worried about 4 rows of IT. I don't think my query will produce that. Kindly correct. SELECT * FROM ( SELECT D.DEPT , E.NAME , SALARY , RANK() OVER(PARTITION BY DEPT ORDER BY SALARY DESC) RNK FROM EMP E INNER JOIN DEPT D ON E.DEPTID= D.ID )WHERE RNK

[@AndrewMoMoney]

Which line of code is throwing the error?

[@user-zl3jd9dq8x]

@@AndrewMoMoney No, I don't have SQL Server to run this. I am saying, when we are asking for top 3 Salaries by department, my code will not give 4th row for IT department since Rank will filter that out.

[@AndrewMoMoney]

@@user-zl3jd9dq8x Check out the link in the description to use leetcode's server to run your code!

[@varunhimmatramka7816]

@Ashwini Jay Try using dense_rank function instead of rank.

[@od2437]

Cute dog

[@tonyjinca]

Cute wooden pig Sir!!

[@jayshiljain7946]

Much Simpler and Faster Using WINDOW FUNCTIONS WITH CTE AS ( SELECT d.Name as Department, e.Name as Employee, Salary, DENSE_RANK() OVER(PARTITION BY d.Name ORDER BY Salary desc) as r FROM Employee e LEFT JOIN Department d on e.DepartmentID = d.Id) SELECT Department, Employee, Salary FROM CTE WHERE r < 4

[@AskArianne]

Did you get Chrisy P from Amazon? 😆

[@AndrewMoMoney]

She's from a small town in Austria! Handcrafted from Hallstatt :)

[@AskArianne]

@@AndrewMoMoney Wow :o

[@AndrewMoMoney]

@@AskArianne I totally should've mentioned it in the video tho!

[@learnlife8055]

Hi Andrew, Thanks man for this video. How about the below query? WITH MAX3_SALARY_PER_DEPT AS ( SELECT DEPARTMENT_NAME, FIRST_NAME, SALARY, ROW_NUMBER() OVER ( PARTITION BY DEPARTMENT_NAME ORDER BY SALARY DESC ) AS RN FROM ( SELECT DEPARTMENT_NAME, FIRST_NAME, SALARY FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID WHERE DEPARTMENT_NAME IN ( SELECT DISTINCT DEPARTMENT_NAME FROM DEPARTMENTS ) ) T ) SELECT DEPARTMENT_NAME, FIRST_NAME, SALARY FROM MAX3_SALARY_PER_DEPT WHERE RN

[@ravitanwar9537]

I'm sorry but I quit the video as soon as I saw "leetcode Hard" 🤣

[@AndrewMoMoney]

Wahahaha

[@ravitanwar9537]

@@AndrewMoMoney that's so evil of you professor Andrew 😅

[@AndrewMoMoney]

@@ravitanwar9537 gotta confront the Hards eventually

[@meenakshishukla1374]

Select d.department_name, e.employee_name Row_number() over (partition by d.department_name order by salary desc) rn From employee e Inner join Department d On e.department_id=d.department_id Where rn

[@AndrewMoMoney]

I say: GOOD

[@miguksimin]

Row number wouldnt work since multiple people have same salaries. You need dense_rank instead

[@iamyuvi009]

select Department, Employee, Salary from (select d.name Department, e.name Employee, e.salary, dense_rank() over(partition by e.departmentId order by salary desc) rnk from employee e join department d on e.departmentId=d.id) a where rnk

[@BitigoBlack]

🤯😍👏🏾📃✍🏽

[@moizshaikh9653]

Don't you think your solution is a little complicated to understand? Here is my take on the same problem, SELECT Department, Employee, Salary FROM ( SELECT d.Name as Department, e.Name as Employee, Salary, DENSE_RANK() OVER(Partition by e.departmentid order by e.Salary desc) as rnk FROM Employee e JOIN Department d ON e.departmentId = d.Id ) cte WHERE rnk

[@AndrewMoMoney]

Well written! I think this is a great approach in comparison

[@kuyajerrysolis7194]

Mga idol magtulongan nlang tyu pwede thank u god bless

[@AndrewMoMoney]

Salamat, Kuya Jerry!

[@bobfar]

Sample Data /* create table employee ( id int , name varchar(100) , salary int , departmentid int); insert into employee ( id , name, salary, departmentid ) values(1,'Joe',85000,1), (2,'Henry',80000,2), (3,'Sam',60000,2), (4,'Max',90000,1), (5,'Janet',69000,1), (6,'Randy',85000,1), (7,'Will',70000,1) create table department ( id int , name varchar(100)); insert into department ( id , name) values (1,'IT'), (2,'Sales'); */ Solution select Department,Employee from ( select e.name as Employee, d.name as Department , rank() over (partition by departmentid order by salary desc) rn from employee e inner join department d on d.id = e.departmentid ) as t where t.rn