Ameriprise LLC Company SQL Interview Problem | Data Analytics

We are going to solve an interesting SQL problem in this video. This problem was asked in Ameriprise LLC company in a SQL interview.
We are going to solve this question using 2 methods. Keep watching.
DDL and DML:
create table Ameriprise_LLC
(
teamID varchar(2),
memberID varchar(10),
Criteria1 varchar(1),
Criteria2 varchar(1)
);
insert into Ameriprise_LLC values
('T1','T1_mbr1','Y','Y'),
('T1','T1_mbr2','Y','Y'),
('T1','T1_mbr3','Y','Y'),
('T1','T1_mbr4','Y','Y'),
('T1','T1_mbr5','Y','N'),
('T2','T2_mbr1','Y','Y'),
('T2','T2_mbr2','Y','N'),
('T2','T2_mbr3','N','Y'),
('T2','T2_mbr4','N','N'),
('T2','T2_mbr5','N','N'),
('T3','T3_mbr1','Y','Y'),
('T3','T3_mbr2','Y','Y'),
('T3','T3_mbr3','N','Y'),
('T3','T3_mbr4','N','Y'),
('T3','T3_mbr5','Y','N');
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 #interview #analytics

[@vandanaK-mh9zo]

looking at the question seems very complex and tedious. But when you solve it step by step, it looks so easily understandable.

[@ankitbansal6]

The magic of SQL 😊

[@chandrakanthchandru1387]

God of SQL 🙏 , because of your such simple explanations in making to understand such complex questions and building the intuitions out of it , I am here successfully switched to the desired Data engineer role...All Thanks to you and Keep up the good work sir 🙏

[@ankitbansal6]

Many many congratulations. Keep rocking 🥳🥳

[@atharvabhangre9044]

You are the best sql teacher ankit god bless you

[@pottichandrasekharsai1015]

thank you for the table data in description. time saving to practice ourselves

[@kabiraggarwal-y7f]

SELECT * , case when (sum( CASE when Criteria1 = Criteria2 and criteria1 = 'Y' then 1 else 0 end) over(partition by teamid))>1 then 'Q' else 'NQ' end as Verdict FROM [TEST].[dbo].[Ameriprise_LLC] Proud Student of Yours . Thank You Sir

[@SonuPatel-hr2bg]

dude your sql is wrong.

[@Dhanushts-g7x]

@@SonuPatel-hr2bg dude check mines

[@shinejohnson777]

SELECT * , case when (sum( CASE when Criteria1 = Criteria2 and criteria1 = 'Y' then 1 else 0 end) over(partition by teamid))>1 and Criteria1 = Criteria2 and criteria1 = 'Y' then 'Q' else 'NQ' end as Verdict FROM [TEST].[dbo].[Ameriprise_LLC]

[@Datapassenger_prashant]

@@shinejohnson777 sorry to say , It's still not dynamically correct, Insert a row: ('T3','T3_mbr6','Y','Y') and it will give this Q where are there is no other team member to couple.

[@saurabhsomkuwar1276]

A twisted problem statement for this: If your team qualifies then even if you are not qualifying Criteria1 or Criteria2 (being part of that team) you should still be included. Answer for the above: select *, case when sum(case when Criteria1 = 'Y' and Criteria2 = 'Y' then 1 else 0 end) over(partition by teamID) >= 2 then 'Y' else 'N' end as qualifier from Ameriprise_LLC;

[@sahilummat8555]

Hello Sir Love your videos ;with cte as ( select * ,case when Criteria1='Y' and Criteria2='Y' then 'Y' else 'N' end as flag ,count(1)over(partition by teamID) as cnt from Ameriprise_LLC) select *, case when Criteria1='Y' and Criteria2='Y' and outpt>1 then 'Y'else 'N' end as qual_flag from( select *, count(case when flag='Y' then flag else null end)over(partition by teamID) as outpt from cte )a

[@nipunshetty9640]

Sir your just a Born Genius

[@ankitbansal6]

Thank you 😊

[@Dhanushts-g7x]

no one here is born genius he worked hard to become genius!!!

[@ArijitDatta-pt6wi]

Hi Ankit your playlist is revolutionary and is a lookup for interviews. here is my solution with cte1 as( select teamid,criteria1,criteria2, case when Criteria1='Y' and Criteria2='Y' then 'Yes' else 'No' end as flag from Ameriprise_LLC) ,cte2 as( select teamid,flag,count(1) as cnt from cte1 group by teamID,flag having flag='Yes') select *, case when teamID in (select teamid from cte2 where cnt>=2) and Criteria1='Y' and Criteria2='Y' then 'YES QUALIFIED' ELSE 'NOT QUALIFIED' end as Output from Ameriprise_LLC ;

[@shakthimaan007]

A suggestion: 13:00 instead of again calculating the formula, just call it in outer sub query so the solution looks easier SELECT * ,CASE WHEN CRITERIA1 = 'Y' AND CRITERIA2 = 'Y' AND QUALIFY_COUNT > 1 THEN 'Y' ELSE 'N' END AS QUALIFY_FLAG FROM ( SELECT * ,SUM(CASE WHEN CRITERIA1 = 'Y' AND CRITERIA2 = 'Y' THEN 1 ELSE 0 END) OVER (PARTITION BY TEAMID) AS QUALIFY_COUNT FROM COMPANY C )TEMP ;

