MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS Ads (
  ad_id INT,
  user_id INT,
  `action` ENUM( 'Clicked', 'Ignored', 'Viewed'),
  PRIMARY KEY(ad_id, user_id)
);

INSERT INTO Ads
VALUES (1, 1, 'Clicked'),	
    (2, 2, 'Clicked'),
	(3, 3, 'Viewed'),
	(5, 5, 'Ignored'),
	(1, 7, 'Ignored'),
	(2, 7, 'Viewed'),
	(3, 5, 'Clicked'),
	(1, 4, 'Viewed'),
	(2, 11, 'Viewed'),
	(1, 2, 'Clicked');
SELECT ad_id,
  ROUND(100 * COALESCE(SUM(action = 'Clicked') / SUM(action IN ('Clicked', 'Viewed')), 0), 2) ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id;


ad_id
ctr


1
66.67

3
50.00

2
33.33

5
0.00


SELECT DISTINCT ad_id,
  ROUND(
    100 * 
    COALESCE(
      SUM(action = 'Clicked') OVER (PARTITION BY ad_id) / 
      SUM(action IN ('Clicked', 'Viewed')) OVER (PARTITION BY ad_id)
      , 0
    )
    , 2
  ) ctr
FROM Ads
ORDER BY ctr DESC, ad_id;


ad_id
ctr


1
66.67

3
50.00

2
33.33

5
0.00