✅ 코드 SELECT H.hacker_id , H.name FROM Submissions AS S INNER JOIN Challenges AS C ON S.challenge_id = C.challenge_id INNER JOIN Difficulty AS D ON C.difficulty_level = D.difficulty_level INNER JOIN Hackers AS H ON S.hacker_id = H.hacker_id WHERE D.score = S.score AND D.difficulty_level = C.difficulty_level GROUP BY H.hacker_id , H.name HAVING COUNT(S.submission_id) > 1 ORDER BY COUNT(S.submissio..
✅ 코드 SELECT IF(G.Grade < 8, NULL, S.Name) , G.Grade , S.Marks FROM Students AS S INNER JOIN Grades AS G ON S.Marks BETWEEN G.Min_Mark AND G.Max_Mark ORDER BY G.Grade DESC, S.Name, S.Marks INNER JOIN 기준을 BETWEEN문을 사용해서 세웠습니다.
이번 포스팅은 아래 데이터리안 아티클을 읽고 작성했습니다! 간단하게 RFM 분석법에 대해 요약하고, 주목한 점들을 정리해보겠습니다. RFM 고객 세분화 분석이란 무엇일까요 CRM 타겟팅을 하는 방식 중 가장 범용적으로 사용할 수 있는 RFM 고객 세분화 분석에 대해 알아보겠습니다 datarian.io RFM 분석법은 사용자들의 행동 패턴을 통해 타겟팅 하는 방식 중 가장 범용적으로 사용할 수 있고, 특히 구매 고객들을 분류하는 데 효과적입니다. 구매 고객들은 다음 3가지 기준에 따라 분류됩니다. Recency - 얼마나 최근에 구매했는가 Frequency - 얼마나 자주 구매했는가 Monetary - 얼마나 많은 금액을 지출했는가 이러한 기준들을 통해서 구매 고객들을 분류한 후, 각 고객군별 차별화된 마..
✅ 코드 SELECT MONTH(START_DATE) AS MONTH , CAR_ID , COUNT(HISTORY_ID) AS RECORDS FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE MONTH(START_DATE) IN (8, 9, 10) GROUP BY CAR_ID HAVING COUNT(HISTORY_ID) >= 5) AND MONTH(START_DATE) IN (8, 9, 10) GROUP BY MONTH, CAR_ID HAVING COUNT(HISTORY_ID) >= 1 ORDER BY MONTH , CAR_ID DESC; 우선적으로 ..
✅ 코드 SELECT CAR_ID , CASE WHEN SUM(IF('2022-10-16' BETWEEN START_DATE AND END_DATE, 1, 0)) = 0 THEN '대여 가능' ELSE '대여중' END AS AVAILABILITY FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID ORDER BY CAR_ID DESC; 그룹화를 수행한 뒤에 CASE문이 수행됩니다.
✅ 코드 SELECT C.CAR_ID AS CAR_ID , C.CAR_TYPE AS CAR_TYPE , FLOOR(C.DAILY_FEE * 30 * (1 - P.DISCOUNT_RATE/100)) AS FEE FROM CAR_RENTAL_COMPANY_CAR AS C INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON C.CAR_TYPE = P.CAR_TYPE AND P.DURATION_TYPE = '30일 이상' AND C.CAR_TYPE IN ('세단', 'SUV') LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H ON C.CAR_ID = H.CAR_ID AND H.END_DATE >= '2022-11-01' AND H...
✅ 코드 WITH RECURSIVE cte AS ( SELECT 0 AS num UNION ALL SELECT num + 1 FROM cte WHERE num < 23 ), ANIMAL_OUTS_REFACTOR AS ( SELECT HOUR(DATETIME) AS HOUR , COUNT(ANIMAL_ID) AS CNTS FROM ANIMAL_OUTS GROUP BY HOUR ORDER BY HOUR ) SELECT cte.num , IFNULL(O.CNTS, 0) FROM cte LEFT JOIN ANIMAL_OUTS_REFACTOR AS O ON cte.num = O.HOUR WITH RECURSIVE CTE문을 사용한 풀이입니다. NULL값을 다루기 위해서 IFNULL 함수를 사용했습니다.
✅ 코드 # 리뷰를 가장 많이 작성한 회원 WITH MOST_REVIEWS AS ( SELECT MP.MEMBER_ID AS MEMBER_ID , MP.MEMBER_NAME AS MEMBER_NAME , COUNT(DISTINCT RR.REVIEW_ID) AS REVIEWS FROM MEMBER_PROFILE AS MP INNER JOIN REST_REVIEW AS RR ON MP.MEMBER_ID = RR.MEMBER_ID GROUP BY MP.MEMBER_ID , MP.MEMBER_NAME ORDER BY REVIEWS DESC LIMIT 1 ) SELECT MR.MEMBER_NAME AS NAME , RR.REVIEW_TEXT AS REVIEW_TEXT , DATE_FORMAT(RR.REVIEW_D..
