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

这就是SQL题带给我的自信😕

可能发题解到博客上，主要是写完一遍实在不想看第二遍，太长了，优化都不想优化，看着头疼。

技术栈 – WhiteNight's Site

更新：MySQL例题：业务查询 – WhiteNight's Site 。大致写了下思路。

一

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT A.date,IFNULL(ROUND(COUNT(DISTINCT E.user_id)/COUNT(DISTINCT C.user_id),3),0) AS p
FROM login AS A
LEFT JOIN(
SELECT *
FROM login AS B  
WHERE B.date=(
    SELECT MIN(D.date)
    FROM login AS D 
    WHERE B.user_id=D.user_id
    GROUP BY D.user_id
    LIMIT 1
)) AS C
ON A.date=C.date AND A.user_id=C.user_id
LEFT JOIN login AS E 
ON A.user_id=E.user_id AND DATE_ADD(A.date,INTERVAL 1 DAY)=E.date
GROUP BY A.date
ORDER BY A.date ASC
########## End ##########

 二

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT t.user_id,MIN(t.date) AS first_buy_date,MAX(t.date) AS second_buy_date,MAX(t.cnt) AS cnt
FROM(
    SELECT *,COUNT(B.user_id)over(partition by B.user_id) AS cnt,row_number()over(partition by B.user_id order by B.date ASC) AS rk
    FROM order_info AS B 
    WHERE B.status!="no_completed"
    AND B.date>'2021-10-15'
    AND IF(B.product_name="C++" OR B.product_name="JAVA" OR B.product_name="Python",1,0)=1
)t
WHERE t.rk<=2
GROUP BY t.user_id
HAVING COUNT(t.user_id)>=2
########## End ##########

三

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT t2.product_name,t2.user_id,t2.rnk,CONCAT(ROUND(t2.incomp_rate,2),'%') AS incomp_rate
FROM(
    SELECT *,dense_rank()over(partition by t.product_name order by t.incomp_rate DESC) AS rnk
    FROM (
SELECT A.user_id,A.product_name,ROUND(COUNT(IF(A.status='no_completed',1,NULL))*100/COUNT(A.status),4) AS incomp_rate
FROM order_info AS A
WHERE A.date>='2021-10-16' AND A.date<='2021-10-31'
AND EXISTS(
    SELECT 1
    FROM order_info AS B 
    WHERE B.user_id=A.user_id AND B.product_name=A.product_name
    AND B.date>='2021-10-16' AND B.date<='2021-10-31'
    AND B.status='no_completed'
)
GROUP BY A.user_id,A.product_name
    )t
)AS t2
WHERE t2.rnk<=3
ORDER BY t2.product_name ASC,t2.rnk ASC
########## End ##########