Solving HARD SQL Interview Questions on Analyst Builder

Let's look at how we can solve a Hard SQL technical interview question. In this series we work our way through easy, medium, hard, and very hard SQL interview questions that you may see in a technical interview.

____________________________________________

RESOURCES:

Coursera Courses:

Udemy Courses:

*Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!*
____________________________________________

BECOME A MEMBER -

Want to support the channel? Consider becoming a member! I do Monthly Livestreams and you get some awesome Emoji's to use in chat and comments!

____________________________________________

Websites:
📱Instagram: @Alex_The_Analyst
____________________________________________

*All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for*

[Appropriate_dinner]

Hi Alex! From Malaysia here, i just want to tell you i have just landed an interview for Data Analyst job although i am in Civil Engineering background.I have been following you since last June and follow learned all your bootcamp. I just want to tell you thank you so much!

[lovelyintheisland]

Thank you Alex. I listen to your videos in the car on my to work then I watch it in the evenings to practice. Very helpful and inspiring to learn and achieve more in my career.

[khadigahany]

your videos are extremely helpful, I've been studying from them for a while and wanted to thank you a lot for helping others by giving very useful tips and clear explanation to all important matters. you're such an angel.

[BehzadAbedi-bw]

Awesome video. For the Cake vs Pie question, you can use Rank and Lag functions. SELECT *, RANK() over (partition by SoldDate order by amount_Sold) as Winner, amount_sold - LAG(amount_sold, 1, 0) over (partition by SoldDate order by amount_sold) as diff
FROM CakePie

SELECT aaa.SoldDate, aaa.Product, aaa.diff
fROM
(
SELECT *, RANK() over (partition by SoldDate order by amount_Sold) as Winner, amount_sold - LAG(amount_sold, 1, 0) over (partition by SoldDate order by amount_sold) as diff
FROM CakePie
) as aaa
WHERE aaa.Winner = 2

[brendansully]

This channel looks like a WEALTH of information! Thank you for making it all

[yeerehneh]

Found ur channel a few days ago as an aspiring data analyst and lovelovelove everything!!!! Pls keep up the good work thanku

[RakeshKumar-rwb]

Alex ! This Channel Just a Tree of Best Learning Knowledge ! Thx !

[Bobthetomado]

I've ran into similar problems of StrataScratch, but the Analyst Builder question should be a bit more clear on if the discount only applies to the 3rd transaction ever made, or every 3 transactions.

[Nyrus]

Alex! Your content has been so huge in my journey to become a data analyst.

I’m working on doing some projects and putting together my portfolio, but man it’s so hard to put all of the skills together and know what to do and when to use certain tools.

I’m starting to get some of those valley of despair feelings. Do you, or anyone else for that matter, have any advice or tips on getting through knowing the technical skills but not knowing how to implement them?

[StandbyxP]

Great video as always Alex! Just one question since I am still learning: Does the "DATEDIFF(t1.date, t2.date) = 1" always mean 1 day difference? Because I thought it is mandatory to put an intervall before putting (start_date, end_date) inside the parenthesis of DATEDIFF(), to determine if you want year, month, day etc. as difference.

[malcorub]

I solved it like this in SSMS. But I don't think temp tables are allowed in Analyst Builder.

SELECT [date]
, [temperature]
, [temperature]-LAG([temperature]) OVER(ORDER BY DATE) AS 'LAG'
INTO #TEMPY
FROM [temperatures]

SELECT [DATE]
FROM #TEMPY
WHERE [LAG]>0

DROP TABLE #TEMPY

[PRIYANKAREDDY-lnhu]

Let's assume you have the following three tables:

Students table:

Columns: StudentID (Primary Key), FirstName, LastName

Courses table:
Columns: CourseID (Primary Key), CourseNameEnrollments table:

Columns: EnrollmentID (Primary Key), StudentID (Foreign Key referencing Students table), CourseID (Foreign Key referencing Courses table), Grade
Write a SQL query that retrieves the average grade for each course.


The result should display the CourseName and the average Grade for each course.
If a course has no grades recorded, it should still be included in the result with an average

[UtsavSaxena-rs]

In the first question, My output is same but its showing that its not correct,

WITH cte_t AS
(SELECT
customer_id, transaction_id, amount, row_number() over(PARTITION BY customer_id ORDER BY transaction_id) AS trns3
FROM purchases)
SELECT customer_id, transaction_id, amount, amount*0.67 FROM cte_t where trns3 = 3 ORDER BY customer_id;

[de_da_oo]

re: Q2 shouldn't filter in the ON clause; the ON clause should only include join criteria. Poor practice and readability matter at this level

[Helmofthedominator]

Kelly's 3rd Purchase. That code is working only if the customers don`t have the 6th purchase, to solve that problem you need to write the correction in code like when the row_number is = 3 then it must restart and begin again from 1 to 3, and on and on and on and on. Sample code :

WITH CTE AS
(
SELECT *, (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY transaction_id )-1) % 3 + 1 AS third_transaction
FROM purchases
)
SELECT customer_id, transaction_id, amount, (amount - amount *0.33) AS discounted_amount
FROM CTE
WHERE third_transaction = 3

[carlosoviedo]

I now this is no the place to ask this but please; can make a video of import and export data from managment sql server and cover the thing with the wizard? plase you are really good explaining things

[JoshuaNichollsMusic]

8:30 I’m yelling at the screen “Use QUALIFY!!” I use window functions all the time and QUALIFY is the simplest solution for these situations where you need to use the output of a window function, in my experience. Maybe it’s only a Snowflake SQL thing 😅

[vladton]

What about 6th, 9th, 12 th transactions?

[AltFEsc]

I love this, but you should show every third purchase. The 1st 3rd purchase wont get you very far.

Where mod(row_num, 3) = 0
Mod returns the remainder of long division

[datascience]

Hi Alex, having trouble logging into analystbuiler. can you help?