# $1322. 广告效果
# https://leetcode-cn.com/problems/ads-performance/

# SQL架构
Create table If Not Exists Ads (ad_id int, user_id int, action ENUM('Clicked', 'Viewed', 'Ignored'));
Truncate table Ads;
insert into Ads (ad_id, user_id, action) values ('1', '1', 'Clicked');
insert into Ads (ad_id, user_id, action) values ('2', '2', 'Clicked');
insert into Ads (ad_id, user_id, action) values ('3', '3', 'Viewed');
insert into Ads (ad_id, user_id, action) values ('5', '5', 'Ignored');
insert into Ads (ad_id, user_id, action) values ('1', '7', 'Ignored');
insert into Ads (ad_id, user_id, action) values ('2', '7', 'Viewed');
insert into Ads (ad_id, user_id, action) values ('3', '5', 'Clicked');
insert into Ads (ad_id, user_id, action) values ('1', '4', 'Viewed');
insert into Ads (ad_id, user_id, action) values ('2', '11', 'Viewed');
insert into Ads (ad_id, user_id, action) values ('1', '2', 'Clicked');

# Write your MySQL query statement below
select
    ad_id,
    round(
        ifnull(
            sum(action = 'clicked') / sum(action in ('clicked', 'viewed')) * 100,
            0
        ),
        2
    ) as ctr
from
    Ads
group by
    ad_id
order by
    ctr desc,
    ad_id;

# clean-up
drop table Ads;