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
Пікірлер: 6
Are the two users 1 and 3 who are working in company 1? Does this correct?
@Code-Con
Ай бұрын
yeah
with cte as ( select company_id,user_id from company_users where language in ('English','German') group by user_id,company_id having count(language)=2 ) select company_id FROM cte group by company_id having count(user_id)>=2;
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
--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