[MySQL] 코딩테스트 연습 예제 2

*본 문제는 모 기업 코딩테스트에서 나온 문제를 제 방식대로 변형한 문제입니다.

---

문제. 주어진 테이블을 바탕으로 members 내 회원(member_id) 중 회사의 직원이 아닌 회원에 대해 

1. 첫 구매 시간(first_time)

2. 마지막 구매 시간(last_time)

3. 총 결제액(sum_pay)

4. 주문수(order_cnt)

를 구하는 쿼리를 작성하시오. (단, 해당 칼럼은 모두 결제완료인 주문에 한해서 계산되어야 함. 또한, 쿼리를 실행하여 얻게 된 데이터는 반드시 주어진 테이블에 있는 데이터이어야 함.)

* 주어진 테이블 모두 임의로 넣은 값입니다.

 

members 스키마

orders 스키마

스키마 작성 코드

* 해당 values는 연습용으로 만든 값입니다.

CREATE TABLE members
    (`member_id` int, `employee_yn` varchar(1))
;
    
INSERT INTO members
    (`member_id`, `employee_yn`)
VALUES
    (299655, 'n'),
    (183825, 'n'),
    (50364, 'y'),
    (201430, 'n'),
    (152253, 'n'),
    (406049, 'n'),
    (261449, 'n'),
    (392587, 'y'),
    (397801, 'n'),
    (190581, 'y'),
    (124205, 'n'),
    (408045, 'n'),
    (229046, 'y'),
    (37524, 'n'),
    (30834, 'y'),
    (269704, 'y'),
    (328999, 'n'),
    (341556, 'n'),
    (173005, 'y'),
    (115934, 'y'),
    (1792, 'n')
;


CREATE TABLE orders
    (`order_code` int, `order_time` datetime, `pay_money` int, `pay_yn` varchar(1), `member_id` int)
;
    
INSERT INTO orders
    (`order_code`, `order_time`, `pay_money`, `pay_yn`, `member_id`)
VALUES
    (662622, '2020-05-17 04:34:49', 62400, 'n', 43581),
    (387836, '2018-04-13 10:20:57', 4500, 'y', 70955),
    (345477, '2017-12-16 03:30:43', 34200, 'y', 70955),
    (531013, '2019-05-16 19:22:28', 56300, 'y', 70955),
    (442547, '2018-09-13 04:24:26', 78900, 'y', 299655),
    (395865, '2018-05-05 20:49:58', 91150, 'y', 299655),
    (548572, '2019-07-04 20:31:21', 25500, 'y', 299655),
    (238425, '2017-02-21 02:50:18', 28150, 'y', 201430),
    (287842, '2017-07-08 20:53:55', 63800, 'y', 201430),
    (578845, '2019-09-27 10:37:28', 68000, 'y', 201430),
    (67073, '2015-11-02 22:54:46', 50000, 'y', 201430),
    (504529, '2019-03-03 16:39:44', 56300, 'n', 201430),
    (297771, '2017-08-05 17:08:15', 79900, 'n', 201430),
    (50867, '2015-09-19 00:32:24', 4500, 'n', 201430),
    (99287, '2016-01-31 19:54:34', 21800, 'y', 1792),
    (500922, '2019-02-21 22:54:11', 45600, 'y', 1792),
    (314778, '2017-09-22 03:09:28', 45000, 'n', 1792),
    (630066, '2020-02-17 05:23:51', 19900, 'y', 1792),
    (121837, '2016-04-03 08:09:06', 18600, 'n', 1792),
    (534253, '2019-05-25 22:45:57', 63700, 'y', 1792),
    (477655, '2018-12-19 00:37:37', 40790, 'y', 1792),
    (568877, '2019-08-30 13:29:52', 69250, 'y', 400390)
;

---

나의 풀이 in MySQL

select a.member_id, b.first_time, b.last_time, b.sum_pay, b.order_cnt
from (select * from members where employee_yn = 'n') a
join (select member_id, min(order_time) first_time, max(order_time) last_time, 
      count(member_id) order_cnt, sum(pay_money) sum_pay
      from orders
      where pay_yn = 'y'
      group by member_id ) b
on a.member_id = b.member_id

풀이 설명

1. members 테이블에서 직원이 아닌 데이터만 추출해서 a 테이블 생성.

2. orders 테이블에서 주문횟수는 count, 주문금액의 합은 sum, 첫 주문은 min, 마지막 주문은 max를 활용해서 컬럼 생성하고 실제 구매로 이어진 고객에 대한 고객별 b 테이블 생성.

3. key값은 member_id로 a, b 테이블을 join하고 문제에서 요구하는 컬럼을 생성.

 

해당 문제는 각 테이블별 많은 조건을 하나씩 차근차근 풀이하면 쉽게 해결할 수 있습니다. 실제 sql에서는 이보다 훨씬 많은 양의 데이터를 추출하고 활용해야 하므로 복잡하게 생각하기보다는 최대한 간결하게 코드를 짜는 습관을 들이는 게 좋습니다.

 

코드 결과

* 해당 문제는 제가 여러 SQL 관련 코딩테스트를 치르며 문제와 테이블 모두 만든 문제입니다. 저처럼 SQL 관련 코딩테스트를 준비하시는 사람들에게 조금의 도움이라도 될까 생각하며 만들었습니다.

 

다른 문제들 보러가기

• SQL 코딩테스트 연습 예제 1
• SQL 코딩테스트 연습 예제 3