Solving SQL Interview Problem with MULTIPLE solutions

Solving SQL Interview Problem with MULTIPLE solutions | Practice SQL Queries

Рет қаралды 21,069

Күн бұрын

In this video, we will solve an SQL interview problem by providing 6 different solutions. The ability to solve an SQL query using multiple solutions can help you to get better at SQL since you will be able to apply different concepts of SQL to solve the same problem and also it can be beneficial during interviews.
During SQL interviews, it is very common for the interviewer to ask you to not not use a specific concept when solving a SQL query or you may be asked to provide multiple solutions to the same SQL problem hence this video will be a good example to practice solving SQL queries where you provide multiple different solutions to the same SQL problem.
Timeline:
00:00 Intro
00:41 Understanding the SQL problem statement
02:24 Solution 1
07:10 Solution 2
11:25 Solution 3
12:39 Solution 4
17:49 Solution 5
21:10 Solution 6
Download the scripts, and dataset from the blog below:
techtfq.com/blog/solving-sql-...
Thanks for watching!
Watch more videos:
🔴 My Recommended courses 👇
✅Complete Data Analytics Bootcamp:
codebasics.io/bootcamps/data-...
✅ Learn Power BI:
codebasics.io/courses/power-b...
✅ Learn complete SQL:
learnsql.com/?ref=thoufiqmoha...
techtfq.graphy.com/courses/Re...
✅ Practice SQL Queries:
www.stratascratch.com/?via=te...
✅ Learn Python:
techtfq.graphy.com/courses/Py...
🔴 WATCH MORE VIDEOS HERE 👇
✅ SQL Tutorial - Basic concepts:
• SQL Tutorial - Basic c...
✅ SQL Tutorial - Intermediate concepts:
• SQL Tutorial - Interme...
✅ SQL Tutorial - Advance concepts:
• SQL Tutorial - Advance...
✅ Practice Solving Basic SQL Queries:
• Practice Solving BASIC...
✅ Practice Solving Intermediate SQL Queries:
• Practice Solving INTER...
✅ Practice Solving Complex SQL Queries:
• Practice Solving COMPL...
✅ Data Analytics Career guidance:
• Data Analytics career ...
✅ SQL Course, SQL Training Platform Recommendations:
• SQL Course / Training
✅ Python Tutorial:
• Python Tutorial
✅ Git and GitHub Tutorial:
• Git and GitHub
✅ Data Analytics Projects:
• Data Analytics Projects
THANK YOU,
Thoufiq | techTFQ

Пікірлер: 67

@swapnilpatil3329 6 ай бұрын

Thank you for the amazing explanation with multiple solutions. Always loved to watch your videos.

@Mrlegacy1_ 6 ай бұрын

What more can I say...your videos are just self explanatory, I love this so much....I just finished solving the 22 SQL problems you posted earlier on the painters dataset, it just feels amazing. I'm always waiting for your videos to drop, I do learn a lot from them♥️♥️♥️

@avi8016 6 ай бұрын

Just wow!! Each solution was different from the other 💯 Thankyou sir 🙏

@thaheers 6 ай бұрын

Really appreciate your preparation and efforts to make the videos straight to the point and easily understandable. Keep doing the great work Thoufiq.

@sufyanyaqoob9109 6 ай бұрын

Thank you so much for the easy explanation, your videos are amazing.

@082SRIRAMDR 6 ай бұрын

Your videos are truly incredible, and they have proven to be exceptionally helpful in enhancing my understanding of SQL concepts. I appreciate the effort and clarity you bring to your content, making it easier for me to grasp complex ideas. Thank you for providing such valuable resources!✨✨

@nambidasan6314 6 ай бұрын

Thank you Tfq. I am really happy that you have made a video of my question which I have sent over an email to you.

@geet_lol 5 ай бұрын

Hi Toufiq, i m so glad to find ur channel. Ur way of explaining things is soooooo good. Wish I had found ur channel long before. Never stop doing what u r doing!

@gocrow23 6 ай бұрын

Wonderfully explained, thank you! Please explore KQL also and start making similar tutorials if possible, it's very powerful too.

@kevinwtao5321 6 ай бұрын

Amazing.... thanks!

@nandan7755 6 ай бұрын

Thanks 👍 explanation is really amazing ❤

@shaonimitra1949 5 ай бұрын

You videos are a gem... Very informative and well explained.. Please make some videos on SQL Date format and SQL String Functions.

@fahimfaysalall 6 ай бұрын

Need more this type of video

@jdisunil 6 ай бұрын

you made my day, I learnt it.

@suchitaachari6615 4 ай бұрын

Thanks a ton for your SQL videos 👏👏 . Your way of presenting the SQL contents is on another level. No bakwas, detailed explanation👏 I am here with another solution for the same. with c1 as (select distinct p_id,sum(case when status='Active' then 1 else 0 end) as flag from dup group by 1 ) select distinct p_id,case when flag=0 then 'InActive' else 'Active' end as status from c1

