[SQL 코드카타] 95 ~ 98번 (LeetCode)

95번. queries-quality-and-percentage

SELECT 
    Q.query_name,
    ROUND(SUM(rating / position)/COUNT(Q.query_name),2) AS quality,
    ROUND(A.cnt/COUNT(Q.query_name)*100,2) AS poor_query_percentage
FROM 
    Queries Q
    JOIN (select query_name, count(1) AS cnt
        from Queries
        where rating < 3
        Group by query_name) A
    ON Q.query_name = A.query_name
GROUP BY Q.query_name

> poor_query_percentage를 어떻게 할지 고민하다가 결국 join을 썼는데, 한 수 배웠다. AVG와 SUM

SELECT 
    query_name,
    ROUND(AVG(rating / position), 2) as quality,
    ROUND(SUM(CASE WHEN rating < 3 THEN 1 
                    ELSE 0 END) * 100 / count(*), 2) AS poor_query_percentage
FROM 
    Queries
GROUP BY 
    query_name

 

96번. monthly-transactions-i

SELECT
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) AS trans_count,
    SUM(CASE WHEN state = 'approved' THEN 1
            ELSE 0 END) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(CASE WHEN state = 'approved' THEN amount
            ELSE 0 END) AS approved_total_amount
FROM 
    Transactions
GROUP BY 
    DATE_FORMAT(trans_date, '%Y-%m'), country

 

97번. immediate-food-delivery-ii

SELECT
    ROUND(SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1 
                ELSE 0 END)*100/COUNT(*),2) AS immediate_percentage
-- 다른 방법
-- round(avg(order_date = customer_pref_delivery_date)*100, 2) as immediate_percentage
FROM
    Delivery
WHERE 
    (customer_id, order_date) IN (
        SELECT customer_id, MIN(order_date) AS first_order_date
        FROM Delivery
        GROUP BY customer_id
    )