计算留存率的一个小问题，求助

[hydral]

现有如下两张表：

用户表
用户ID 注册时间
1 20150303
2 20150304
3 20150305
4 20150306

订单表
用户ID 订单创建时间
1 20150506
1 20150405
2 20150304
3 20150506
3 20150607

数据抽取起始时间2015年1月 结束时间2015年7月
计算隔月留存率 ：当月有首笔交易用户数 次月仍有交易用户数

请教大神们，SQL该如何写，谢谢！

[Tiger_Zhao]

WITH /* 测试数据
用户表(用户ID,注册时间)AS(
    SELECT 1,'20150303' UNION ALL
    SELECT 2,'20150304' UNION ALL
    SELECT 3,'20150305' UNION ALL
    SELECT 4,'20150306'
)
,订单表(用户ID,订单创建时间)AS(
    SELECT 1,'20150506' UNION ALL
    SELECT 1,'20150405' UNION ALL
    SELECT 2,'20150304' UNION ALL
    SELECT 3,'20150506' UNION ALL
    SELECT 3,'20150607'
),*/
m AS (
    SELECT DATEADD(month,number,'2015-01-01') d0,
           DATEADD(month,number+1,'2015-01-01') d1,
           DATEADD(month,number+2,'2015-01-01') d2
      FROM master..spt_values
     WHERE type = 'p'
       AND number < 7
)
    SELECT CONVERT(varchar(7),m.d0,120) 月,
           COUNT(DISTINCT o1.用户ID) 当月有首笔交易用户数,
           COUNT(DISTINCT o2.用户ID) 次月仍有交易用户数
      FROM m
      JOIN 用户表 u
        ON u.注册时间 >= m.d0
       AND u.注册时间 < m.d1
      JOIN 订单表 o1
        ON u.用户ID = o1.用户ID
       AND o1.订单创建时间 >= m.d0
       AND o1.订单创建时间 < m.d1
 LEFT JOIN 订单表 o2
        ON u.用户ID = o2.用户ID
       AND o2.订单创建时间 >= m.d0
       AND o2.订单创建时间 < m.d1
  GROUP BY CONVERT(varchar(7),m.d0,120)

月      当月有首笔交易用户数 次月仍有交易用户数
------- -------------------- ------------------
2015-03                    1                  1

[hydral]

引用 1 楼 Tiger_Zhao 的回复:

WITH /* 测试数据
用户表(用户ID,注册时间)AS(
    SELECT 1,'20150303' UNION ALL
    SELECT 2,'20150304' UNION ALL
    SELECT 3,'20150305' UNION ALL
    SELECT 4,'20150306'
)
,订单表(用户ID,订单创建时间)AS(
    SELECT 1,'20150506' UNION ALL
    SELECT 1,'20150405' UNION ALL
    SELECT 2,'20150304' UNION ALL
    SELECT 3,'20150506' UNION ALL
    SELECT 3,'20150607'
),*/
m AS (
    SELECT DATEADD(month,number,'2015-01-01') d0,
           DATEADD(month,number+1,'2015-01-01') d1,
           DATEADD(month,number+2,'2015-01-01') d2
      FROM master..spt_values
     WHERE type = 'p'
       AND number < 7
)
    SELECT CONVERT(varchar(7),m.d0,120) 月,
           COUNT(DISTINCT o1.用户ID) 当月有首笔交易用户数,
           COUNT(DISTINCT o2.用户ID) 次月仍有交易用户数
      FROM m
      JOIN 用户表 u
        ON u.注册时间 >= m.d0
       AND u.注册时间 < m.d1
      JOIN 订单表 o1
        ON u.用户ID = o1.用户ID
       AND o1.订单创建时间 >= m.d0
       AND o1.订单创建时间 < m.d1
 LEFT JOIN 订单表 o2
        ON u.用户ID = o2.用户ID
       AND o2.订单创建时间 >= m.d0
       AND o2.订单创建时间 < m.d1
  GROUP BY CONVERT(varchar(7),m.d0,120)

月      当月有首笔交易用户数 次月仍有交易用户数
------- -------------------- ------------------
2015-03                    1                  1

非常感谢！ 还有有一种情况用户注册之后，当月可能没有订单，过了几个月之后才产生首单，这种情况该如何处理呢？

[Tiger_Zhao]

那么不需要用户表了，把首单日期当作注册时间用。
还有上面o2的关联条件写错了。

WITH /* 测试数据
订单表(用户ID,订单创建时间)AS(
    SELECT 1,'20150506' UNION ALL
    SELECT 1,'20150405' UNION ALL
    SELECT 2,'20150304' UNION ALL
    SELECT 3,'20150506' UNION ALL
    SELECT 3,'20150607'
), */
u AS (
    SELECT 用户ID,
           MIN(订单创建时间) AS 注册时间
      FROM 订单表
  GROUP BY 用户ID
)
,m AS (
    SELECT DATEADD(month,number,'2015-01-01') d0,
           DATEADD(month,number+1,'2015-01-01') d1,
           DATEADD(month,number+2,'2015-01-01') d2
      FROM master..spt_values
     WHERE type = 'p'
       AND number < 7
)
    SELECT CONVERT(varchar(7),m.d0,120) 月,
           COUNT(DISTINCT o1.用户ID) 当月有首笔交易用户数,
           COUNT(DISTINCT o2.用户ID) 次月仍有交易用户数
      FROM m
      JOIN u
        ON u.注册时间 >= m.d0
       AND u.注册时间 < m.d1
      JOIN 订单表 o1
        ON u.用户ID = o1.用户ID
       AND o1.订单创建时间 >= m.d0
       AND o1.订单创建时间 < m.d1
 LEFT JOIN 订单表 o2
        ON u.用户ID = o2.用户ID
       AND o2.订单创建时间 >= m.d1
       AND o2.订单创建时间 < m.d2
  GROUP BY CONVERT(varchar(7),m.d0,120)

月      当月有首笔交易用户数 次月仍有交易用户数
------- -------------------- ------------------
2015-03                    1                  0
2015-04                    1                  1
2015-05                    1                  1

又：回复请不要大段包含原文。