[@Datapassenger_prashant]

Wowwww, What a Question!! I took a while to solve the problem, but so grateful to God and you that your teachings helped in solving the problem.. Note: I touch my ears first and say sorry to you, for pointing error in your query.. As it is not dynamically correct. if we add another row for TeamID 3 with all YY -> ('T3','T3_mbr6','Y','Y') it will also consider as Team Qualified. if such is qualified then Team2 member should qualify too.. Here is my sol, please provide your feedback. with cte as ( Select teamID, Criteria1, Criteria2, memberId, count(*) over(partition by teamID, Criteria1, Criteria2 order by count(*) rows between unbounded preceding and current row) as running_cnt ,count(*) over(partition by teamID, Criteria1, Criteria2 ) as couples from Ameriprise_LLC --where Criteria1 = 'Y' and Criteria2 = 'Y' group by teamID, Criteria1, Criteria2, memberID ) Select teamID, memberID, Criteria1, Criteria2, case When Criteria1 ='Y' and Criteria2 = 'Y' then case when couples%2 = 0 then 'Y' when couples > 1 and running_cnt

[@vinothkumars7421]

Very intersting one.

[@MixedUploader]

Ankit I am glad I was able to do with mentos jindagi solution with help of all your earlier explanations: with cte as (select *, count(case when Criteria1='Y' and Criteria2='Y' then memberID end) over(partition by teamID) as qualified_members from Ameriprise_LLC) select *, case when Criteria1='Y' and Criteria2='Y' and qualified_members>=2 then 'Y' else 'N' end as Output from cte

[@sanjeetsignh]

Hey Ankit. Big fan. Updated mentos solution a bit. with cte as ( select *, case when Criteria1 = 'Y' and Criteria2 = 'Y' then 'Y' else 'N' end as is_eligible, count(case when Criteria1 = 'Y' and Criteria2 = 'Y' then 1 end) over(partition by teamID) as eligible_member_cnt from Ameriprise_LLC ) select *, case when is_eligible = 'Y' and eligible_member_cnt >= 2 then 'Y' else 'N' end as output from cte

[@chetanphalak7192]

Amazing solution, Thanks Ankit

[@ankitbansal6]

Keep watching

[@p51_ishansingh49]

Using simple CTE and CASE with cte as (select *, case when Criteria1='Y' and Criteria2='Y' then 'Y' else 'N' end as isQualified from Ameriprise_LLC) select Ameriprise_LLC.*, case when teamID in (select teamID from cte where isQualified='Y' group by teamID having count(isQualified)>=2) then 'Y' else 'N' end as output from Ameriprise_LLC;

[@dhrumilgohel1655]

Awsome !!! my solution: select teamid, count(teamid) as team_count, sum(case when criteria1 = 'Y' and criteria2 = 'Y' then 1 else 0 end) as check_2 from ameriprise_LLC group by teamid HAVING (count(teamid) - sum(case when criteria1 = 'Y' and criteria2 = 'Y' then 1 else 0 end))

[@dnyaneshsatpute7734]

with c1 as (select *,case when criteria1='Y' and criteria2='Y' then 1 else 0 end as flag from Ameriprise_LLC) ,c2 as ( select teamid,case when sum(flag)>1 then 1 else 0 end as qualifyfl from c1 group by teamid ) select c1.* from c1 join c2 on c1.teamid=c2.teamid where flag=1 and qualifyfl=1

[@iamkiri_]

Thank you .. Here is my solution with cte as(select teamID,memberID, max(case when Criteria1 = 'Y' and Criteria2 = 'Y' THEN 1 else 0 end) as Criteria12Y from Ameriprise_LLC group by teamID, memberID) select A.teamID, A.memberID , case when A.total >= 2 then 'Y' else 'N' end as qualifiedflag from ( select *, sum(Criteria12Y) over (partition by teamID) as total from cte) A ;

[@akashsharma4769]

Thank you Ankit. You Made me Hero from Absolute Zero in SQL. I would hate CTE as i didn't know how easy this is unti you came into picture. Now my every query start with 'WITH' Keyword 😆 Please review this solution and let me know if this is a good logic. with c1 as ( select *, CASE WHEN Criteria1 = 'Y' and Criteria2 = 'Y' then 1 else 0 end as select_flag from Ameriprise_LLC) select teamId from c1 where select_flag = 1 group by teamId having count(select_flag)%2 = 0

[@ankitbansal6]

Glad to know . Keep going 💪

[@srinivashosur9964]

got the output as per the table shown in the video with CTE as( select * ,sum(case when Criteria1 = 'Y' and Criteria2 = 'Y' then 1 else 0 end) over(partition by teamID) as eligible_count from Ameriprise_LLC ) select teamID, memberID, Criteria1, Criteria2 ,(case when Criteria1 = 'Y' and Criteria2 = 'Y' and eligible_count >=2 then 'Y' else 'N' end) as just_for_reference ,(case when Criteria1 = 'Y' and Criteria2 = 'Y' then 'Y' else 'N' end) as output from CTE;

[@sumitrawat4103]

