MySQL11月16-11月21日活动赠送的优惠券使用率_20161124

一、11.16到21号活动规则是 单笔订单最高的金额划分客户为399,799,1599元三档 达标的分别赠送对应的优惠券 优惠券ID有标号区间

SELECT a.*,CASE WHEN 金额<399 THEN "无优惠券" WHEN 金额>=399 AND 金额<799 THEN "399档" WHEN 金额>=799 AND 金额<1599 THEN "799档" WHEN 金额>=1599 THEN "1599档" ELSE NULL END AS 档位,b.联系电话
FROM (
    SELECT 城市,用户ID,用户名称,DATE(订单日期) AS 订单日期,订单ID AS 单笔最高订单ID,销售员,SUM(金额) AS 金额
    FROM a003_order 
    WHERE 城市='杭州' AND 订单日期>="2016-11-16" AND 订单日期<"2016-11-22" AND 金额>0 #and 用户ID=57012
    GROUP BY 订单ID
    ORDER BY 用户ID,SUM(金额) DESC 
) AS a
LEFT JOIN a001_resterant AS b ON a.用户ID=b.用户ID
GROUP BY a.用户ID
ORDER BY 金额 DESC 

二、由于达标客户会分多张优惠券 因此我们将发放优惠券表和使用优惠券表连接起来 以发放表为主表 后面表为从表 

这样可以查看给每个用户每张优惠券在未来一段时间内的使用情况

SELECT a.*,b.使用日期,b.订单号,b.下单金额,CASE WHEN b.订单号 IS NULL THEN "未使用" ELSE "使用" END AS 优惠券使用状态
FROM (#发放的用户明细 一个用户分配多个优惠券ID
    SELECT 城市,DATE(发放日期) AS 发放日期,用户ID,用户名称,优惠券ID,优惠券名称
    ,RIGHT(LEFT(优惠券名称,INSTR(优惠券名称,"档")-0),CHAR_LENGTH(LEFT(优惠券名称,INSTR(优惠券名称,"档")-0))-CHAR_LENGTH(LEFT(优惠券名称,INSTR(优惠券名称,"【")))) AS 档位,金额 AS 优惠券金额
    FROM a015_customer_coupon
    WHERE 城市='杭州' AND 发放日期>="2016-11-23" AND 优惠券ID>=1165 AND 优惠券ID<=1178 AND 状态<>'已失效'
) AS a
LEFT JOIN (#每个优惠券ID使用情况
    SELECT 城市,DATE(使用时间) AS 使用日期,用户ID,用户名称,订单号,下单金额,优惠券ID,优惠券名称,RIGHT(LEFT(优惠券名称,INSTR(优惠券名称,"档")-0),CHAR_LENGTH(LEFT(优惠券名称,INSTR(优惠券名称,"档")-0))-CHAR_LENGTH(LEFT(优惠券名称,INSTR(优惠券名称,"【")))) AS 档位,优惠券类别,优惠券金额
    FROM a016_order_customercoupon_xref
    WHERE 城市='杭州' AND 使用时间<CURRENT_DATE AND 使用时间>="2016-11-23" AND 使用时间<"2016-12-16" AND 优惠券ID>=1165 AND 优惠券ID<=1178 
) AS b ON a.用户ID=b.用户ID AND a.优惠券ID=b.优惠券ID
WHERE a.用户ID IN (
    SELECT 用户ID
    FROM a015_customer_coupon
    WHERE 城市='杭州' AND 发放日期>="2016-11-23" AND 优惠券ID>=1165 AND 优惠券ID<=1178 AND 状态<>'已失效'
)  
ORDER BY 优惠券使用状态,b.使用日期 

三、使用率：用excel countifs 计算