Capgemini SQL Interview Question | SQL Interview Question no 11 | Daily SQL Practice | Advanced SQL

[@themightyvk]

select distinct company_id,
user_id,
count(language) from company_user where language not in('Spanish') group by company_id, user_id having count(language)>=2

[@bhavindoshi2857]

Select company_id
From (
Select company_id, user_id, count (*)
From company_user
Where language in('English', 'German')
Group by 1,2
Having count (language)= 2
)
Group by company_id
Having count (distinct user_id) >=2

[@namangarg7023]

with cte as(
select company_id, user_id,count(user_id) over(partition by user_id) as total, language
from company_user
where language in('German','English')
group by company_id, user_id,language
)
select company_id, user_id, total, language
from cte
where total>=2
group by company_id, user_id, total, language

[@venkatkrishna1640]

select * from company_user where user_id % 2=1 and user_id!=7;

[@One_minute_vedio]

I think you can also do it using self join

[@Thedata.techclub]

Yes, there are multiple ways.

[@MOHAMMEDHAFEEZ-j7g]

SELECT USER_ID,COMPANY_ID,LANGUAGE FROM (
SELECT USER_ID,COMPANY_ID,LANGUAGE,COUNT(DISTINCT LANGUAGE) OVER (PARTITION BY COMPANY_ID,USER_ID) AS COUNT FROM Company_user
WHERE LANGUAGE IN ('German','English') )
WHERE COUNT=2

[@echodelta7680]

I often use the power of GROUP_CONCAT.
WITH cte AS
(
SELECT company_id, user_id, GROUP_CONCAT(language SEPARATOR ' ') AS lang_list
FROM company_user GROUP BY company_id, user_id
)
SELECT company_id, user_id, lang_list
FROM cte
WHERE lang_list IN ('German English', 'English German');
The CTE block itself returns the following output:
company_id | user_id | lang_list
```````````````````` ````````````````` ``````````````````````````````````````
1 1 German English
1 2 German
1 3 English German
1 4 English
2 5 German Spanish
.
.
.
. and so on.
The entire query returns:
company_id | user_id | lang_list
```````````````````` ````````````````` ``````````````````````````````````````
1 1 German English
1 3 English German
2 5 German English