[MYSQL] SolveSql JOIN, UNION

✔ JOIN, UNION

두 테이블 결합하기

[SolveSql join]

/*
 SELECT : 선수 ID
 FROM : events, records 테이블
 WHERE : 종목 이름이 Golf 
*/
SELECT DISTINCT
  R.athlete_id
FROM
  events E
  JOIN records R ON E.id = R.event_id
WHERE
  E.sport = 'Golf'

쇼핑몰의 일일 매출액

[SolveSql olist-daily-revenue]

/*
 SELECT : 매출 날짜와 해당 날짜의 매출액
 FROM : olist_orders_dataset, olist_order_payments_dataset 테이블
 WHERE : 2018년 1월 1일 이후
 GROUP BY : 날짜 별
 ORDER BY : 매출 날짜 기준으로 오름차순 정렬
*/

# SQLite
SELECT
  STRFTIME ('%Y-%m-%d', O.order_purchase_timestamp) AS dt,
  ROUND(SUM(P.payment_value), 2) AS revenue_daily
FROM
  olist_orders_dataset O
  JOIN olist_order_payments_dataset P ON O.order_id = P.order_id
WHERE
  STRFTIME ('%Y%m%d', O.order_purchase_timestamp) >= '20180101'
GROUP BY
  STRFTIME ('%Y-%m-%d', O.order_purchase_timestamp)
ORDER BY
  dt
  
# MySQL
SELECT
  DATE_FORMAT(O.order_purchase_timestamp, '%Y-%m-%d') AS dt,
  ROUND(SUM(P.payment_value), 2) AS revenue_daily
FROM
  olist_orders_dataset O
  JOIN olist_order_payments_dataset P ON O.order_id = P.order_id
WHERE
  DATE(O.order_purchase_timestamp) >= '2018-01-01'
GROUP BY
  DATE_FORMAT(O.order_purchase_timestamp, '%Y-%m-%d')
ORDER BY
  dt

다음날도 서울숲의  미세먼지 농도는 나쁨

[SolveSql bad-finedust-measure]

/*
 SELECT : 당일, 다음날, 당일의 미세먼지 농도, 다음날의 미세먼지 농도
 FROM : measurements, measurements의 측정 일시에 하루를 더한 테이블
 WHERE : 당일의 미세먼지 농도보다 다음날의 미세먼지 농도가 더 안 좋은 날
*/

# SQLite
SELECT
  A.measured_at AS 'today',
  B.measured_at AS 'next_day',
  A.pm10 AS 'pm10',
  B.pm10 AS 'next_pm10'
FROM
  measurements A
  LEFT JOIN measurements B ON B.measured_at = DATE(A.measured_at, '+1 day')
WHERE
  B.pm10 > A.pm10
  
# MySQL
SELECT
  A.measured_at AS 'today',
  B.measured_at AS 'next_day',
  A.pm10 AS 'pm10',
  B.pm10 AS 'next_pm10'
FROM
  measurements A
  LEFT JOIN measurements B ON B.measured_at = DATE_ADD(A.measured_at, INTERVAL 1 DAY)
WHERE
  B.pm10 > A.pm10;

복수 국적 메달 수상한 선수 찾기

[SolveSql multiple-medalist]

/*
 SELECT : 선수 이름
 FROM : records, atheletes, games 테이블
 WHERE : 2000년 이후 경기, 메달 수상 기록
 GROUP : 선수 id
 HAVING : 2개 이상의 국적 (2개 이상의 국가 정보)
 ORDER BY : 선수 이름 오름차순 정렬
*/
SELECT
  A.name
FROM
  records R
  JOIN athletes A ON R.athlete_id = A.id
  JOIN games G ON R.game_id = G.id
WHERE
  G.year >= 2000
  AND medal IS NOT NULL
GROUP BY
  A.id
HAVING
  COUNT(DISTINCT R.team_id) >= 2
ORDER BY
  A.name

쇼핑몰의 일일 매출액과 ARPPU

[SolveSql daily-arppu]

/*
 SELECT : 매출 날짜, 결제 고객 수, 해당 날짜의 매출액, 결제 고객 1인 당 평균 결제 금액
 FROOM : olist_orders_dataset, olist_order_payments_dataset 테이블
 WHERE : 2018년 1월 1일 이후
 GROUP BY : 매출 날짜 별
 ORDER BY : 매출 날짜 기준으로 오름차순 정렬
*/
SELECT
  DATE(O.order_purchase_timestamp) AS 'dt',
  COUNT(DISTINCT O.customer_id) AS 'pu',
  ROUND(SUM(P.payment_value), 2) AS 'revenue_daily',
  ROUND(SUM(P.payment_value) / COUNT(DISTINCT O.customer_id), 2) AS 'arppu'
FROM
  olist_orders_dataset O
  JOIN olist_order_payments_dataset P ON O.order_id = P.order_id
WHERE
  dt >= '2018-01-01'
GROUP BY
  dt
ORDER BY
  dt

멘토링 짝꿍 리스트

[SolveSql mentor-mentee-list]

/*
 SELECT : 멘티 ID, 멘토 이름, 멘토 ID, 멘토 이름
 FROM : employees 테이블
 WHERE : 멘티의 경우, 2021년 12월 31일을 기준으로 3개월 이내 입사한 직원 (2021년 9월 1일부터 2021년 12월 31일 사이에 입사한 직원)
         멘토의 경우, 2021년 12월 31일을 기준으로 재직한지 2년 이상된 직원 (2019년 12월 31일부터 재직한 직원)
         멘티와 멘토가 서로 다른 부서 
 ORDER BY : 멘티 ID를 기준으로 오름차순 정렬, 멘티 1명에 대해 배정 가능한 멘토가 여러 명인 경우 멘토 ID로 오름차순 정렬
*/
SELECT
  A.employee_id AS 'mentee_id',
  A.name AS 'mentee_name',
  B.employee_id AS 'mentor_id',
  B.name AS 'mentor_name'
FROM
  employees A
  CROSS JOIN employees B
WHERE
  A.join_date BETWEEN '2021-09-01' AND '2021-12-31'
  AND B.join_date <= '2019-12-31'
  AND A.department != B.department
ORDER BY mentee_id, mentor_id

작품이 없는 작가 찾기

[SolveSql artists-without-artworks]

/*
 SELECT : 작가 ID, 작가 이름
 FROM : artists, artworks_artists 테이블
 WHERE : MoMA에 등록되어있고, 현재 살아있지 않으며, MoMA에 등록된 작품이 없는 작가
*/
SELECT
  A.artist_id,
  A.name
FROM
  artists A
  LEFT OUTER JOIN artworks_artists W ON A.artist_id = W.artist_id
WHERE
  A.death_year IS NOT NULL
  AND W.artist_id IS NULL