https://www.youtube.com/watch?v=KWOZ9VoVFac&list=PLBTZqjSKn0IfuIqbMIqzS-waofsPHMS0E&index=14

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');

WITH
CASE WHEN

WITH qualified_teams AS
(
SELECT 
    teamID,COUNT(*) AS eligible_members
FROM
    ameriprise_LLC
WHERE
    Criteria1 = 'Y' AND Criteria2 = 'Y'
GROUP BY teamID
HAVING COUNT(1) >=2
)

SELECT alc.*,
CASE WHEN Criteria1 = 'Y' AND Criteria2 = 'Y' AND qt.teamID IS NOT NULL THEN 'Y' ELSE 'N' END AS Qualified_Flag
FROM ameriprise_llc alc 
LEFT JOIN qualified_teams qt 
ON alc.teamID = qt.teamID;

-- Approach 2 (Shorter)
SELECT *,
CASE WHEN Criteria1 = 'Y' AND Criteria2 = 'Y' AND SUM(CASE WHEN  Criteria1 = 'Y' AND Criteria2 = 'Y' THEN 1 ELSE 0 END) OVER (PARTITION BY teamID) >= 2 THEN 'Y' ELSE 'N' END AS Qual_flag
FROM ameriprise_llc