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

[@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';

[@akashgoel601]

great solution! adding my sol, using pivot got the output. even simpler: with cte as ( SELECT student_id AS student_id, [python], [sql], [tableau] FROM ( SELECT student_id, skill FROM students ) AS SourceTable PIVOT ( min(skill) FOR skill IN ([python], [sql], [tableau]) ) AS PivotTable ) select student_id from cte where tableau is null and python is not null and sql is not null

[@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;

[@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

[@rajat1999gupta]

Hey Ankit, my mentos style solution select student_id, group_concat(skill order by skill) from students group by student_id having group_concat(skill order by skill) ='python,sql'

[@Dataengineeringlearninghub]

This is Great Ankit, keep doing it

[@Ganeshay-09]

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

[@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'

[@ReinisRap]

I liked the no. 10 the best!

[@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

[@kailashpatro5768]

Worth Varma worth vere level

[@letsdomistakes]

WITH SQL AS (SELECT * FROM students where skill = 'SQL') , python as (select * from students where skill = 'python') , other as (select * from students where skill not in ('sql', 'python')) SELECT * FROM SQL s inner JOIN python p on s.student_id = p.student_id where s.student_id NOT IN (SELECT student_id from other); check this method, it's really helpful

[@vikramjitsingh6769]

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

[@SaiPrashanthThalanayar]

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

[@anirbanbiswas7624]

with cte as(select student_id,case when skill='python' then 1 when skill='sql' then 1 else 3 end as flag from students), cte2 as( select student_id,sum(flag) as total from cte group by student_id) select STUDENT_ID from cte2 where total=2 ---YOU MAY USE ANY NUMBER MORE THAN 3 TO SATISFY THE CONDITION

[@randomlyaj4735]

hello sir , my solution is - select student_id,skills from( select student_id,skills from (select student_id,group_concat(skill) as skills from students -- where skill='sql'or skill='python' group by student_id)a where skills not like '%tableau%')b where skills not like '%sql'

[@fog2640]

finished watching

[@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)

[@sarithakakarla-y1x]

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

[@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'

[@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%'

[@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?

[@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';

[@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

[@theraizadatalks14]

One more solution : Select student_id from students group by student_id having count(distinct skill) = 2 and sum(case when skill in ('sql','python') then 1 else 0 end ) = 2

[@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 )

[@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

[@deaspirant]

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.

[@as-youtuber6215]

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

[@Ganeshay-09]

AWS FREE COURSE |#devops #aws #abhishekveeramalla

[@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)

[@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

[@Mahi_RSV]

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

[@sz6618]

-- Method 6 -- co related sub query explantion Select student_id, count(*) as Total_skills from #students s1 where not exists (select student_id from #students s2 where s2.skill not in ('sql','python') and s1.student_id=s2.student_id) group by student_id having count(*) = 2 /* select student_id from #students s2 where s2.skill not in ('sql','python') Result s2 id : 1,4,5 Select student_id, count(*) as Total_skills from #students s1 group by student_id Result S1 id : 1,2,3,4,5 where not exists means ( result of s2 id : 1,4,5 will be excluded ) id = 2,3 left after putting having count(*) = 2 id 2 will be excluded output would be id = 3 */

[@pranavtaparia7711]

--find students who know only sql and python with cte as( select student_id, (case when skill = 'sql' then 1 else 0 end) as SQL , (case when skill = 'python' then 1 else 0 end) as Python, (case when skill = 'tableau' then 1 else 0 end) as Tableau from students ) ,jte as( select student_id, sum(sql) as "SQL", sum(python) as "Python", sum(tableau) as "Tableau" from cte group by student_id order by student_id ) select student_id from jte where "SQL" = 1 and "Python" = 1 and "Tableau"!=1 ;

[@ank_kumar12]

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

[@akhiladevangamath1277]

select student_id from students group by student_id having COUNT(DISTINCT skill)=2 AND SUM(skill NOT IN ('sql', 'python')) = 0;

[@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;

[@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";

[@RSJ1947]

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;

[@Demomail-m6w]

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?

[@dishantjain7789]

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