@pragatiaggarwal8103 Ай бұрын

Your explanations are just wow 🤟

@whooaaapppp 5 ай бұрын

I really learned a lot from this video!

@vikaskumar-qr5tj 5 ай бұрын

Amazing..

@abhishekgowda1776 5 ай бұрын

Thank you 😊

@user-kp4kp2jc8q 5 ай бұрын

I have also Tried in following way - With Cte_parent_child_status (Parent_Id, Sts) as ( Select Parent_Id, LISTAGG(Status, ',') Within Group(Order By Parent_Id) Sts From parent_child_status Group By Parent_Id ) Select Parent_Id, Case when REGEXP_INSTR(REPLACE(STS,'InActive','No'), 'Active')>0 then 'Active' Else 'InActive' End Status from Cte_parent_child_status Order By Parent_Id;

@sanjeetsignh 4 ай бұрын

Amazing.. I had another approach. select parent_id , case when (sum(case when status = 'Active' then 1 else 0 end)) = 0 then 'Inactive' else 'Active' end as status from parent_child_status group by parent_id

@F_A_R_man 6 ай бұрын

Thanks for exercises 😍 And below is one of my solutions↓ select distinct parent_id, case when sum(case when status = 'Active' then 1 else 0 end) over(partition by parent_id) = 0 then 'InActive' else 'Active' end as status from parent_child_status;

@naveenkalyan4700 6 ай бұрын

May I know why you used the aggregate function in a case statement and how it actually works in a case statement? I have been trying to understand this but I couldn't 😢. Hope you will be helpful 😊

@F_A_R_man 6 ай бұрын

