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

  • @Tech.S7
    @Tech.S7Ай бұрын

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

  • @Code-Con

    @Code-Con

    Ай бұрын

    yeah

  • @vinil9212
    @vinil921217 күн бұрын

    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;

  • @zizu7755
    @zizu77555 ай бұрын

    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

  • @Abhilash-s2g
    @Abhilash-s2g25 күн бұрын

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

Келесі