#90. [LeetCode / MySQL] 1251. Average Selling Price

문제 입력 코드 1. SELECT p.product_id, ROUND(SUM(units * price) / SUM(units), 2) AS average_price FROM Prices AS p JOIN UnitsSold AS u ON (p.product_id = u.product_id) AND (u.purchase_date BETWEEN start_date AND end_date) GROUP BY product_id 2. SELECT p.product_id, ROUND(SUM(units * price) / SUM(units), 2) AS average_price FROM Prices AS p INNER JOIN UnitsSold AS u ON (p.product_id = u.product_id) WHE..

• format_list_bulleted SQL
• · 2023. 9. 16.
• textsms

#88. [LeetCode / MySQL] 1934. Confirmation Rate

문제 입력 코드 1. SELECT s.user_id, ROUND(AVG(IF(c.action="confirmed",1,0)),2) AS confirmation_rate FROM Signups AS s LEFT JOIN Confirmations AS c USING(user_id) GROUP BY user_id 2. WINDOW - PARTITION BY SELECT DISTINCT s.user_id, ROUND(SUM( CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END ) OVER (PARTITION BY user_id) / COUNT(*) OVER (PARTITION BY user_id), 2) AS confirmation_rate FROM Signups AS s..

• format_list_bulleted SQL
• · 2023. 9. 14.
• textsms

#84. [LeetCode / MySQL] 1661. Average Time of Process per Machine

문제 입력 코드 1. JOIN - AND 조건 SELECT a1.machine_id, ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time FROM Activity AS a1 JOIN Activity AS a2 ON (a1.machine_id = a2.machine_id) AND (a1.process_id = a2.process_id) AND (a1.activity_type='start') AND (a2.activity_type='end') GROUP BY a1.machine_id 2. JOIN SELECT a1.machine_id, ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time FR..

• format_list_bulleted SQL
• · 2023. 9. 10.
• textsms

#74. JOIN : 상품을 구매한 회원 비율 구하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 1. SELECT YEAR, MONTH, COUNT(*) AS PUCHASED_USERS, ROUND((COUNT(*)/ ( SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021 )), 1) AS PUCHASED_RATIO FROM ( SELECT DISTINCT YEAR(S.SALES_DATE) AS YEAR, MONTH(S.SALES_DATE) AS MONTH, U.USER_ID FROM ONLINE_SALE AS S JOIN USER_INFO AS U ON S.USER_ID = U.USER_ID AND YEAR(JOINED) = 2021 ) AS A GROUP BY YEAR, MONTH ORDER BY YEAR, MONTH; 2. SE..

• format_list_bulleted SQL
• · 2023. 8. 31.
• textsms

#73. STRING, DATE : 자동차 대여 기록 별 대여 금액 구하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 1. SELECT RENT.HISTORY_ID, ROUND(CAR.DAILY_FEE * (DATEDIFF(RENT.END_DATE, RENT.START_DATE)+1) * (CASE WHEN DATEDIFF(END_DATE,START_DATE)+1 < 7 THEN 1 WHEN DATEDIFF(END_DATE,START_DATE)+1 < 30 THEN 0.95 WHEN DATEDIFF(END_DATE,START_DATE)+1 < 90 THEN 0.92 ELSE 0.85 END) ) AS FEE FROM CAR_RENTAL_COMPANY_CAR AS CAR JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS RENT ON CAR.CAR_ID = RENT.CAR_ID J..

• format_list_bulleted SQL
• · 2023. 8. 30.
• textsms

#69. JOIN : 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 SELECT CAR.CAR_ID, CAR.CAR_TYPE, ROUND((CAR.DAILY_FEE * ((100 - DISCOUNT.DISCOUNT_RATE)/100) * 30)) AS FEE FROM CAR_RENTAL_COMPANY_CAR AS CAR JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS RENT ON CAR.CAR_ID = RENT.CAR_ID JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS DISCOUNT ON CAR.CAR_TYPE = DISCOUNT.CAR_TYPE WHERE CAR.CAR_ID NOT IN( SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE EN..

• format_list_bulleted SQL
• · 2023. 8. 28.
• textsms