SQL Magic Show | Solving a Tricky SQL Problem with 10 Methods | SQL Tutorial

Hello everyone, today we are going to solve a tricky problem using 10 methods using SQL. In this process we will cover almost all the SQL topics so watch this video till end and enjoy the show.
Here is the script:
create table students (
student_id int,
skill varchar(20)
);
delete from students;
insert into students values
(1,'sql'),(1,'python'),(1,'tableau'),(2,'sql'),(3,'sql'),(3,'python'),(4,'tableau'),(5,'python'),(5,'tableau');
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer

[@mohdtoufique7446]

Hi Ankit! Thanks for the content ...Another alternative approach SELECT student_id,count(*),STRING_AGG(skill,',')WITHIN GROUP(ORDER BY skill) AS skillset FROM students GROUP BY student_id HAVING STRING_AGG(skill,',')WITHIN GROUP(ORDER BY skill)='python,sql';

[@PyTechnologies]

This is Great Ankit, keep doing it

[@anjalimoon619]

very nice Session Sir. SELECT student_id FROM students GROUP BY student_id HAVING COUNT(DISTINCT skill) = 2 AND SUM(CASE WHEN skill = 'sql' OR skill = 'python' THEN 1 ELSE 0 END) = 2;

[@ReinisRap]

I liked the no. 10 the best!

[@vikramjitsingh6769]

It would be helpful if u could rank these on most efficient query and why....

[@mahesh97413]

Hi Ankit. I tried the below one: %sql WITH cte AS ( SELECT student_id, COUNT(DISTINCT skill) AS num_skills, SUM(CASE WHEN skill IN ('sql', 'python') THEN 1 ELSE 0 END) AS python_sql_count FROM students_A GROUP BY student_id ) SELECT student_id FROM cte WHERE num_skills = 2 -- Ensure there are exactly 2 distinct skills AND python_sql_count = 2; -- Ensure both skills are SQL and Python

[@Ganeshay-996]

oooh ur real brilliant in sql queries ..thanks for more explain and details with different ways

[@ankitbansal6]

You are most welcome

[@avi8016]

Great video sir💯 Excellent use of different methods to achieve the same result. Loved the interesect and except method. Thankyou for bringing such wonderful videos🙏

[@ankitbansal6]

You are most welcome

[@piyushbamboriya1288]

select student_id from students group by student_id having sum(case when skill in ('sql','python') then 1 else -1 end) =2

[@ankitbansal6]

It won't work

[@hari_2305]

its working. good thought!

[@ankitbansal6]

Sorry for the confusion. I think it should work.

[@sahilummat8555]

Hi Ankit another solution from my end select * from ( select student_id,STRING_AGG(skill,',') within group(order by skill) as skills from students group by student_id)a where a.skills ='python,sql'

[@fog2640]

finished watching

[@kailashpatro5768]

Worth Varma worth vere level

[@letslearn9384]

with cte as(select *, case when skill="python" then 1 when skill="sql" then 2 else 3 end as ans from student) select student_id from cte group by student_id having count(student_id)>1 and sum(ans)=3;

[@user-hs9ye6ei6g]

Another Solution: SELECT student_id,count(*) FROM students GROUP BY student_id having count(*) =2 and max(skill) = 'sql' and min(skill) = 'python'

[@sanyamgarg8288]

hi Ankit. with a as( select student_id, count(*) from students group by student_id having count(*)=2) ,b as ( select student_id, min(skill),max(skill) from students where student_id in(select student_id from a) group by student_id having min(skill)='python' and max(skill)='sql' ) select distinct student_id from b; is this ok?

[@user-hf8tq2ul2q]

Hi Sir, Is this correct way select student_id,listagg(skill,',') from students group by student_id having listagg(skill,',')='sql,python'

[@user-xg6yi7hu1t]

Hi Ankit, I tried to solve problem with this approach with cte as ( select student_id,count(distinct skill) cnt from students group by student_id order by student_id ), cte1 as( select s.student_id,c.cnt,s.skill,row_number() over(partition by s.student_id order by s.student_id) rn from students s join cte c on s.student_id=c.student_id and c.cnt=2 and (s.skill='sql' or s.skill='python')) select student_id from cte1 group by student_id having count(rn)!=1; let me know your thoughts on this?

[@dakshbhatnagar]

My solution to the problem with cte as ( select student_id, group_concat(skill) as skills from students group by student_id) select * from cte WHERE skills LIKE '%sql%' AND skills LIKE '%python%' and skills NOT LIKE '%tableau%'

[@naveent1793]

Hi Ankit, I am planning to buy your course and next week I am going to attend for Tableau interview for one of the top product based company. I have some basic knowledge about sql can you help with what questions and concepts need to prepare for the interview.

[@ankitbansal6]

There are no specific topics. They will ask problems and you need to solve them. That's it.

[@KoushikT]

My Simple Approach select student_id from students group by student_id having count(skill) = 2 and count(skill) = sum(case when skill = 'sql' or skill ='python' then 1 else 0 end)

[@as-youtuber6215]

