📒 SQL - 문제풀이 Day2

📙 쇼핑몰의 일일 매출액과 ARPPU

• dt - 매출 날짜 (예: 2018-01-01)

• pu - 결제 고객 수

• revenue_daily - 해당 날짜의 매출액

• arppu - 결제 고객 1인 당 평균 결제 금액

• 주의할 점은 주문 고객이 해당 날짜에 중복될 수 있다는 점이다. -> DISTINCT 사용해야함

SELECT
  DATE(a.order_purchase_timestamp) dt,
  COUNT(DISTINCT a.customer_id) pu,
  SUM(payment_value) revenue_daily,
  ROUND(SUM(payment_value)/COUNT(DISTINCT a.customer_id), 2) arppu
FROM olist_orders_dataset a
JOIN olist_order_payments_dataset b ON a.order_id = b.order_id
WHERE dt >= '2018-01-01'
GROUP BY dt
ORDER BY dt

📙 멘토링 짝꿍 리스트

• mentee : ‘2021년 12월 31일’을 기준으로 3개월 이내
• mentor : ‘2021년 12월 31일’을 기준으로 재직한지 2년 이상

SELECT 
    mentee.employee_id AS mentee_id,
    mentee.name AS mentee_name,
    mentor.employee_id AS mentor_id,
    mentor.name AS mentor_name
FROM 
    (
      SELECT employee_id, name, department
      FROM employees
      WHERE join_date BETWEEN '2021-10-01' AND '2022-03-31'
    ) AS mentee
LEFT JOIN 
    (
      SELECT employee_id, name, department
      FROM employees
      WHERE join_date <= '2019-12-31'
    ) AS mentor
ON mentee.department != mentor.department
ORDER BY mentee_id ASC, mentor_id ASC

📙 가구 판매의 비중이 높았던 날 찾기

• order_date - 주문 날짜
• furniture - 해당 일의 Furniture 카테고리 주문 수
• furniture_pct - 해당 일의 전체 주문 대비 Furniture 카테고리 주문의 비율 (%)
• Furniture 카테고리의 주문 비율은 백분율로 계산하며, 반올림하여 소수점 둘째자리까지만 출력
• Furniture 카테고리의 주문 비율이 높은 것부터 보여주도록 정렬하고, 비율이 같다면 날짜 순으로 정렬

SELECT
  order_date,
  COUNT(DISTINCT(CASE WHEN category='Furniture' THEN order_id END)) AS furniture,
  ROUND((COUNT (DISTINCT(CASE WHEN category='Furniture' THEN order_id END))+0.00) / (COUNT(DISTINCT(order_id))+0.00)*100, 2) AS furniture_pct
FROM records
GROUP BY order_date
HAVING COUNT(DISTINCT order_id) >= 10 AND furniture_pct>= 40
ORDER BY furniture_pct DESC;

WITH order_counts AS (
  SELECT
    order_date,
    COUNT(DISTINCT order_id) AS total_orders,
    COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id END) AS furniture_orders
  FROM records
  GROUP BY order_date
)
SELECT
  order_date,
  furniture_orders AS furniture,
  ROUND(furniture_orders * 100.0 / total_orders, 2) AS furniture_pct
FROM order_counts
WHERE total_orders >= 10
  AND (furniture_orders * 100.0 / total_orders) >= 40
ORDER BY furniture_pct DESC;