Mentos Zindagi Part 2- with cte as( select teamID,memberID,Criteria1,Criteria2, count(memberID) over(partition by teamID,Criteria1,Criteria2 ) rnk from Ameriprise_LLC) select teamID,memberID,Criteria1,Criteria2, case when rnk>=2 and Criteria1="Y" and Criteria2="Y" then "Y" else "N" end as output from cte group by 1,2,3,4 order by teamID,memberid,rnk ;

[@ankitdhar3692]

Adding another approach, Sir kindly review it. with te as( select *, case when Criteria1='Y' and Criteria2='Y' then 'Y' else 'N' end as Ref_output from Ameriprise_LLC), te2 as( select t.teamID,Ref_output, count(Ref_output) [counter] from te as t where Ref_output='Y' group by t.teamID,Ref_output having count(Ref_output)>=2) select a.teamID,a.criteria1,a.criteria2, case when Criteria1='Y' and Criteria2='Y' then 'Y' else 'N' end as Ref_output, case when counter is not null then 'Y' else 'N' end as Output from Ameriprise_LLC as a left join te2 as t on t.teamID=a.teamID;

[@KoushikT]

My Approach with A as (select *, case when Criteria1 = 'Y' and Criteria2='Y' then 1 else 0 end as flag from Ameriprise_LLC) select *, case when sum(flag) over (partition by teamID) >=2 and flag = 1 then 'Y' else 'N' end as Output from A

[@anamikajaiswal9423]

select *, case when Criteria1 = 'Y' and Criteria2 = 'Y' and count(1) over(partition by teamID,Criteria1,Criteria2) >=2 then 'Y' else 'N'end as output from Ameriprise_LLC

[@Datapassenger_prashant]

sorry to say , It's still not dynamically correct, Insert a row: ('T3','T3_mbr6','Y','Y') and it will give this Q where are there is no other team member to couple.

[@praveenbhandari2516]

Helpful! This will what is the best website for SQL practice could you please guide me as a beginner level.

[@saikatde6343]

Your second solution is too good. Below is my solution. with amp_cte AS ( select * from Ameriprise_LLC as a where a.criteria1 = 'Y' and a.Criteria2 = 'Y' ) select a.* , case when a.teamID = b.teamid and a.criteria1 = 'Y' and a.Criteria2 = 'Y' then 'Y' else 'N' end as just_for_ref from Ameriprise_LLC as a left JOIN (SELECT teamid from amp_cte as a group by teamid having COUNT(memberid) >= 2) as b on a.teamID = b.teamid;

[@ankitbansal6]

Thanks for posting 👏

[@satyamgoyal942]

With CTE_1 as ( Select a.*, sum(a.final) over(partition by a.teamID) as sum1 from ( Select *, case when Criteria1 = "Y" and Criteria2 = "Y" then 1 else 0 end as final from test1 )a ) Select teamID, memberID, Criteria1, Criteria2, Case when final = 1 and sum1 >= 2 then "Y" else "N" end as "qualifies for program" from CTE_1

[@krantiabinash6364]

Excellent

[@rk-ej9ep]

Awesome🎉

[@coolraviraj24]

mentos zindagi to gazab ka method tha....😮

[@jayselokar]

with cte as (select *, count() over(partition by teamid) as nos from Ameriprise_LLC where criteria1 = 'Y' and criteria2 = 'Y' ) select * from cte where nos >= 2

[@mohammedriyaz-g4s]

with cte_1 as(select TeamID,count(both_check)as both_check_cnt from (select *,case when Criteria1 ='Y' and Criteria2='Y' then 'Y' else 'N' end as both_check from Ameriprise_LLC) where both_check='Y' group by teamID) ,cte2 as (select *,case when Criteria1 ='Y' and Criteria2='Y' then 'Y' else 'N' end as both_check from Ameriprise_LLC) select teamID,memberID,Criteria1,Criteria2,both_check,case when both_check ='Y'and both_check_cnt >=2 then 'passed' else 'failed' end as final_selection from (select b.*,a.both_check_cnt from cte_1 a inner join cte2 b on a.TeamID=b.TeamID)

[@vaibhavpandey6245]

with cte as (select * , lead(teamID , 1) over (order by teamID) as leadt from Ameriprise_LLC where Criteria1 = 'Y' and Criteria2 = 'Y') select distinct(teamID) from cte where teamID = leadt

[@hairavyadav6579]

Hello sir my approach with cte as(select * ,if(Criteria1 ="y" and Criteria2 ="y","y","N") as cnd from ameriprise),cte1 as (select * ,count(if(cnd="y",1,null)) over(partition by teamID) as rnk from cte) select *,case when cnd ="y" and rnk>=2 then "y" else "N" end as qualified_flag from cte1;

[@dhirajparikarmagupta6325]

with cte as ( select teamid , sum(case when concat(criteria1, criteria2) = 'YY' then 1 else 0 end) as flag from ameriprise_llc_17 group by teamid having sum(case when concat(criteria1, criteria2) = 'YY' then 1 else 0 end) >= 2 order by teamid ) select * , (case when concat(criteria1, criteria2) = 'YY' and teamid in(select teamid from cte) then 'Y' else 'N' end )as output from ameriprise_llc_17

[@Ankitatewary-q6w]

