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

In this video, we tackle a common SQL interview question: How do we find employees who speak both German and English? We’ll walk through a practical scenario where we have an Employee Languages table and must filter employees based on their languages.
You’ll learn the following:
How to use GROUP BY and HAVING in SQL.
How to filter results based on multiple conditions.
A step-by-step breakdown of the query logic to solve real-world problems.
This type of question is often asked in SQL interviews, and mastering it will give you an edge in data-related roles!
If you found this video helpful, don't forget to like, comment, and subscribe for more SQL interview tips and tutorials.
#SQL #SQLInterviewQuestions #LearnSQL #DataAnalysis #SQLQuery #InterviewTips #Database #Programming
.
.
.
.
.
-------------Table SCript-----------------------------
CREATE TABLE Company_user (
Company_Id VARCHAR(512),
User_Id INT,
Language VARCHAR(512)
);
INSERT INTO Company_user (Company_Id, User_Id, Language) VALUES ('1', '1', 'German');
INSERT INTO Company_user (Company_Id, User_Id, Language) VALUES ('1', '1', 'English');
INSERT INTO Company_user (Company_Id, User_Id, Language) VALUES ('1', '2', 'German');
INSERT INTO Company_user (Company_Id, User_Id, Language) VALUES ('1', '3', 'English');
INSERT INTO Company_user (Company_Id, User_Id, Language) VALUES ('1', '3', 'German');
INSERT INTO Company_user (Company_Id, User_Id, Language) VALUES ('1', '4', 'English');
INSERT INTO Company_user (Company_Id, User_Id, Language) VALUES ('2', '5', 'German');
INSERT INTO Company_user (Company_Id, User_Id, Language) VALUES ('2', '5', 'English');
INSERT INTO Company_user (Company_Id, User_Id, Language) VALUES ('2', '6', 'Spanish');
INSERT INTO Company_user (Company_Id, User_Id, Language) VALUES ('2', '6', 'English');
INSERT INTO Company_user (Company_Id, User_Id, Language) VALUES ('2', '7', 'English');

[@SanjoliMehrotra]

with cte as
(Select *, row_number()over(partition by user_id) as user_rn
from company_user)
Select company_id, user_id,language
from cte
where user_rn>=2
and language in ('English','German')

[@Savenature635]

select user_id,company_id,language
from capgemini_user
where language in ('English','German')
and user_id in (
select user_id
from capgemini_user
where language in ('English','German')
group by company_id,user_id having count(user_id)>1);

[@kshetrabasiMohanta-q4e]

select user_id,count(language) from company_user
where language in('English','German')
group by user_id
having count(language)=2

[@SK-kg6en]

This is exactly what I thought too. Since the data is less, you were able to put in as not in Spanish. But in a large table, its not easy to filter the data we dont need. The best one is to put in the filter on the data we need.

[@sachinn5503]

WITH CTE AS(
SELECT *,
count(USER_ID) OVER(PARTITION BY USER_ID) ATLEAST_2
FROM COMPANY_USER
WHERE LANGUAGE IN( 'ENGLISH' ,'GERMAN'))
SELECT * FROM CTE
WHERE G=2;

[@MathanRJ-p5c]

with temp as
(
select user_id
from Company_user
where Language in ("English","German")
group by user_id
having count(Language) = 2
)
select company_id
from Company_user
where user_id in (select user_id from temp)
group by company_id
having count(distinct(user_id)) >= 2

[@bhupeshsolanki289]

with cte as (
select Company_Id, User_Id, group_concat(Language) as grp from Company_user group by Company_Id, User_Id )
select * from cte where grp = 'German,English' or grp = 'English,German';

[@saijaswanth3036]

WITH cte AS (
SELECT company_id , user_id,
COUNT(CASE WHEN language IN ('german' , 'english') THEN 1 ELSE NULL END) AS count_
FROM company_user
GROUP BY company_id , user_id )
SELECT a.user_id , b.company_id , b.language
FROM cte as a , company_user as b
WHERE a.user_id = b.user_id
and a.count_ = 2

[@beginner6667]

This is wrong you asked company should have atleast two users where as your company id 2 in output has only one user

[@hairavyadav6579]

Same thing i also get confused

[@ilpaolotopleggend6663]

Yes😂

[@hairavyadav6579]

select Company_Id from (select Company_Id,user_id from company_user where Language in("English","German")
group by Company_Id,user_id having count(distinct Language)=2) sal group by Company_Id having count(user_id) >=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

[@One_minute_vedio]

I think you can also do it using self join

[@Thedata.techclub]

Yes, there are multiple ways.

[@entertainmenthub5066]

select a.User_Id,count(*) as no_of_lan,b.Language from Company_user A
inner join Company_user b
on a.User_Id=b.User_Id
where a.Language !='Spanish'
group by a.User_Id,b.Language
having count(*)=2
order by User_Id

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

[@venkatkrishna1640]

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

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

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