Ankit sir, Can you start your AWS Training as soon as possible????

[@Ganeshay-996]

AWS FREE COURSE |#devops #aws #abhishekveeramalla

[@karangupta_DE]

with cte as ( select student_id, listagg(skill, '_') as skills from students_new group by student_id ) select student_id from cte where skills = 'sql_python';

[@RamaKrishna-ll8iz]

select student_id from students where skill='python' and student_id in ( select student_id from students where skill='sql' ) and student_id in ( select student_id from students group by student_id having count(skill)=2 )

[@himanshuparihar9888]

select student_id from students_1 group by student_id having sum(case when skill in ('sql' , 'python' ) then 1 else 0 end) = 2 and count(*) =sum(case when skill in ('sql' , 'python' ) then 1 else 0 end) --------------------------------------------------------------- with cte as ( select student_id from students_1 group by student_id having count(distinct skill) = 2 ) select student_id from students_1 where skill in ('sql' , 'python' ) and student_id in (select * from cte ) group by student_id having count(*) = 2

[@SM-fq4go]

Know Only SQL, Python: select student_id,string_agg(skill,',') as skills from students group by student_id having Upper(string_agg(skill,',')) like 'SQL,PYTHON' or upper(string_agg(skill,',')) like 'PYTHON,SQL' O/P: student_id skills 3 sql,python Know SQL , Python for sure, can have other skills too: select student_id,string_agg(skill,',') as skills from students group by student_id having string_agg(upper(skill),',') like '%SQL%PYTHON%' or string_agg(upper(skill),',') like '%PYTHON%SQL%' O/P: student_id skills 1 sql,python,tableau 3 sql,python 5 python,tableau,sql

[@MegaSanku123]

--approach 1 with cte as (select student_id,string_agg(skill,',' order by skill) as skill_aggregated from students group by student_id ) select student_id from cte where skill_aggregated='python,sql'; --approach 2 with cte as ( select student_id, max(case when skill='sql' then 'Y' else 'N' end) as sql_skill, max(case when skill='python' then 'Y' else 'N' end) as python_skill, max(case when skill='tableau' then 'Y' else 'N' end) as tableau_skill from students group by student_id order by student_id ) select * from cte where sql_skill='Y' and python_skill='Y' and tableau_skill='N'

[@Leo-qo5hk]

select student_id,count as total_skill,STRING_AGG(skill,', ') as skill from(select *, lag(skill) over(order by student_id) as lag, lead(skill) over(order by student_id) as lead, count(*) over(partition by student_id) as count from students_skills)x where count = 2 and (skill='sql' and lead='python' or skill='python' and lag='sql') group by student_id,count

[@reshmaammu4726]

with cte AS (select student_id, count(skill) as skills from students group by student_id having count(skill)=2), cte2 as (select a.student_id, skills, case when skill="sql" or skill="python" then 1 else 0 end as checker from cte a left join students b on a.student_id=b.student_id) select student_id, count(checker) from cte2 where checker=1 group by student_id having count(checker)=2 or with cte as (select student_id, count(skill) as skills from students where skill in('sql', 'python') group by student_id having count(skill)=2) select b.student_id from students a right join cte b on a.student_id=b.student_id group by b.student_id, skills having skills=count(skill)

[@ank_kumar12]

select student_id from students group by student_id having group_concat(skill) ='sql,python';

[@monasanthosh9208]

1st question solution Select Student_id from (Select *,group_concat(Skill) as Skills From Students group by Student_id Order by Skill)N Where Upper(Skills) Like "SQL,PYTHON";

[@radhikajujjavarapu6971]

WITH SKILLS AS ( SELECT STUDENT_ID , STRING_AGG(SKILL, ',' ORDER BY SKILL) AS ALL_SKILLS FROM STUDENTS_NS GROUP BY STUDENT_ID ) SELECT STUDENT_ID FROM SKILLS WHERE ALL_SKILLS = 'PYTHON,SQL' ;

[@radhikagupta7314]

SELECT DISTINCT student_id FROM ( SELECT students.*, LISTAGG(skill, ',') WITHIN GROUP( ORDER BY skill ) OVER(PARTITION BY student_id) AS consolidated_skill FROM students ORDER BY student_id ) WHERE consolidated_skill = 'python,sql' or consolidated_skill ='sql,python'

[@sunandpal]

WITH SQL_PYTHON AS ( SELECT student_id, COUNT(CASE WHEN skill = 'sql' THEN 1 ELSE NULL END) SQL, COUNT(CASE WHEN skill = 'python' THEN 1 ELSE NULL END) PYTHON, COUNT(CASE WHEN skill = 'tableau' THEN 1 ELSE NULL END) TABLEAU FROM students GROUP BY student_id) SELECT student_id FROM SQL_PYTHON WHERE SQL=1 AND PYTHON =1 AND TABLEAU=0;

[@rishavvv6441]

with base as( select student_id,skill, count(*) over(partition by student_id ) as cnt, case when skill='sql' or skill='python' then 1 else 0 end as flag from students) select student_id from base group by student_id having max(cnt)=2 and sum(flag)=2