实验11 SQL互联网业务查询-2

第1关 查询某网站每个日期新用户的次日留存率

USE mydata;
#请在此处添加实现代码
########## Begin ##########
with Table_a as(
    SELECT
        user_id,
        MIN(date) date
    FROM login
    GROUP BY user_id
)
SELECT
    Table_a.date,
    ROUND (COUNT(DISTINCT login.user_id)/COUNT(Table_a.user_id),3) p
FROM Table_a
LEFT JOIN login ON Table_a.user_id = login.user_id AND DATEDIFF(login.date,Table_a.date) = 1
GROUP BY Table_a.date
UNION
SELECT
    login.date,
    0.000
FROM login
WHERE date NOT IN(
    SELECT date
    FROM Table_a
)
ORDER BY date
########## End ##########

第2关 查询满足条件的用户

USE mydata;

#请在此处添加实现代码
########## Begin ##########
SELECT
    user_id,
    MIN(CASE WHEN num = 1 THEN date END) first_buy_date,
    MAX(CASE WHEN num = 2 THEN date END) second_buy_date,
    COUNT(*) cnt
FROM (
    SELECT
        *,
        row_number() over(partition by user_id order by date) num
    FROM order_info
    WHERE status = 'completed'
    AND date > '2021-10-15'
    AND product_name IN ('C++','Python','Java')
) a
GROUP BY user_id
HAVING COUNT(1)>=2
########## End ##########

第3关 查询未完成订单率排名前三的用户

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT
    product_name,
    user_id,
    rnk,
    CONCAT(FORMAT(incomp_rate*100, 2), '%') incomp_rate
FROM(
    SELECT
        product_name,
        user_id,
        DENSE_RANK() OVER (PARTITION BY product_name ORDER BY SUM(IF(status = 'no_completed',1,0)) / COUNT(*) DESC)  rnk,
        ROUND(SUM(IF(status = 'no_completed',1,0))/COUNT(*),4) incomp_rate
    FROM order_info
    WHERE date BETWEEN '2021-10-16' AND '2021-10-31'
    GROUP BY product_name,user_id
    HAVING incomp_rate > 0
    ORDER BY product_name,rnk
) a
GROUP BY product_name,user_id
HAVING rnk <= 3

########## End ##########