2021-04-04 SQL刷题

小红书-计算好评率

select user_name,
sum(case when sat_name = '好评' then 1 else 0 end)/count(sat_name)+'%' as 好评率,
sum(case when sat_name = '中评' then 1 else 0 end)/count(sat_name)+'%' as 中评率,
sum(case when sat_name = '差评' then 1 else 0 end)/count(sat_name)+'%' as 差评率
from
(select *,DATE_FORMAT(sub_time,'%Y-%m-%d') as subtime from redbk_userjudge
where sub_time between '2019-03-01' and '2019-03-31' and user_name = '小张')t

image.png

第二题

select 
user_id,item_id,
case when 是否购买 = 1 and 是否收藏 =1 then 1 else 0 end as "购买且收藏",
case when 是否购买 = 0 and 是否收藏 =1 then 1 else 0 end as "收藏未购买",
case when 是否购买 = 1 and 是否收藏 =0 then 1 else 0 end as "购买未收藏",
case when 是否购买 = 0 and 是否收藏 =0 then 1 else 0 end as "未购买收藏"
FROM
    (select * FROM
        (select user2 as user_id,item2 as item_id,
        (case when item1 is null then 0  else 1 end) as '是否购买',
        (case when item2 is null then 0  else 1 end) as '是否收藏'
    from
                (select a.user_id as user1,a.item_id as item1,b.user_id as user2,b.item_id as item2
                from redbk_orders a right join redbk_favorites b 
                on a.user_id = b.user_id and a.item_id = b.item_id)t1
    )t2
    UNION
    (select user1 as uesr_id,item1 as item_id,
        (case when item1 is null then 0  else 1 end) as '是否购买',
        (case when item2 is null then 0  else 1 end) as '是否收藏'
     from
                (select a.user_id as user1,a.item_id as item1,b.user_id as user2,b.item_id as item2
                from redbk_orders a left join redbk_favorites b 
                on a.user_id = b.user_id and a.item_id = b.item_id)t3)
    )t4
order by user_id,item_id

image.png

笔记一下

image.png