Duplicate Job Listings

[문제]

https://datalemur.com/questions/duplicate-job-listings

 

[풀이]

1. company_id, title, description 별로 job_id를 계산한 뒤 2개 이상인 company_id만 추출한다

2. 추출한 company_id에서 고유값의 개수를 계산하고 반환한다

 

[코드]

SELECT COUNT(DISTINCT company_id) AS duplocate_companies
FROM
(
  SELECT company_id
  FROM job_listings
  GROUP BY company_id, title, description
  HAVING COUNT(job_id) >= 2
) sub