with cte1 as (select *, (case when (Criteria1='Y' and Criteria2='Y') then 'Y' else 'N' end) as level1_selection from Ameriprise_LLC), cte2 as( select *, case when level1_selection='Y' then 1 else 0 end as flag from cte1) select cte2.teamID, cte2.memberID, cte2.Criteria1, cte2.criteria2, cte2.level1_selection as Qualifies_for_program, case when (sum(flag) over (partition by teamID))>=2 and level1_selection='Y' then 'Y' else 'N' end as output from cte2;

[@ujjwalvarshney3188]

select teamid from ( select * ,case when criteria1 ='Y' and criteria2 ='Y' then 'Y' else 'N' end as Output from Ameriprise_LLC) where Output = 'Y' group by 1 having count(Output)>=2

[@jyotidixit1121]

Can u plz Refer Ur one video of sql that would be enough as a fresher to crack interview for data analyst or data engineering for business analyst. Plz reply

[@ankitbansal6]

kzbin.info/www/bejne/f6econWwYsisjKM

[@joerokcz]

I wrote lot of lines 🤣🤣🤣🤣🤣🤣🤣🤣 but anyway Thanks Ankit, for your videos only I m getting idea on real inetervew qustions. WITH qualifiers AS ( SELECT teamID, Criteria1, Criteria2, memberID, COUNT(*) OVER (PARTITION BY teamID, Criteria1, Criteria2) AS number_of_qualifiers FROM Ameriprise_LLC WHERE Criteria1 = 'Y' AND Criteria2 = 'Y' ), non_qualifiers AS ( SELECT teamID, Criteria1, Criteria2, memberID, COUNT(*) OVER (PARTITION BY teamID, Criteria1, Criteria2) AS number_of_qualifiers FROM Ameriprise_LLC WHERE Criteria1 = 'N' OR Criteria2 = 'N' ), total_qualifiers as ( SELECT teamID, number_of_qualifiers, Criteria1, Criteria2, memberID FROM qualifiers UNION SELECT teamID, number_of_qualifiers, Criteria1, Criteria2, memberID FROM non_qualifiers ) select teamID, Criteria1, Criteria2, memberID,number_of_qualifiers, CASE when Criteria1 = 'Y' AND Criteria2 = 'Y' then 'Y' ELSE 'N' END as qualifies_for_program from total_qualifiers

[@blitzhope]

how much time did yuo take to solve this??

[@sagargudla1362]

Amazing ❤

[@parmoddhiman678]

mentos jindgi in mysql with cte as (select * , sum(case when Criteria1='Y' and Criteria2='Y' then 1 else 0 end) over(partition by teamid) as team from llc) select teamID, memberID, Criteria1, Criteria2, case when Criteria1='Y' and Criteria2='Y' and team>=2 then 'Y' else 'N' end as qualify_team from cte

[@praveensinghrathore4542]

My solution in MYSQL: with cte as (select teamID, sum(case when criteria1 = 'Y' and criteria2 = 'Y' then 1 else 0 end) count_yes from Ameriprise_LLC group by 1 having sum(case when criteria1 = 'Y' and criteria2 = 'Y' then 1 else 0 end) >= 2) select a.* from cte c join Ameriprise_LLC a on a.teamID = c.teamID where criteria1 = 'Y' and criteria2 = 'Y';

[@NaveenKumar-lz6xi]

select teamID, memberID, Criteria1, Criteria2,qualifies from ( select *, count(1) over(partition by teamId, qualifies) as ct from ( select *, case when Criteria1 = 'Y' and Criteria2 = 'Y' then 'Y' else 'N' end as qualifies from Ameriprise_LLC)A)B where ct > 1 and qualifies = 'Y'

[@anish_bhateja]

my solution: with cte as (select *,case when criteria1= 'Y' and criteria2 ='Y' then 'Y' else 'N' end as qualifies_for_program from Ameriprise_LLC), cte2 as(select teamid,memberid,'Y' as output from cte where teamid in (select teamid from cte group by 1 having count(case when qualifies_for_program = 'Y' then 1 end) >=2) and qualifies_for_program = 'Y') select a.teamid,a.memberid,criteria1,criteria2,qualifies_for_program,coalesce(output,'N') as output from cte a left join cte2 b on a.teamid = b.teamid and a.memberid = b.memberid; or with cte as (select *,case when criteria1= 'Y' and criteria2 ='Y' then 'Y' else 'N' end as qualifies_for_program from Ameriprise_LLC), cte2 as (select *,sum(case when qualifies_for_program = 'Y' then 1 end) over(partition by teamid) as no_of_qualified_candidates from cte order by teamid) select teamid,memberid,criteria1,criteria2,qualifies_for_program,case when qualifies_for_program='Y' and no_of_qualified_candidates>=2 then 'Y' else 'N' end as output from cte2;

[@shyamyadav9233]

Uploaded_date --------------- 09-01-2023 05:27:20 PM --------------- If uploaded_date is greater than 5pm then consider next morning date and time start from 10 am How can I do Ankit ..?

[@ankitbansal6]

Using the date part check if time is greater than 5, if yes then add one day to the date and time as 10am

[@sreekanthk9956]

my solution: with cte as ( select * ,case when Criteria1='Y' and Criteria2='Y' then 1 else null end as con_chk from Ameriprise_LLC ) ,cte2 as (select teamID,sum(con_chk) as count_of_team from cte group by teamID ),cte3 as (select cte.*,count_of_team from cte,cte2 where cte.teamID=cte2.teamID) select cte3.teamID,cte3.memberID,cte3.Criteria1,cte3.Criteria2 ,case when cte3.con_chk is not null and cte3.count_of_team >=2 then 'Y' else 'N' end as 'Justice_for_reference' ,case when cte3.con_chk is not null and cte3.count_of_team >=2 then 'Y' else 'N' end as 'Output' from cte3

