SQL 查询优化：下载未展示用户、点击率分析、决策时间中位数

题目1

SELECT a.package_name, COUNT(DISTINCT a.userid) AS user_count
FROM app a
WHERE a.action_type = 'download' AND NOT EXISTS (
    SELECT 1 FROM app WHERE userid = a.userid AND package_name = a.package_name AND action_type = 'itemShow'
)
GROUP BY a.package_name
ORDER BY user_count DESC
LIMIT 100

该SQL语句查询的是在app表中，下载了某个应用但未展示过该应用的用户数量，按照应用名称分组并按照用户数量从大到小排序，最后取前100个。

题目2

SELECT
    a.package_name,
    COUNT(DISTINCT a.userid) AS click_count,
    COUNT(DISTINCT CASE WHEN d.userid IS NULL THEN a.userid END) AS click_without_download_count,
    COUNT(DISTINCT CASE WHEN d.userid IS NULL THEN a.userid END) / COUNT(DISTINCT a.userid) AS ctr
FROM app a
LEFT JOIN (
    SELECT userid, package_name FROM app WHERE action_type = 'download'
) d ON a.userid = d.userid AND a.package_name = d.package_name
WHERE a.action_type = 'itemClick'
GROUP BY a.package_name
HAVING click_count > 0
ORDER BY ctr DESC
LIMIT 100

该SQL语句查询的是在app表中，点击某个应用的用户数量、未下载该应用的用户点击该应用的数量、该应用的点击率，按照点击率从大到小排序，最后取前100个。

题目3

SELECT
    m.category,
    CAST(
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY TIMESTAMPDIFF(SECOND, a.time, d.time))
        AS UNSIGNED
    ) AS median_decision_time
FROM app a
JOIN mapping_table m ON a.package_name = m.package_name
JOIN (
    SELECT userid, package_name, MIN(time) AS time
    FROM app
    WHERE action_type = 'download'
    GROUP BY userid, package_name
) AS d ON a.userid = d.userid AND a.package_name = d.package_name
WHERE a.action_type = 'itemShow'
GROUP BY m.category

该SQL语句查询的是在app表中，不同应用所属的不同类别中，下载某应用的用户的决策时间中位数。其中，决策时间定义为用户从展示某应用开始到下载该应用所花费的时间。最后按照类别分组。