Solving SQL Interview Problem with MULTIPLE solutions | Practice SQL Queries

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

[@swapnilpatil3329]

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

[@nambidasan6314]

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.

[@kevinwtao5321]

Amazing.... thanks!

[@sufyanyaqoob9109]

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

[@Mrlegacy1_]

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]

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

[@thaheers]

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

[@fahimfaysalall]

Need more this type of video

[@gocrow23]

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

[@nandan7755]

Thanks 👍 explanation is really amazing ❤

[@vikaskumar-qr5tj]

Amazing..

[@geet_lol]

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!

[@pragatiaggarwal8103]

Your explanations are just wow 🤟

[@shaonimitra1949]

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

[@suchitaachari6615]

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

[@082SRIRAMDR]

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!✨✨

[@abhishekgowda1776]

Thank you 😊

[@jdisunil]

you made my day, I learnt it.

[@user-yt7ok5fy8i]

good!

[@elvemoon]

would use QUALIFY if your database support it

[@whooaaapppp]

I really learned a lot from this video!

[@F_A_R_man]

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]

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]

@@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_]

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

[@F_A_R_man]

@@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-yp5tj2ce3s]

Really THANK YOU SOOOO... MUCH🙂🙂🙂

[@jkumar8590]

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

[@gouravbarkle7286]

Please do this type of video. thansk

[@sanjeetsignh]

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

[@user-xi8if4il9s]

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

[@anudeepreddy5559]

❤❤🔥 💯

[@aimanansarmomin4542]

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

[@vishalsonawane.8905]

Done

[@user-ur8ph9lj6f]

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

[@akifahmed9610]

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

[@subhranshuchaulia]

Hi TFQ

[@ekpenkovictor2638]

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

[@user-kp4kp2jc8q]

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;

[@sagarmagdum7407]

SQL king I like it

[@btkshamsu561]

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

[@rajareddy3848]

when will start new sql batch ?

[@gcpchannelforbegineers7080]

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

[@gazart8557]

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

[@Mrlegacy1_]

But you saw that it worked right?

[@kkrkk1113]

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

[@MarioTorres736]

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

[@arthiques2577]

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]

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;

[@abhishekgarg9029]

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

[@kiransheikh8536]

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

[@sateeshkumar2698]

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

[@Gauravop101]

His is using pgadmin with dark theme.

[@sateeshkumar2698]

@@Gauravop101 ok, thanks for replying

[@Alexpudow]

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

[@sonysingh-vw6qu]

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

[@kishorl7190]

😅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]

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

[@babag5324]

Python new batch start date plz

[@anchal7876]

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

[@monasanthosh9208]

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;

[@user-we3cu9sy8i]

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;

[@arjundev4908]

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;

[@abhinavkumar2662]

We can use here foreign key

[@vinaytekkur]

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;