如何查询网站建设时间,赤峰建网站的电话,注册网站乱填邮箱,姓氏头像在线制作免费生成图片这就是SQL题带给我的自信#x1f615;
可能发题解到博客上#xff0c;主要是写完一遍实在不想看第二遍#xff0c;太长了#xff0c;优化都不想优化#xff0c;看着头疼。
技术栈 – WhiteNights Site
一
USE mydata;
#请在此处添加实现代码
########## Begin #######…这就是SQL题带给我的自信
可能发题解到博客上主要是写完一遍实在不想看第二遍太长了优化都不想优化看着头疼。
技术栈 – WhiteNights 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_idD.user_idGROUP BY D.user_idLIMIT 1
)) AS C
ON A.dateC.date AND A.user_idC.user_id
LEFT JOIN login AS E
ON A.user_idE.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 rkFROM order_info AS B WHERE B.status!no_completedAND B.date2021-10-15AND IF(B.product_nameC OR B.product_nameJAVA OR B.product_namePython,1,0)1
)t
WHERE t.rk2
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 rnkFROM (
SELECT A.user_id,A.product_name,ROUND(COUNT(IF(A.statusno_completed,1,NULL))*100/COUNT(A.status),4) AS incomp_rate
FROM order_info AS A
WHERE A.date2021-10-16 AND A.date2021-10-31
AND EXISTS(SELECT 1FROM order_info AS B WHERE B.user_idA.user_id AND B.product_nameA.product_nameAND B.date2021-10-16 AND B.date2021-10-31AND B.statusno_completed
)
GROUP BY A.user_id,A.product_name)t
)AS t2
WHERE t2.rnk3
ORDER BY t2.product_name ASC,t2.rnk ASC
########## End ##########
