[250919] 스파르타코딩 본캠프 34일차

💥  CODEKATA                                                                                                           

~ 96문제

 

오늘의 코드카타 후기 :

SQL 최대한 효율적인 코드로 짜려고 하니까 시간 진짜 오래 걸린다

96번에 너무 지쳐서 효율 1등 코드는 나중에 보는걸로

그리고,

통 계 . ….게 . .. .. . 개 싫 드 아 아 아 아 앙

 

 

 

95) Queries Quality and Percentage

# 내코드
WITH cnt AS (
    SELECT query_name, COUNT(rating) cnt FROM queries WHERE rating < 3 GROUP BY query_name
)

SELECT 
    a.query_name, 
    ROUND(AVG(a.new), 2) AS quality, 
    IFNULL(ROUND((c.cnt / COUNT(a.query_name)) * 100, 2), 0) AS poor_query_percentage
FROM (
    SELECT *, (rating / position) AS new
    FROM queries
) a LEFT JOIN cnt c ON a.query_name = c.query_name
GROUP BY a.query_name

⇒ ⭕

나름 속도 빠른 축에 속하는데 이거보다 괜찮은 코드 있나 탐방함

 

# 1등코드
SELECT 
	query_name,
	ROUND(AVG(cast(rating AS DECIMAL) / 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

# 1차 코드
SELECT a.month, t.country, COUNT(DISTINCT t.id) AS trans_count, a.approved_count, SUM(t.amount) AS trans_total_amount, a.approved_total_amount
FROM transactions t JOIN (
    SELECT id, country, state, amount, DATE_FORMAT(trans_date, "%Y-%m") AS month, COUNT(*) AS approved_count, SUM(amount) AS approved_total_amount
    FROM transactions
    WHERE state = "approved"
    GROUP BY DATE_FORMAT(trans_date, "%Y-%m"), country
) a ON DATE_FORMAT(t.trans_date, "%Y-%m") = a.month
GROUP BY a.month, t.country

⇒ ❌

DISTINCT 를 먹여도 JOIN 때문인지 id가 계속 중복값이 나와서 SUM(amount)가 안맞음 WITH 안쓰고 풀고 싶었는데, 해결을 못하겠어서 WITH씀

 

# 2차 코드
WITH allt AS (
    SELECT id, country, state, amount, trans_date, DATE_FORMAT(trans_date, "%Y-%m") AS month, COUNT(*) AS trans_count, SUM(amount) AS trans_total_amount
    FROM transactions
    GROUP BY DATE_FORMAT(trans_date, "%Y-%m"), country
),
appt AS (
    SELECT id, country, state, amount, DATE_FORMAT(trans_date, "%Y-%m") AS month, IFNULL(COUNT(*), 0) AS approved_count, IFNULL(SUM(amount), 0) AS approved_total_amount
    FROM transactions
    WHERE state = "approved"
    GROUP BY DATE_FORMAT(trans_date, "%Y-%m"), country
)
SELECT a1.month, a1.country, a1.trans_count, a2.approved_count, a1.trans_total_amount, a2.approved_total_amount
FROM allt a1 JOIN appt a2 ON a1.month = a2.month AND a1.country = a2.country

⇒ ❌

답은 맞는데, 서버 테스트 결과에서 틀린게 나옴.

모두 다 approved가 하나도 없는 경우에는 아예 테이블 자체가 출력되지 않음;

 

# 3차 코드
WITH allt AS (
    SELECT id, country, state, amount, trans_date, DATE_FORMAT(trans_date, "%Y-%m") AS month, COUNT(*) AS trans_count, SUM(amount) AS trans_total_amount
    FROM transactions
    GROUP BY DATE_FORMAT(trans_date, "%Y-%m"), country
),
appt AS (
    SELECT id, country, state, amount, DATE_FORMAT(trans_date, "%Y-%m") AS month, COUNT(trans_date) AS approved_count, SUM(amount) AS approved_total_amount
    FROM transactions
    WHERE state = "approved"
    GROUP BY DATE_FORMAT(trans_date, "%Y-%m"), country
)
SELECT a1.month, a1.country, a1.trans_count, IFNULL(a2.approved_count, 0) AS approved_count, a1.trans_total_amount, IFNULL(a2.approved_total_amount, 0) AS approved_total_amount
FROM allt a1 LEFT JOIN appt a2 ON a1.month = a2.month AND a1.country = a2.country

⇒ ❌

테이블 자체가 출력 안되는 건 LEFT JOIN 으로 해결하고, 아예 없는 NULL을 대비해서 IFNULL을 사용했는데, 이제 country 가 null일 때 count를 못하는 경우가 생김 하ㅣ.....ㄱ- 도저히 모르겠어서 지피티한테 물어봄

 

JOIN 에서 a1.country = a2.country 때문에 country 가 NULL인 경우 매칭이 안됨 → JOIN 매칭이 실패하면 a2. 테이블의 모든 컬럼은 NULL 이 되기 때문에 IFNULL 때문에 0이 나옴

 

라고 한다

와 여기서 JOIN 할 때 month랑만 하면 month에 다른 나라들도 다 끼는데 어떻게 해야되지; 모르겠어서 걍 지피티 답안 봄

 

# 최종코드
WITH allt AS (
    SELECT id, country, state, amount, trans_date, DATE_FORMAT(trans_date, "%Y-%m") AS month, COUNT(*) AS trans_count, SUM(amount) AS trans_total_amount
    FROM transactions
    GROUP BY DATE_FORMAT(trans_date, "%Y-%m"), country
),
appt AS (
    SELECT id, country, state, amount, DATE_FORMAT(trans_date, "%Y-%m") AS month, COUNT(trans_date) AS approved_count, SUM(amount) AS approved_total_amount
    FROM transactions
    WHERE state = "approved"
    GROUP BY DATE_FORMAT(trans_date, "%Y-%m"), country
)
SELECT a1.month, a1.country, a1.trans_count, IFNULL(a2.approved_count, 0) AS approved_count, a1.trans_total_amount, IFNULL(a2.approved_total_amount, 0) AS approved_total_amount
FROM 
    allt a1 LEFT JOIN appt a2 ON a1.month = a2.month 
    AND (a1.country = a2.country OR (a1.country IS NULL AND a2.country IS NULL))

⇒ ⭕

아 IS NULL 조건을 그냥 추가만 시키면 되는구나 이런 젠장~~~

아 진짜 시간 오래걸려서 똥꼬쑈 했는데 시간 효율 별로라 슬픔…

 

 

 

효율 1등 코드 나중에 볼 것

# 1등코드
SELECT 
    LEFT(trans_date, 7) AS month,
    country, 
    COUNT(id) AS trans_count,
    SUM(state = 'approved') AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM((state = 'approved') * amount) AS approved_total_amount
FROM 
    Transactions
GROUP BY 
    month, country;