上周新增激活用户在上周下单情况
1、上周激活用户明细
#上周激活用户明细
SELECT a.城市,a.用户ID,a.用户名称,b.用户地址,b.联系电话,a.订单日期,c.年周,c.上周一,a.订单ID,a.产品ID,a.销售员,a.金额
FROM (
SELECT a1.城市,a1.用户ID,a1.用户名称,DATE(a1.订单日期) AS 订单日期,a1.订单ID,a1.产品ID,a1.销售员,SUM(a1.金额) AS 金额
FROM `a003_order` AS a1
WHERE a1.城市="北京" AND a1.金额>0 AND a1.订单日期>=DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AND a1.订单日期<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY))
GROUP BY a1.订单ID,a1.产品ID
ORDER BY a1.订单日期,a1.订单ID
) AS a
LEFT JOIN `a001_resterant` AS b ON a.用户ID=b.用户ID
INNER JOIN (#上周重激活新增用户明细 取交集
SELECT c6.*
FROM (
SELECT c5.城市,c5.用户ID,c5.上周首单日期,c5.年周,c5.上周一
,c5.上周首单日前30天之前金额,c5.上周首单日前30天金额,c5.上周首单日金额,
CASE
WHEN c5.上周首单日期>=c5.上周一 AND c5.上周首单日期< c5.本周一 THEN CONCAT("上周",c5.激活情况)
ELSE NULL END AS 激活情况,c5.本周一
FROM (#取上周首单 并添加用户重激活新增属性
SELECT c3.城市,c3.用户ID,c3.订单日期 AS 上周首单日期,c3.年周,
SUM(IF(DATE(c4.订单日期)<=DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND c4.金额>0,c4.金额,NULL)) AS 上周首单日前30天之前金额,
SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,c4.金额,NULL)) AS 上周首单日前30天金额,
c3.订单额 AS 上周首单日金额,
CASE
WHEN SUM(IF(DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,c4.金额,NULL)) IS NULL THEN "新增"
WHEN SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,金额,NULL)) IS NOT NULL THEN "留存"
WHEN SUM(IF(DATE(c4.订单日期)<=DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND c4.金额>0,金额,NULL)) IS NOT NULL AND SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,金额 ,NULL)) IS NULL THEN "重激活"
ELSE NULL END AS 激活情况,c3.上周一,c3.本周一
FROM (
SELECT c2.城市,c2.用户ID,c2.订单日期,c2.年周,c2.订单额,c2.本周一,c2.上周一#取出当月首单订单日期 首单销售 首单额 以这个日期往前推30天判断激活留存情况
FROM (#上周每一天
SELECT c1.城市,c1.用户ID,DATE(c1.订单日期) AS 订单日期,DATE_FORMAT(c1.订单日期,"%x%v") AS 年周,SUM(c1.金额) AS 订单额,DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AS 上周一,DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)) AS 本周一 #本月下单用户每天明细
FROM `a003_order` AS c1
WHERE c1.城市="北京" AND c1.订单日期>=DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AND c1.订单日期<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)) AND c1.金额>0
GROUP BY c1.用户ID,DATE(c1.订单日期)
) AS c2
GROUP BY c2.用户ID
) AS c3
LEFT JOIN `a003_order` AS c4 ON c4.用户ID=c3.用户ID
#where c3.用户ID=22200
GROUP BY c3.用户ID
) AS c5
WHERE c5.激活情况<>"留存"
) AS c6
WHERE c6.激活情况 IS NOT NULL
) AS c ON c.用户ID=a.用户ID
2、上周汇总
SELECT a.城市,a.用户ID,a.用户名称,d.用户地址,d.联系电话,c.上周首单日期 AS 上周激活新增日期,c.年周,c.上周一,a.上周下单天次,a.上周订单数,b.上周产品数,a.上周金额,c.激活情况,c.本周一
FROM (#上周下单天次 订单数 金额
SELECT a3.城市,a3.用户ID,a3.用户名称,COUNT(a3.订单日期) AS 上周下单天次,SUM(a3.订单数) AS 上周订单数,SUM(a3.金额2) AS 上周金额
FROM (
SELECT a2.城市,a2.用户ID,a2.用户名称,a2.订单日期,COUNT(a2.订单ID) AS 订单数,SUM(a2.金额1) AS 金额2
FROM (
SELECT a1.城市,a1.用户ID,a1.用户名称,DATE(订单日期) AS 订单日期,a1.订单ID,SUM(金额) AS 金额1
FROM `a003_order` AS a1
WHERE a1.城市="北京" AND a1.金额>0 AND a1.订单日期>=DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AND a1.订单日期<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY))
GROUP BY a1.订单ID
) AS a2
GROUP BY a2.用户ID,a2.订单日期
) AS a3
GROUP BY a3.用户ID
) AS a
LEFT JOIN (#上周产品数
SELECT b2.城市,b2.用户ID,COUNT(b2.产品ID) AS 上周产品数
FROM (
SELECT b1.城市,b1.用户ID,b1.产品ID,SUM(金额) AS 金额
FROM `a003_order` AS b1
WHERE b1.城市="北京" AND b1.金额>0 AND b1.订单日期>=DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AND b1.订单日期<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY))
GROUP BY b1.用户ID,b1.产品ID
) AS b2
GROUP BY b2.用户ID
) AS b ON a.用户ID=b.用户ID
INNER JOIN (#上周重激活新增用户明细 取交集
SELECT c6.*
FROM (
SELECT c5.城市,c5.用户ID,c5.上周首单日期,c5.年周,c5.上周一
,c5.上周首单日前30天之前金额,c5.上周首单日前30天金额,c5.上周首单日金额,
CASE
WHEN c5.上周首单日期>=c5.上周一 AND c5.上周首单日期< c5.本周一 THEN CONCAT("上周",c5.激活情况)
ELSE NULL END AS 激活情况,c5.本周一
FROM (#取上周首单 并添加用户重激活新增属性
SELECT c3.城市,c3.用户ID,c3.订单日期 AS 上周首单日期,c3.年周,
SUM(IF(DATE(c4.订单日期)<=DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND c4.金额>0,c4.金额,NULL)) AS 上周首单日前30天之前金额,
SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,c4.金额,NULL)) AS 上周首单日前30天金额,
c3.订单额 AS 上周首单日金额,
CASE
WHEN SUM(IF(DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,c4.金额,NULL)) IS NULL THEN "新增"
WHEN SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,金额,NULL)) IS NOT NULL THEN "留存"
WHEN SUM(IF(DATE(c4.订单日期)<=DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND c4.金额>0,金额,NULL)) IS NOT NULL AND SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,金额 ,NULL)) IS NULL THEN "重激活"
ELSE NULL END AS 激活情况,c3.上周一,c3.本周一
FROM (
SELECT c2.城市,c2.用户ID,c2.订单日期,c2.年周,c2.订单额,c2.本周一,c2.上周一#取出当月首单订单日期 首单销售 首单额 以这个日期往前推30天判断激活留存情况
FROM (#上周每一天
SELECT c1.城市,c1.用户ID,DATE(c1.订单日期) AS 订单日期,DATE_FORMAT(c1.订单日期,"%x%v") AS 年周,SUM(c1.金额) AS 订单额,DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AS 上周一,DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)) AS 本周一 #本月下单用户每天明细
FROM `a003_order` AS c1
WHERE c1.城市="北京" AND c1.订单日期>=DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AND c1.订单日期<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)) AND c1.金额>0
GROUP BY c1.用户ID,DATE(c1.订单日期)
) AS c2
GROUP BY c2.用户ID
) AS c3
LEFT JOIN `a003_order` AS c4 ON c4.用户ID=c3.用户ID
#where c3.用户ID=22200
GROUP BY c3.用户ID
) AS c5
WHERE c5.激活情况<>"留存"
) AS c6
WHERE c6.激活情况 IS NOT NULL
) AS c ON c.用户ID=a.用户ID
LEFT JOIN `a001_resterant` AS d ON d.用户ID=a.用户ID
ORDER BY a.用户ID,c.上周首单日期