[@meghanahiremath1347]

Hi Ankith Do you take any pyspark and databricks class?

[@ankitbansal6]

Not yet

[@praveenkumarrai101]

with tempcte as ( select *, case when Criteria1 = 'y' and Criteria2 = 'y' then 1 else 0 end as flag from Ameriprise_LLC ), tempabc as ( select *, sum(flag) over(partition by teamid) as sumflag from tempcte ) select * from tempabc where flag = 1 and sumflag >=2

[@rajeshwarrajigare210]

select teamID, sum(case when Criteria1 = "Y" and Criteria2 = "Y" then 1 else 0 end) as cnt from Ameriprise_LLC group by teamID having sum(case when Criteria1 = "Y" and Criteria2 = "Y" then 1 else 0 end) >= 2;

[@PriyankaReddy-s4e]

with cte as (select al.*, sum(case when Criteria1='Y' and Criteria2='Y' then 1 else 0 end) over (partition by teamID) as eligible_criteria from Ameriprise_LLC al) select cte.*, case when eligible_criteria>=2 then 'Y' else 'N' end as Output from cte

[@kumardilip5255]

Thank you sir...❤

[@ankitbansal6]

Welcome

[@bikitamuli]

with cte as( select teamID , case when sum(case when (upper(Criteria1)='Y') and (Upper(Criteria2)='Y') then 1 else 0 end) >=2 then 'Y' else 'N' end final_output from Ameriprise_LLC group by teamID) select a.* , case when (upper(Criteria1)='Y') and (Upper(Criteria2)='Y') then isnull(b.final_output,'N') else 'N' end from Ameriprise_LLC a left join cte b on a.teamID=b.teamID and b.final_output='Y'

[@pavangsk8404]

ankit sir can you pls make videos on sas

[@karangupta_DE]

with cte as ( select *, count(case when criteria1 = 'Y' and criteria2 = 'Y' then memberid else null end)over(partition by teamid) as number_qualified from ameriprise_llc ) select teamid, memberid, criteria1, criteria2, case when criteria1 = 'Y' and criteria2 = 'Y' and number_qualified >= 2 then 'Y' else 'N' end as is_qualified from cte;

[@Mayank-jw9yy]

I have done small change in first method and it is acceptable to reach to below answer- with eligible_count as( SELECT teamID, count(teamID) as no_of_count FROM Ameriprise_LLC where Criteria1 = 'Y' and Criteria2 = 'Y' group by teamID ) SELECT al.*, eligible_count.*, CASE WHEN (Criteria1 = 'Y' and Criteria2 = 'Y') and no_of_count >= 2 Then 'Y' ELSE 'N' END AS qualified_flag FROM Ameriprise_LLC al LEFT JOIN eligible_count ON al.teamID = eligible_count.teamID

[@rahulmehla2014]

my approach: with cte as ( select *, case when c12 = "YY" then "Y" else "N" end as output1, sum(case when c12 = "YY" THEN 1 else 0 end) over(partition by teamid) as cnt from( select *, concat(criteria1,criteria2) as c12 from Ameriprise_LLC) new_table) select teamid,memberid,criteria1,criteria2,case when cnt >=2 then output1 else "N" end as output from cte

[@ashwinkadam4633]

with main as (select teamid, memberid, criteria1, criteria2, count(*) over(partition by teamid, criteria1, criteria2) as cnt from Ameriprise_LLC as a) select teamid, memberid, criteria1, criteria2, (case when cnt > 1 and criteria1 = 'Y' and criteria2 = 'Y' then 'Y' else 'N' end) as flag from main

[@praveenkumarrai101]

Hi, for how much experience this question can be asked

[@ankitbansal6]

3+

[@ankitsaxena565]

Sir aap ragular video upload Kiya kre

[@ankitbansal6]

Sure

[@sankuM]

Loved the 'Mentos Zindagi' @ankitbansal6....!!! 🙌🏻🙌🏻🙌🏻🙌🏻🙌🏻 When I read the problem statement, window function is the first thing that came to my mind...!!

[@adityaroxx1]

Sir, just want to know while solving this problem how much time did u take to think about the solution . AS IN INTERVIEW we barely have 10 mins to think n write about solution. 😂

[@ankitbansal6]

For me it doesn't take any time. You need to practice consistently..

[@Tusharchitrakar]

with cte1 as ( select teamID, count(if(criteria1='Y' and criteria2='Y',1,null)) as accepted_number from interview_test_18 group by teamid ), cte2 as (select * from cte1 join interview_test_18 using(teamID)) select teamID, memberID, Criteria1, Criteria2, (if(accepted_number>=2 and criteria1='Y' and criteria2='Y', 'Y','N')) as Output from cte2;

[@bhaskar9781]

Mentos zindagi was good 😊

[@aamirsohail2576]

