TIL - 250411_(SQL_datalemur_7개)

문제1 (링크)

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

SELECT
  COUNT(job_id) - COUNT(DISTINCT CONCAT(company_id,title)) duplicate_companies
FROM job_listings

비고 : 위에 코드처럼 짰는데 아래 코드로도 짤 수 있는 걸 알게 되었다. 간단하게 작성하는 것이 좋은 거 같다.

문제2 (링크)

SELECT
  city 
  , COUNT(*) total_orders
FROM trades
LEFT JOIN users
USING(user_id)
WHERE status='Completed'
GROUP BY city
ORDER BY total_orders DESC
LIMIT 3

문제3 (링크)

SELECT
  EXTRACT(MONTH FROM submit_date) AS mth 
  , product_id AS product
  , ROUND(AVG(stars),2) AS avg_stars
FROM reviews
GROUP BY mth, product
ORDER BY mth, product

문제4 (링크)

SELECT a.employee_id, a.name
FROM employee a
JOIN employee b 
ON a.manager_id=b.employee_id
AND a.salary > b.salary

비고 : JOIN 절에 ON에 여러 조건을 넣을 수 있다.

문제5 (링크)

SELECT
  app_id
  , ROUND(100.0 * SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) 
  / SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END), 2)
FROM events
WHERE EXTRACT(YEAR FROM timestamp) = 2022
GROUP BY app_id

비고 : 100으로 곱하면 정수 * 정수 형태이기 때문에 실수를 계산하지 않는다. 소수점을 원하면 실수인 100.0으로 계산하면 된다.

문제6 (링크)

# PostgreSQL
SELECT
  user_id
FROM emails
JOIN texts 
USING(email_id)
WHERE EXTRACT(DAY FROM action_date - signup_date) = 1
AND signup_action = 'Confirmed'

# MySQL
SELECT
  user_id
FROM emails
JOIN texts 
USING(email_id)
WHERE TIMESTAMPDIFF(DAY, signup_date, action_date) = 1
AND signup_action = 'Confirmed'

비고 : PostgreSQL과 MySQL의 차이가 있는 코드였다. 두 가지 방법을 알게 되었다.

문제7 (링크)

SELECT
  cnt
  , COUNT(employee_id)
FROM 
(
  SELECT 
    e.employee_id
    ,COUNT(DISTINCT query_id) cnt
  FROM queries q 
  RIGHT JOIN employees e 
  ON q.employee_id=e.employee_id
  AND EXTRACT(QUARTER FROM query_starttime) = 3
  GROUP BY 1
  ) a 
GROUP BY 1
ORDER BY 1

비고 : USING을 사용하고 WHERE절을 이용했는데 그 경우 0이 추출되지 않았다. 이유는 WHERE에서 NULL값인 사람들이 필터링되기 때문이다. 주의하자.