题⽬4：每个品类下输出曝光点击率点击⼈数曝光⼈数最⾼的app要求限制单个app的曝光量⼤ 于100

SELECT a.category, a.app_name, ROUND(b.click_cnt/b.exposure_cnt, 4) AS ctr FROM ( SELECT category, app_name, SUM(exposure_cnt) AS exposure_cnt FROM logs GROUP BY category, app_name HAVING exposure_cnt > 100 ) a JOIN ( SELECT category, app_name, SUM(click_cnt) AS click_cnt, SUM(exposure_cnt) AS exposure_cnt FROM logs GROUP BY category, app_name HAVING exposure_cnt > 100 ) b ON a.category = b.category AND a.app_name = b.app_name LEFT JOIN ( SELECT category, MAX(click_cnt/exposure_cnt) AS max_ctr FROM ( SELECT category, app_name, SUM(click_cnt) AS click_cnt, SUM(exposure_cnt) AS exposure_cnt FROM logs GROUP BY category, app_name HAVING exposure_cnt > 100 ) GROUP BY category ) c ON a.category = c.category WHERE b.click_cnt/b.exposure_cnt = c.max_ctr ORDER BY a.category, ctr DESC, a.app_name;