with cte as (select teamID,memberID,Criteria1,Criteria2, case when Criteria1='Y' and Criteria2 = 'Y' then 1 else 0 end as is_qualify from Ameriprise_LLC), ab as (select *,sum(is_qualify) over(partition by teamID) as sum1 from cte) select teamId,memberID,Criteria1,Criteria2, case when is_qualify = 1 and sum1 >1 then 'Y' else 'N' end as well_qualify from ab

[@AravindKumar-es4nd]

select L.*,case when f.flag = 'Y' and f.cnt_Y_N is not null then 'Y' else 'N' end as Output from Ameriprise_LLC L left join (select teamID,flag,count(flag) cnt_Y_N from( select teamID,case when Criteria1 = 'Y' and Criteria2 = 'Y' then 'Y' else 'N' end as flag from Ameriprise_LLC)KK where flag = 'Y' group by teamID,flag having count(flag)>1) F on L.teamid = F.TeamId

[@saqibhussain9555]

This is my solution with cte as( select teamid,count(*) as total_quali from ameriprise_llc where criteria1 ="Y" and criteria2="Y" group by teamid), cte2 as( select a.*,cte.total_quali from ameriprise_llc as a join cte on a.teamid = cte.teamid) select teamid,memberid,criteria1,criteria2, case when criteria1= 'Y' and criteria2= 'Y' and total_quali >1 then "Y" else "N" end as "Qualified" from cte2

[@zaravind4293]

Hi sir Please find my solution below. with a as( select a.*,case when criteria1='Y' and criteria2='Y' then 'Y'else 'N' end op from Ameriprise_LLC a) select a.*,case when ( sum(case when op='Y' then 1 else 0 end ) over(partition by teamid))>1 then 'Y' else 'N' end output from a;

[@kasmitharam982]

WITH CTE1 AS (SELECT *, CASE WHEN Criteria1 = 'Y' AND Criteria2 = 'Y' THEN 1 ELSE 0 END AS individual_flag FROM Ameriprise_LLC), CTE2 AS(SELECT *, SUM(individual_flag) OVER (partition by teamID) AS team_level_sum FROM CTE1), CTE3 AS (SELECT *, CASE WHEN team_level_sum >= 2 THEN 1 ELSE 0 END AS team_level_flag FROM CTE2) SELECT * FROM CTE3 WHERE individual_flag = 1 and team_level_flag = 1

[@grzegorzko55]

WITH cte AS( SELECT teamid , memberid , criteria1 , criteria2 , CASE WHEN criteria1 = 'Y' AND criteria2 = 'Y' THEN 'YES' ELSE 'NO' END AS TEST , SUM(CASE WHEN criteria1 = 'Y' AND criteria2 = 'Y' THEN 1 ELSE 0 END ) OVER(PARTITION BY teamid) AS TEST2 FROM Ameriprise_LLC WHERE 1=1 ), cte2 AS( SELECT teamid , memberid , criteria1 , criteria2 , test FROM cte WHERE 1=1 AND test2 >=2 ), summary AS( SELECT * FROM cte2 UNION ALL SELECT teamid , memberid , criteria1 , criteria2 , 'NO' AS test FROM Ameriprise_LLC WHERE teamid NOT IN (SELECT teamid FROM cte2) ) SELECT * FROM summary ORDER BY teamid

[@SonuPatel-hr2bg]

select teamid, memberid, criteria1, criteria2, case when criteria1 = 'Y' and criteria2 = 'Y' and (sum(case when a.criteria1 = 'Y' and a.criteria2 = 'Y' then 1 else 0 end) over(partition by a.teamid) > 1) then 'Y' else 'N' end as qualify from Ameriprise_LLC a;

[@throughmyglasses9241]

with CTE AS ( select teamID,count(1) as cnt from Ameriprise_LLC where Criteria1='Y' and Criteria2='Y' group by teamID) select *,CASE WHEN Criteria1='Y' and Criteria2='Y' and cnt >=2 THEN "Y" ELSE "N" END AS output from Ameriprise_LLC a inner join CTE on a.teamID=CTE.teamID;

[@shwetasaini6892]

My Version - select teamID, memberID, Criteria1, Criteria2, members_qualified, case when Criteria1 = 'Y' and Criteria2 = 'Y' and members_qualified >= 2 then 'Y' else 'N' end as Qualified_Flag from ( select *, sum(member_qualify) over(partition by teamID) as members_qualified from ( select *, case when Criteria1 = 'Y' and Criteria2 = 'Y' then 1 else 0 end as member_qualify from Ameriprise_LLC ) as x ) as a

[@needtobechanged]

Ankit - Thanks for all your great work of sharing the knowledge. I am one of your subscribers. I have got 3 offers for Data engineering. 1. Wipro 2. Accenture and 3. Coforge(NIIT Technologies). Please suggest me a company to choose. My joining date is 1st June. Please help

[@ankitbansal6]

Accenture or coforge

[@needtobechanged]

@@ankitbansal6 thank you for the reply. Do you think Wipro is not a right choice over Coforge ?. Is coforge better than Wipro

[@needtobechanged]

BTW I have also taken up your NamasteSQL course and learnt a lot. Thank you very much

[@SaiPriyaEleti]

@@needtobechanged by the way which company have you joined finally.? (curiosity) 😄

[@needtobechanged]

@@SaiPriyaEleti I have joined Wipro 😀

[@monasanthosh9208]

