sql - 统计用户回头率

Question

我想求用户返回率。

我想知道用户是否在本月的第一天来过，他们是否在本月的剩余时间回来过。我有很多跨越几年的数据。

Table_01:
    -----------------------------------------------
        EVENT_DATE          | EVENT_ID |  USER_ID
    -----------------------------------------------
    2014-08-01 00:00:00.000 | 0021253  |   153
    2014-08-01 00:00:00.000 | 0021254  |   522
    2014-08-02 00:00:00.000 | 0021255  |   153
    2014-08-03 00:00:00.000 | 0021256  |   953
    2014-08-04 00:00:00.000 | 0021257  |   683
    2014-08-05 00:00:00.000 | 0021258  |   153
    2014-08-08 00:00:00.000 | 0021259  |   853
    2014-08-08 00:00:00.000 | 0021260  |   653
    2015-08-01 00:00:00.000 | 0022253  |   622
    2015-08-01 00:00:00.000 | 0022254  |   653
    2015-08-01 00:00:00.000 | 0022254  |   953
    2015-08-02 00:00:00.000 | 0022255  |   753
    2015-08-03 00:00:00.000 | 0022256  |   953
    2015-08-04 00:00:00.000 | 0022257  |   683
    2015-08-05 00:00:00.000 | 0022258  |   753
    2015-08-10 00:00:00.000 | 0022259  |   853
    2015-08-10 00:00:00.000 | 0022260  |   653

Answer: 08/2014 : 50%
        08/2015 : 66%

Microsoft SQL Server 2016。兼容级别:SQL Server 2005 (90)

Answer 1

像这样？

SELECT tab.mnt
       , tab.year
       , SUM(CASE WHEN tab.cnt > 1 AND tab.frst >= 1 THEN 1 ELSE 0 END)/SUM(CASE WHEN tab.frst >= 1 THEN 1 ELSE 0 END)
  FROM (SELECT MONTH(event_date) AS mnt
               , YEAR(event_date) AS year
               , user_id
               , SUM(CASE WHEN DAY(event_date) = 1 THEN 1 ELSE 0 END) AS frst
               , COUNT(*) AS cnt
          FROM table_01
         GROUP BY MONTH(event_date), YEAR(event_date), user_id) tab 
WHERE tab.frst >= 1
GROUP BY tab.mnt, tab.year