# 버전1
WITH daily_fee AS (
SELECT car_id, car_type, daily_fee
FROM car_rental_company_car
WHERE car_type = "트럭"
), -- 트럭만 하루 대여금액
discount AS (
SELECT plan_id, car_type, duration_type, discount_rate
FROM car_rental_company_discount_plan
WHERE car_type = "트럭"
),
rentalday AS (
SELECT history_id, car_id,
DATE_FORMAT(start_date, "%Y-%m-%d") AS start_date,
DATE_FORMAT(end_date, "%Y-%m-%d") AS end_date,
(end_date - start_date) + 1 AS rentalday
FROM car_rental_company_rental_history
), -- 며칠 렌트했는지
total AS (
SELECT
r.history_id, r.car_id, f.daily_fee, r.rentalday,
(f.daily_fee * r.rentalday) AS total
FROM daily_fee f JOIN rentalday r ON f.car_id = r.car_id
), -- 트럭만 총금액 할인X
total_plan AS (
SELECT history_id, car_id, daily_fee, rentalday, total,
CASE
WHEN rentalday < 7 THEN 0
WHEN rentalday BETWEEN 7 AND 29 THEN 10
WHEN rentalday BETWEEN 30 AND 89 THEN 11
WHEN rentalday >= 90 THEN 12
END AS plan_id
FROM total
), -- total + plan_id
final AS (
SELECT t.history_id, t.total, t.rentalday, COALESCE(d.discount_rate, 0) as dis_rate,
CASE
WHEN t.rentalday < 7 THEN total
WHEN t.rentalday BETWEEN 7 AND 29 THEN FLOOR(t.total * (1 - (d.discount_rate/100)))
WHEN t.rentalday BETWEEN 30 AND 89 THEN FLOOR(t.total * (1 - (d.discount_rate/100)))
WHEN t.rentalday >= 90 THEN FLOOR(t.total * (1 - (d.discount_rate/100)))
END AS fee
FROM total_plan t LEFT JOIN discount d ON t.plan_id = d.plan_id
ORDER BY fee DESC, t.history_id DESC
)
SELECT history_id, fee
FROM final
⇒
왜……..왜 틀렸다는 것인가…!
트럭만의 대여 총금액 할인X 인 total 과 discount 를 JOIN 하고 싶은데, 같은 컬럼이 없어서 total_plan 가상 테이블을 하나 더 생성하고 plan_id 컬럼을 가상으로 만들었음
그리고 가상 plan_id 에다가 렌탈 기간에 맞춰서 찐 plan_id 를 숫자로 하드코딩 해서 그런가? ? ?해서 바꿔봄
# 버전2
WITH daily_fee AS (
SELECT car_id, car_type, daily_fee
FROM car_rental_company_car
WHERE car_type = "트럭"
), -- 트럭만 하루 대여금액
rentalday AS (
SELECT history_id, car_id,
DATE_FORMAT(start_date, "%Y-%m-%d") AS start_date,
DATE_FORMAT(end_date, "%Y-%m-%d") AS end_date,
(end_date - start_date) + 1 AS rentalday
FROM car_rental_company_rental_history
), -- 며칠 렌트했는지
total AS (
SELECT
r.history_id, r.car_id, f.daily_fee, r.rentalday,
(f.daily_fee * r.rentalday) AS total
FROM daily_fee f JOIN rentalday r ON f.car_id = r.car_id
) -- 트럭만 총금액 할인X
SELECT history_id,
CASE
WHEN rentalday >= 90 THEN FLOOR(total * (1 - ((SELECT discount_rate FROM car_rental_company_discount_plan
WHERE car_type = "트럭" AND duration_type = "90일 이상")/100)))
WHEN rentalday >= 30 THEN FLOOR(total * (1 - ((SELECT discount_rate FROM car_rental_company_discount_plan
WHERE car_type = "트럭" AND duration_type = "30일 이상")/100)))
WHEN rentalday >= 7 THEN FLOOR(total * (1 - ((SELECT discount_rate FROM car_rental_company_discount_plan
WHERE car_type = "트럭" AND duration_type = "7일 이상")/100)))
ELSE total END AS fee
FROM
total
ORDER BY
fee DESC, history_id DESC
⇒
ROUND는 반올림이라 소수만 버리는 FLOOR 사용해봄
SELECT 절에서 또 SELECT ~ FROM하여 할인율 데려오는 방법은 AI의 도움을 받았음
음 그런데도 틀렸다함ㅡㅡ????????
# 버전3
WITH daily_fee AS (
SELECT car_id, car_type, daily_fee
FROM car_rental_company_car
WHERE car_type = "트럭"
), -- 트럭만 하루 대여금액
discount AS (
SELECT plan_id, car_type, duration_type, discount_rate
FROM car_rental_company_discount_plan
WHERE car_type = "트럭"
),
rentalday AS (
SELECT history_id, car_id,
DATEDIFF(end_date, start_date) + 1 AS rentalday
FROM car_rental_company_rental_history
), -- 며칠 렌트했는지
total AS (
SELECT
r.history_id, r.car_id, f.daily_fee, r.rentalday,
(f.daily_fee * r.rentalday) AS total
FROM daily_fee f JOIN rentalday r ON f.car_id = r.car_id
) -- 트럭만 총금액 할인X
SELECT history_id,
CASE
WHEN rentalday BETWEEN 7 AND 29 THEN FLOOR(total * (1 - ((SELECT discount_rate FROM discount WHERE duration_type LIKE "7%")/100)))
WHEN rentalday BETWEEN 30 AND 89 THEN FLOOR(total * (1 - ((SELECT discount_rate FROM discount WHERE duration_type LIKE "30%")/100)))
WHEN rentalday >= 90 THEN FLOOR(total * (1 - ((SELECT discount_rate FROM discount WHERE duration_type LIKE "90%")/100)))
ELSE total END AS fee
FROM
total
ORDER BY
fee DESC, history_id DESC
⇒
범인은 rentalday에 있는 날짜 계산
DATE_FORMAT(start_date, "%Y-%m-%d") AS start_date,
DATE_FORMAT(end_date, "%Y-%m-%d") AS end_date,
(end_date - start_date) + 1 AS rentalday
이자식이었다 ㅡㅡ
동일한 달에는 계산을 잘하는데, 달이 넘어가는 순간 숫자가 이상해지는걸 볼 수 있음
당일 렌트가 1일 출력 잘 되는 것만 보고 바로 데이터에 확신을 갖고 쿼리를 이어가서 몇 번을 봐도 대체 어디가 틀렸는지 몰랐던거임 ㅠ
SELECT
user_id
FROM
emails
WHERE
signup_date LIKE "2022-06-%"
ORDER BY
user_id
⇒
WHERE 에다가 signup_date BETWEEN “2022-06-01” AND “2022-06-30” 을 할까 하다가 그냥 LIKE를 사용했는데 datetime 이라고 타입이 지정되어 있으니까 그냥 BETWEEN 쓸 걸 그랬다 ㅠ
역시 튜터님 정답 코드 BETWEEN 사용하심 BETWEEN 이 뭔가 하드코딩 같아서 마음에 안드는데,
SQL에서는 datetype 이면 LIKE 보다는 BETWEEN 쓰는게 나은듯
# 튜터님 정답 코드
SELECT user_id
FROM qcc.emails
WHERE signup_date BETWEEN '2022-06-01' AND '2022-06-30'
ORDER BY user_id
SELECT
count(a.email_id) AS fail_count
FROM (
SELECT email_id
FROM texts
WHERE signup_action = "N"
GROUP BY email_id
) a
# 튜터님 정답 코드
SELECT COUNT(DISTINCT email_id) AS fail_count
FROM qcc.texts
WHERE signup_action = 'N'
⇒
한 번이라도 실패한 고객의 수 니까 X X O 이렇게 3번만에 성공을 했어도 실패로 간주하여 카운트 해야함
=> 호오서브쿼리를 사용하지 않고도
COUNT(DISTINCT count구하려는 컬럼)
를 사용하면 되는구나! 배워갑니다
# 시험에 낸 코드
SELECT
count(a.email_id) as user_count
FROM (
SELECT e.email_id, t.signup_action, RANK() OVER(PARTITION BY e.email_id ORDER BY e.signup_date) AS rnk
FROM emails e JOIN texts t ON e.email_id = t.email_id
WHERE e.signup_date = t.action_date
ORDER BY e.email_id
) a
WHERE
a.rnk = 1 AND a.signup_action != "Y"
⇒
아 미친 잘못냈다 …………….
RANK() 로 email_id를 t.signup_action Y / N 순위를 매겨서, 동일 id가 Y / N 두 개가 있더라도 N이 1순위가 되게끔 하려고 했는데,
일단 1이 중복될 수 있는 RANK()를 사용함….ROW_NUMBER() 을 썼어야 했는데 ㅠ
그리고
ORDER BY e.signup_date < ㅇㅈㄹ했음 t.signup_action 를 썼어야 했는데 대박 바보죠?ㅋㅋ
# 시험 끝나고 생각해본 다른 코드
# 아 ~~~~ SUM 써서 한 번이라도 Y나왔으면 1을 준 다음에 0값만 선택해서 카운트 먹일걸 아~~~
SELECT
count(a.email_id) AS user_count
FROM (
SELECT e.email_id, e.signup_date, t.action_date, SUM(CASE WHEN t.signup_action = 'Y' THEN 1 ELSE 0 END) total
FROM emails e LEFT JOIN texts t ON e.email_id = t.email_id AND e.signup_date = t.action_date
GROUP BY e.email_id
) a
WHERE
a.total = 0
나는 . ..실전에 약한 타입인가부다
바보다 나는………
torr . . ...
# 튜터님 정답 코드
SELECT count(distinct e.user_id) user_count
FROM qcc.emails e JOIN qcc.texts t ON e.email_id = t.email_id
WHERE e.signup_date = t.action_date AND t.signup_action = 'N'
