주간 SQL Study 1차시 (SolveSQL, 해커랭크 문제풀이)

1️⃣ 첫 주문과 마지막 주문(Aggregation)

Q1. https://solvesql.com/problems/first-and-last-orders/

solvesql

 

SELECT
  date(MIN(order_purchase_timestamp)) AS first_order_date,
  date(MAX(order_purchase_timestamp)) AS last_order_date
FROM
  olist_orders_dataset

2️⃣ 고양이와 개는 몇 마리 있을까?

Q2. https://programmers.co.kr/learn/courses/30/lessons/59040

코딩테스트 연습 - 고양이와 개는 몇 마리 있을까

 

SELECT ANIMAL_TYPE,
       COUNT(*)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC

 

3️⃣ 쇼핑몰의 일일 매출액과 ARPPU

Q3. https://solvesql.com/problems/daily-arppu/

solvesql

 

SELECT date(order_purchase_timestamp) as dt
     , count(distinct customer_id) as pu
     , round(sum(payment_value),2) as revenue_daily
     , round(sum(payment_value) /count(distinct customer_id),2) as arppu
FROM olist_orders_dataset as o1
     LEFT JOIN olist_order_payments_dataset AS o2 ON o1.order_id = o2.order_id
WHERE order_purchase_timestamp >= '2018-01-01'
GROUP BY date(order_purchase_timestamp)

4️⃣ 가구 판매의 비중이 높았던 날 찾기

Q4. https://solvesql.com/problems/day-of-furniture/

solvesql

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) / count(distinct order_id),2)*100 AS furniture_pct
FROM records
GROUP BY order_date
HAVING count(distinct order_id)>= 10 AND furniture_pct>= 40
ORDER BY furniture desc, order_date desc

 

5️⃣ 작품이 없는 작가 찾기

Q5. https://solvesql.com/problems/artists-without-artworks/

solvesql

SELECT o1.artist_id
     , o1.name
FROM artists AS o1
LEFT JOIN artworks_artists AS o2 ON o2.artist_id = o1.artist_id
WHERE death_year is not null AND artwork_id is null