题解 | 请写出计算粉丝ctr的sql语句
请写出计算粉丝ctr的sql语句
https://www.nowcoder.com/practice/853a6567cf524f63bab0879b8d0bfe62
SELECT read_num/allcount as fans_ctr from ( SELECT sum(show_num) as allcount , count(if (read_num != 0 ,read_num,null )) as read_num from ( SELECT c.fans_id ,b.author_id , c.content_id as sing_song, t1.content_id as true_like_song , show_num , read_num from c left join b on c.content_id = b.content_id -- left join a on c.fans_id = a.fans_id left join ( SELECT a.fans_id , a.author_id , b.content_id from a left join b on a.author_id = b.author_id group by 1,2,3 order by a.fans_id)t1 on c.fans_id = t1.fans_id where c.fans_id in ((SELECT DISTINCT fans_id from a)) and c.content_id = t1.content_id)t2 )t3