Select Teamid,MemberID,Criteria1,Criteria2, Case When CNt>=2 then "Y" else "N" end as Output from (Select *,sum(Flag) over (Partition by Teamid Range Between Unbounded Preceding and unbounded Following) as CNt from (Select *,Case When Criteria1="Y" and Criteria2="Y" then 1 else 0 end as Flag from Ameriprise_LLC)N)N;

[@nachiketpalsodkar4356]

Another SImpler WAy!!! ; WITH cte as ( select * , CASE WHEN LOWER(Criteria1) = 'y' AND LOWER(Criteria2) = 'y' then 1 END as SAME_VAL , COUNT(CASE WHEN LOWER(Criteria1) = 'y' AND LOWER(Criteria2) = 'y' then 1 END) over(partition by teamID order by teamID) as CNT from Ameriprise_LLC ) select * , CASE WHEN CNT >= 2 AND SAME_VAL = 1 then 'Y' ELSE 'N' END as QUALIFIED_FLAG from cte

[@arjundev4908]

WITH CTE AS(SELECT *, CASE WHEN CRITERIA1 = 'Y' AND CRITERIA2 = 'Y' THEN 1 ELSE 0 END AS STATUS FROM LLC),DUMP AS( SELECT *, SUM(STATUS)OVER(PARTITION BY TEAMID) AS CNT FROM CTE) SELECT *, CASE WHEN CNT >= 2 AND (CRITERIA1 = 'Y' AND CRITERIA2 = 'Y') THEN 'Y' ELSE 'N' END AS OUTPUT FROM DUMP;

[@KumarPriyansh-h7u]

SELECT * , CASE WHEN Criteria1 ="Y" AND Criteria2="Y" AND teamID IN( SELECT teamID FROM( SELECT teamID ,CASE WHEN Criteria1 ="Y" AND Criteria2="Y" THEN 1 ELSE 0 END AS BothY FROM Ameriprise_LLC) AS temp_table_1 GROUP BY teamID HAVING SUM(BothY)>1) THEN "Y" ELSE "N" END AS qualified FROM Ameriprise_LLC;

[@DevN9007]

Dilip shukla ka number public kar diya

[@ankitbansal6]

Haha

[@Dhanushts-g7x]

--(anyone intrested can check this out my oun solution it will be readable and easy to understand) with cte1 as (select *,sum(q) over(partition by teamid) qualified_mem from (SELECT *, CASE WHEN criteria1="Y" and criteria2="Y" THEN 1 ELSE 0 END AS q from ameriprise_llc) a ) select teamid,memberid,criteria1,criteria2,qualified_mem,if(q=1 and qualified_mem>=2,"Y","N") qualified_flag from cte1

[@ashishroshan5946]

This is my solution: With cte as ( Select *, CASE WHEN criteria1 = 'Y' and Criteria2 = 'Y' THEN 'Y' ELSE 'N' END as person_criteria from Ameriprise_LLC ), cte1 as ( Select teamid from cte where person_criteria = 'Y' group by 1 having COUNT(memberid) >1 ) Select a.*, CASE WHEN a.teamid = b.teamid AND person_criteria = 'Y' THEN 'Y' ELSE 'N' END as final_results from cte a LEFT JOIN cte1 b ON a.teamid = b.teamid

[@shikamaru742-]

using joins and cte with M as (select teamID , memberID ,Criteria1,Criteria2 from Ameriprise_LLC ) , N as (select * , ROW_NUMBER()over(partition by teamID order by teamID)as rn from M where Criteria1 = 'Y' and Criteria2 = 'Y' ) , T as (select teamID , count(teamID) as cn from N group by teamID having count(teamID)>1 ) select * from N inner join T on N.teamID = T.teamID

[@ankitbansal6]

Good attempt. Can be simplified 😊

[@tahakhalid324]

Hi Ankit here is my solution: with new_criteria as ( SELECT teamid, memberid, Criteria1, Criteria2, case when Criteria1 ='Y' and Criteria2 = 'Y' then 'Y' Else 'N' end as Criteria3 from Ameriprise_LLC ) , groups_per_criteria as ( SELECT teamid, count(Criteria3) as counts from new_criteria where Criteria3 = 'Y' group by teamid ) SELECT al.teamid, al.memberid, al.Criteria1, al.Criteria2, case when al.Criteria3 = 'Y' and gpc.counts < 2 then 'N' else Criteria3 end final_criteria from new_criteria al join groups_per_criteria gpc on al.teamid=gpc.teamid

[@DeepakBharti-h9l]

My soloution : WITH cte AS ( SELECT teamID, CASE WHEN Criteria1 = 'Y' AND Criteria2 = 'Y' THEN 1 ELSE 0 END AS pass FROM Ameriprise_LLC ) SELECT a.*,b.*, CASE WHEN Criteria1 = 'Y' AND Criteria2 = 'Y' THEN 'Eligable' ELSE 'Not' END AS Eligablity FROM Ameriprise_LLC a left JOIN ( SELECT teamID FROM cte GROUP BY teamID HAVING SUM(pass) >= 2 ) b ON a.teamID = b.teamID

[@shrutikukdeja8743]

