Account refund rate
I would like to find the user return rate.
I would like to know if the user came on the first day of the month if they come back during the rest of the month. I have a lot of data spanning several years.
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. Compatibility level: SQL Server 2005 (90)
+3
source to share
2 answers
Like this?
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
+2
source to share
try this:
;WITH CTE as
(
SELECT
dateadd(month,datediff(month, 0, EVENT_DATE),0) monthyear,
max(EVENT_DATE) mx,
USER_ID
FROM <yourtable>
GROUP BY datediff(month, 0, EVENT_DATE),USER_ID
HAVING day(min(EVENT_DATE)) = 1
)
SELECT
monthyear,
sum(CASE WHEN day(mx) > 1 THEN 100. ELSE 0 END)/count(*) [pct came back later same month]
FROM CTE
GROUP BY monthyear
+1
source to share