2024.10.29 - 코드카타

 

코드카타 94번

# 각 대회에 등록된 사용자 비율 소수점 두자리 반올림

#퍼센트 내림차순 content_id 오름차순

select r.contest_id, round(count(r.user_id)/3 *100 ,2) as percentage

from register r

left join users u on u.user_id = r.user_id

group by r.contest_id

order by percentage desc, r.contest_id asc

코드카타 95번

# query_name각각 , quality, 를 찾는 해결책

#둘 다 quality소수점 둘째 자리까지 반올림poor_query_percentage 해야 합니다 .

#결과 표를 임의의 순서 로 반환합니다 .

with poor as (

    select query_name,

    rating/position as quality_ratio,

    case when rating <3 then 1 else 0 end as poor_query

    from queries

),

results as (

    select

    query_name,

    avg(quality_ratio) as avg_quality,

    sum(poor_query) as poor_query_count,

    count(*) as total

    from poor

    group by query_name

)

select query_name, round(avg_quality,2) as quality, round(poor_query_count * 100/total,2) as poor_query_percentage

from results

 

코드카타 96번 

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 1, 2