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

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

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

[@kailashpatro5768]

Worth Varma worth vere level

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

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

[@Dataengineeringlearninghub]

This is Great Ankit, keep doing it

[@ReinisRap]

I liked the no. 10 the best!

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

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

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

[@SaiPrashanthThalanayar]

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

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

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

[@Ganeshay-09]

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

[@ankitbansal6]

You are most welcome

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

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

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

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

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

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

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

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

[@vikramjitsingh6769]

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

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

[@fog2640]

finished watching

[@sarithakakarla-y1x]

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

[@ank_kumar12]

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

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

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

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

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

[@akhiladevangamath1277]

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

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

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

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

[@as-youtuber6215]

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

[@Ganeshay-09]

AWS FREE COURSE |#devops #aws #abhishekveeramalla

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

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

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

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

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