SQL

 

Q. '[지역(시도)] 음식점이름(음식종류)' 컬럼 만들고, 총 주문건수 만들기

A. select concat('[', subst(abbr,1,2),']',restaurant_name,
'(',cusine_type,')',"음식점", count(1) "주문 건수"
from food_orders
group by 1

 

1. 어떤 테이블에서 데이터 뽑을지 : from food_orders
2. 어떤 컬럼 : addr,restaurant_name,cusine_type,order_id
3. 어떤 조건 : x
4. 어떤 함수 : 갯수(count),특정 문자만 뽑는 거(subst),문자 합치는 거(group by, concat)

Q. 다음의 조건으로 배달시간이 늦었는지 판단한는 값
(주중 25분이상, 주말 30분이상)
1. SQL문의 기본 구조로 시작
2. 조건을 여러번 적용할 때 if, case 문 중 어떤 것을 이용할지 결정
3. 조건에 '주중, 주말'조건과 '배달 시간'조건을 동시에 줄 때 사용할 논리연산자

A. select order_id,
   restaurant_name,
   day_of_the_week,
   delivery_rime,
case when day_of_the_week='weekday' and delivery_time>=25 then 'Late'
when day_of_the_week='weekend' and delivery>=30 then 'Late'
   else 'On-time' and "지연여부"
from food_orders

 

Q. 가격이 제일 비싼 식품의 정보 출력하기

A. select *
   from food_product
   order by price desc
   limit 1

limit으로 한 칸만 데리고 오기

A. select product_id,
	        product_name,
          product_cd,
          category,
          price
   from food_product
   where price = (select max(price) from food_product

실행순서때문에 where 절에서 max 함수가 실행이 안 됌 !
실행순서 참고하기

 

Q. 배송된 주문을 기준으로 고유 고객수, 총 주문 수, 총 결제 금액, 그리고 고객 1명당 평균 결제액

• cnt_users, cnt_roders, sum_payment, arppu

내 답안
select count(customer_id) as cnt_users,
  	   count(order_if) cnt_orders,
       sum(payment_value) sum_payment
from orders
inner join payment p on o.order_id = p.order_id
where order_status= 'delivered'
group by p.payment_type -- 바보멍청이

정답
SELECT 
    COUNT(DISTINCT o.customer_id) AS cnt_users,
    COUNT(o.order_id) AS cnt_orders, 
    SUM(p.payment_value) AS sum_payment,
    if(COUNT(DISTINCT o.customer_id) = 0, 0, SUM(p.payment_value) / COUNT(DISTINCT o.customer_id)) AS ARPPU
FROM orders o 
INNER JOIN payments p ON o.order_id = p.order_id 
WHERE o.order_status = 'delivered';

 

Q. payment 테이블에서 각 결제 방식별 / 결제 금액의 합계와 / 해당 결제 방식이 전체 결제 금액에서 차지하는 비율을 계산

• payment_type, total_payment_value, payment_percentage

내 답안
SELECT payment_type,
    COUNT(*) ,
	SUM(payment_value) 
from payments ;
where payment_value / 
group by 1

정답
SELECT 
    payment_type,
    SUM(payment_value) AS total_payment_value,
    ROUND(SUM(payment_value) * 100.0 / (SELECT SUM(payment_value) FROM payments), 2) AS payment_percentage 
FROM payments
GROUP BY 1
ORDER BY 2 DESC;

 

 

Q. 배송된 주문을 기준으로 / 고유 고객 수, 총 주문 수, 총 결제 금액, / 그리고 고객 1명당 평균 결제액을 계산

 

내 답안
SELECT COUNT(customer_id) as cnt_users,
  COUNT(order_id) cnt_orders,
  sum(payment_value) sum payment
from orders 
inner join payments p
on o.order_id = p.order_id 
WHERE order_status = 'delivered'
group by p.payment_type ;

정답
SELECT 
    COUNT(DISTINCT o.customer_id) AS cnt_users,
    COUNT(o.order_id) AS cnt_orders,
    SUM(p.payment_value) AS sum_payment,
    CASE
        WHEN COUNT(DISTINCT o.customer_id) = 0 THEN 0
        ELSE SUM(p.payment_value) / COUNT(DISTINCT o.customer_id)
    END AS arppu 
FROM orders o
INNER JOIN payments p ON o.order_id = p.order_id
WHERE o.order_status = 'delivered';

 

 

Q. REST_INFO 테이블에서 (음식종류별)로 (즐겨찾기수가 가장 많은 식당)의 [음식 종류, ID, 식당 이름, 즐겨찾기수]를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.

 

난 이렇게 했던거 같은데 가물가물하다 벌써 .............. 

select category, price, product_name 
from food_product t1
where price = (
   		select max(price) 
   		from food_product 
   		where t1.category = category)
and category in ('과자', '국', '김치', '식용유')
order by price desc

튜터님 답안

SELECT category,
      price,
      product_name
from food_product
where (category, price) in (
SELECT category,
      max(price)
from food_product
where category in ('과자', '국', '김치', '식용유')
group by 1
) 
order by 2 desc

 

나중에 컬럼이 더 많아지면 원하는 값이 나올 수도 있어서 이렇게 바꿔서 풀기 !

🔻만약 여기서 having 사용해서 금액 10,000원 이상인 것을 찾아보면 ?

 

SELECT category,
	   max(price)
from food_product
where category not in ('과자', '국', '김치', '식용유')
group by 1
having max(price) >= 10000

 

 

ROM → FROM→ ON → JOIN → WHERE → GROUP BY → HAVING → 윈도우 함수→ SELECT →DISTINCT → ORDER BY → LIMIT