TIL - 250128

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

SELECT
  COUNT(*)
FROM(
  SELECT
    company_id
    ,title
    ,COUNT(*)
  FROM job_listings
  GROUP BY 1,2
  HAVING COUNT(*) > 1
) a;

다른 경우로 풀 수도 있다.

SELECT
  COUNT(*) - COUNT(DISTINCT CONCAT(company_id,title))
FROM job_listings
;

URL : https://datalemur.com/questions/teams-power-users

SELECT 
  sender_id
  ,COUNT(*) message_count
FROM messages
WHERE EXTRACT(YEAR FROM sent_date) = 2022
AND EXTRACT(MONTH FROM sent_date) = 8
GROUP BY 1
ORDER BY 2 DESC
LIMIT 2
;