@@naveenkalyan4700 I`m happy to help) There are 2 case statements. First one I put in analytic function "SUM(1st case statement) OVER(partition by parent_id)" and then comes 2nd case statement which include that sum analytic function. If you need more detailed explanation then let me know )

@Mrlegacy1_ 6 ай бұрын

Your solution is tricky but simple when you do understand it, I like it👏

@F_A_R_man 6 ай бұрын

@@Mrlegacy1_ Glad that you like it ) In comments I saw "CTE" solution of what I did. You can see it below↓ with cte as( select parent_id, sum(case when status = 'Active' then 1 else 0 end) as act_unact_flag from parent_child_status group by parent_id) select parent_id, case when act_unact_flag > 0 then 'Active' else 'Inactive' end as status from cte; I felt curious about which of this 2 will be faster in performance. Did 4 cross joins and COST difference was 234, CTE was faster. FYI: To look for COST (for Oracle sql) you can highlight query and press F10 or highlight query→right click→Explain...→Explain plan

@user-yt7ok5fy8i 5 ай бұрын

good!

@abhishekgarg9029 6 ай бұрын

SOLUTION 7 : with cte as( select parent_id, sum(case when status = 'Active' then 1 else 0 end) as act_unact_flag from parent_child_status group by parent_id) select parent_id, case when act_unact_flag > 0 then 'Active' else 'Inactive' end as status from cte

@user-yp5tj2ce3s 5 ай бұрын

Really THANK YOU SOOOO... MUCH🙂🙂🙂

@elvemoon 6 ай бұрын

would use QUALIFY if your database support it

@user-xi8if4il9s 5 ай бұрын

Hi sir I saw your video learn how to write SQL queries it's very useful video for all MySQL developers thank you so much and as parllel which course is required for MySQL

@jkumar8590 6 ай бұрын

Hello, what should be the front end tool for developing forms? How to develop reports? Are you covering them in your courses?

@gourav.barkle 5 ай бұрын

Please do this type of video. thansk

@aimanansarmomin4542 6 ай бұрын

Please make a video on date function. And create a table with time column and show how to insert the time

@kiransheikh8536 6 ай бұрын

Dear duty time 20:00 to 8:00 tak hai or check in out k table me 2 column hy empid or checktime or expected output Date-timein-timeout-late-early-dutyhour Is tarha chahiye Slove problem

@user-ur8ph9lj6f 6 ай бұрын

Can you share the important SQL must do questions from platforms like leetcode, stratascratch, data lemur etc in one video to watch before every interview

@anudeepreddy5559 6 ай бұрын

❤❤🔥 💯

@Alexpudow 6 ай бұрын

select parent_id, case when sum(case status when 'Active' then 1 else 0 end) >=1 then 'Active' else 'Inactive' end status from parent_child_status group by parent_id

@vishalsonawane.8905 6 ай бұрын

Done

@arthiques2577 5 ай бұрын

with cte as( select *,sum(case when status='Active' then 1 else 0 end )over(partition by parent_id)as flag from parent_child_status) select distinct parent_Id,case when flag>0 then 'Active' else ' inactive' end as status from cte;

@petermugo9204 5 ай бұрын

Same output thanks with cte as( select *, sum( case when status='Active' then 1 else 0 end )over(partition by parent_id)as flag from parent_child_status) select distinct parent_Id,case when flag =0 then 'InActive' else 'Active' end as status from cte Order by parent_id;

@ekpenkovictor2638 6 ай бұрын

Please help me solve this..write a query to get the least accounts that had the least amount sold in the month of December 2015.. thanks

@gcpchannelforbegineers7080 5 ай бұрын

select parent_id,min(status) as status from table group by parent_id ;

@MarioTorres736 6 ай бұрын

I want to work as data analyst before I start my masters in data science and machine learning. Problem is : I have graduated recently from biomedical Science

@subhranshuchaulia 6 ай бұрын

Hi TFQ

@akifahmed9610 6 ай бұрын

I want to practise SQL, can you suggest some sites where I can practise SQL (not for Data Science purpose)?

@monasanthosh9208 2 ай бұрын

With CTE as (Select Parent_id,group_concat(Child_id), Count(Case when status="Active" then 1 end) as S_Count from Parent_child_Status group by Parent_id) Select Parent_id, Case When S_count>=1 then "Active" else "Inactive" end as Status from CTE;

@sonysingh-vw6qu 5 ай бұрын

Hello sir, Can you please provide the query for the below question to fetch the information: 1.Find all the databases we have in SF non prod account. 2.Find how many tables we have in each databases 3.find total record count in each tables in each databases 4.find last access date by anyone of every table in each databases for all of the above steps, consider only those table which is active or in use 5.find each table size in GB in every databases 6. how to find the storage cost in sf for any Please help me with this

@sagarmagdum7407 6 ай бұрын

SQL king I like it

@btkshamsu561 5 ай бұрын

sir, as a beginner i have a doubt...how do we connect postgre sql server to microsoft excel

@rajareddy3848 5 ай бұрын

when will start new sql batch ?

@kkrkk1113 5 ай бұрын

Hello sir I have one doubt,in there are are 2nd solution has where rn=1,what is the meaning rn

@anchal7876 5 ай бұрын

with cte as (select parent_id,max(case when status='Active' then status else null end) as Active, max(case when status='Inactive' then status else null end) as Inactive from parent_child_status group by parent_id) select parent_id,active from cte where Active is not null union all select parent_id,inactive from cte where Active is null order by parent_id

@user-we3cu9sy8i 5 ай бұрын

select *from parent_child_status; with cte as ( select *, rank() over(partition by parent_id order by cnt desc)as rnk from( select parent_id,status,sum(marks)as cnt from( select *, (case when status = 'Active' then 1 else 0 end)as marks from parent_child_status)as x group by parent_id,status order by parent_id)) select parent_id,status from cte where rnk=1;

@gazart8557 6 ай бұрын

How can you apply min or max to a string type column ? I am referring to status column

@Mrlegacy1_ 6 ай бұрын

But you saw that it worked right?

@babag5324 5 ай бұрын

Python new batch start date plz

@sateeshkumar2698 6 ай бұрын

Can anyone let me know in which editor code was running?

@Gauravop101 6 ай бұрын

His is using pgadmin with dark theme.

@sateeshkumar2698 6 ай бұрын

@@Gauravop101 ok, thanks for replying

@arjundev4908 6 ай бұрын

WITH CTE AS(SELECT *, CASE WHEN PARENT_ID IN(SELECT PARENT_ID FROM PARENT_CHILD_STATUS WHERE STATUS = "ACTIVE") THEN "ACTIVE" ELSE "INACTIVE" END AS NEW_STATUS FROM PARENT_CHILD_STATUS) SELECT PARENT_ID, NEW_STATUS AS STATUS FROM CTE GROUP BY 1;

@kishorl7190 6 ай бұрын

😅i have a doubt to your last solution min(status) . what if the table has 1 inactive and 2 active then the min(status) will be inactive .

@rupeshkumarrk5315 6 ай бұрын

Min doesn’t work that way, when we do min(status) on a string it will give the lowest character string value. As active starts with A, it is the lowest string value. It will still return active in your case

@abhinavkumar2662 6 ай бұрын

We can use here foreign key

@vinaytekkur 6 ай бұрын

WITH get_active_inactive_status AS( SELECT parent_id, SUM(CASE WHEN status='Active' THEN 1 ELSE 0 END) AS active_status, SUM(CASE WHEN status='InActive' THEN 1 ELSE 0 END) AS inactive_status FROM parent_child_status GROUP BY parent_id) SELECT parent_id, CASE WHEN active_status > 0 THEN 'Active' ELSE 'InActive' END AS final_status FROM get_active_inactive_status;