/*
Google SQL Interview Question

Find companies who have atleast 2 users who speks English and German both the languages
*/

-- Schema Setup
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');

-- Solution
SELECT
    company_id
FROM
 (SELECT
     company_id,
     user_id
  FROM company_users
  WHERE language IN('German','English')
  GROUP BY company_id,user_id
  HAVING COUNT(language) = 2) company_users_data
GROUP BY company_id
HAVING COUNT(user_id) = 2;