with cte1 as ( select teamID,MemberID,Criteria1,Criteria2, case when Criteria1='Y' and Criteria2 ='Y' then 'Y' ELSE 'N' END AS [Qualifies_for_program] from Ameriprise_LLC ) select c1.*,case when s1.count >=2 and c1.[Qualifies_for_program]='Y' then 'Y' else 'N' end as [output] from cte1 c1 left join (select teamID,[Qualifies_for_program],count(1) as count from cte1 group by teamID,[Qualifies_for_program] having [Qualifies_for_program]='Y') s1 on c1.teamID = s1.teamID

[@subhojitchatterjee6312]

My solution in My SQL !!! SELECT TEAMID,MEMBERID,CRITERIA1,CRITERIA2,CASE WHEN CRITERIA1 ="Y" AND CRITERIA2="Y" AND NUM_QUALIFIED>=2 THEN "Y" ELSE "N" END AS OUTPUT FROM (SELECT *,COUNT(CASE WHEN CRITERIA1 ="Y" AND CRITERIA2="Y" THEN "Y" END) OVER(PARTITION BY TEAMID) AS NUM_QUALIFIED FROM AMERIPRISE_LLC) T1

[@Gokulsai18]

WITH SP AS( select teamID,sum(points) as sup from ( select *,case when Criteria1='Y' AND Criteria2='Y' then 1 else 0 end as points from Ameriprise_LLC )A GROUP BY teamID) select A.teamID,A.memberID,A.Criteria1,A.Criteria2,case when Criteria1='Y' AND Criteria2='Y' AND sup>=2 then 'Y' else 'N' end as qualified from Ameriprise_LLC A inner join SP B ON A.teamID=B.teamID

[@guptaaniket4155]

WITH res AS( SELECT teamID FROM Ameriprise_LLC WHERE (Criteria1 = 'Y' AND Criteria2 = 'Y') GROUP BY teamID HAVING COUNT(memberID) > 1 ) SELECT *, CASE WHEN teamID in (SELECT * FROM res) THEN 'Y' ELSE 'N' END AS output FROM Ameriprise_LLC

[@HARSHRAJ-wz2rp]

with cte as( SELECT teamID, memberID, Criteria1, Criteria2 FROM Ameriprise_LLC WHERE teamID IN ( SELECT teamID FROM Ameriprise_LLC WHERE Criteria1 = 'Y' AND Criteria2 = 'Y' GROUP BY teamID HAVING COUNT(*) >= 2 ) ), cte1 as( select cte.*, case when Criteria1='Y' and Criteria2='Y' THEN 'Y' ELSE 'N' END AS result FROM cte ), cte2 as( SELECT teamID, memberID, Criteria1, Criteria2 FROM Ameriprise_LLC WHERE teamID NOT IN ( SELECT teamID FROM Ameriprise_LLC WHERE Criteria1 = 'Y' AND Criteria2 = 'Y' GROUP BY teamID HAVING COUNT(*) >= 2 ) ), cte3 as( select cte1.* FROM cte1 UNION select cte2.*,'N' as result FROM cte2 ) select cte3.* FROM cte3 ORDER BY teamID;

[@Dataengineeringlearninghub]

i would say below is better version SELECT * ,CASE WHEN Criteria1 = 'Y' AND Criteria2 = 'Y' AND ( SELECT count(*) FROM Ameriprise_LLC t WHERE t.teamid = Ameriprise_LLC.teamid AND t.Criteria1 = 'Y' AND t.Criteria2 = 'Y' ) >= 2 THEN 'Y' ELSE 'N' END AS Qualifies_for_program

[@goodisnotgoodenough_]

We can also get the answer with the help of CTE & sub-queries as shown below (without using JOINS & Window Functions) : WITH cte1 AS (SELECT teamID FROM ameriprise_llc WHERE Criteria1 = "Y" AND Criteria2 = "Y" GROUP BY teamID HAVING COUNT(*) > 1) SELECT *, CASE WHEN Criteria1 = "Y" AND Criteria2 = "Y" AND teamID IN (SELECT teamID FROM cte1) THEN "Y" ELSE "N" END AS output FROM ameriprise_llc;

[@arpan8076]

WITH CTE1 AS( SELECT *, RANK() OVER (PARTITION BY A.TEAMID ORDER BY A.MEMBERID)AS RNK FROM A_LLC AS A WHERE A.Criteria1='Y' AND A.Criteria2='Y' ) SELECT * FROM ( SELECT *, SUM(RNK)OVER (PARTITION BY TEAMID) AS XX FROM CTE1 GROUP BY CTE1.teamID,CTE1.memberID,CTE1.Criteria1,CTE1.Criteria2,CTE1.RNK ) X1 WHERE X1.XX>=3

[@yashsaini9494]

WITH firstCriteria AS ( SELECT *, CASE WHEN Criteria1 = 'Y' AND Criteria2 = 'Y' THEN 'Y' ELSE 'N' END AS individualQualification FROM Ameriprise_LLC ) , teamLevelCriteria AS ( SELECT teamID, COUNT(CASE WHEN individualQualification = 'Y' THEN 1 END) AS count_y FROM firstCriteria GROUP BY teamID ) , joiningCriteria AS ( SELECT firstCriteria.*, teamLevelCriteria.count_y FROM firstCriteria INNER JOIN teamLevelCriteria on (firstCriteria.teamID = teamLevelCriteria.teamID) ) SELECT *, CASE WHEN individualQualification='Y' AND count_y >=2 THEN 'Y' ELSE 'N' END AS OUTPUT FROM joiningCriteria