Google Interview Question | SQL Intermediate Question 6

Question -
Find the company who have alteast 2 users who speaks both English and German.
Script:
create table company_users
(
company_id int,
user_id int,
language varchar(20)
);
insert into company_users values (1,1,'English')
,(1,1,'German')
,(1,2,'English')
,(1,3,'German')
,(1,3,'English')
,(1,4,'English')
,(2,5,'English')
,(2,5,'German')
,(2,5,'Spanish')
,(2,6,'German')
,(2,6,'Spanish')
,(2,7,'English');
select * from company_users;
#sql #dataanalytics #googleinterview #google #sqldeveloper #dataanalyst #interviewquestions

[@Abhilash-s2g]

--Find the company who have alteast 2 users who speaks both English and German.
with cte as (
select company_id,USER_ID,language,
DENSE_RANK()over(partition by USER_ID order by language)as rn,
count(language)over(partition by USER_ID)as cnt
from Google_Intermediate_Interview
where language in ('English', 'German')
)
select company_id,USER_ID,language from cte
where cnt >= 2

[@Tech.S7]

Are the two users 1 and 3 who are working in company 1?
Does this correct?

[@Code-Con]

yeah

[@zizu7755]

WITH cte AS(
SELECT *, COUNT (user_id) OVER (PARTITION BY company_id, user_id ORDER BY company_id) AS cnt
FROM company_users
WHERE language IN ('English', 'German')
)
SELECT company_id, COUNT (DISTINCT user_id) AS bilingual_cnt
FROM cte
WHERE cnt = 2
GROUP BY company_id
HAVING COUNT (DISTINCT user_id) >= 2