SELECT MIN(BLOCK_TIMESTAMP) AS MD,

payer

FROM flow.core.fact_transactions

GROUP BY 2

), COME_BACK AS (

SELECT MIN(t.BLOCK_TIMESTAMP) AS "TIME OF COME BACK",

COALESCE(MIN(TIMESTAMPDIFF('day',f.md,t.BLOCK_TIMESTAMP)),-1) AS "COME BACK DATE",

f.payer,

MIN(f.md) AS MD,

COUNT(t.BLOCK_TIMESTAMP) AS CB

FROM flow.core.fact_transactions t RIGHT JOIN FIRST_TIME f ON f.payer=t.payer AND f.MD<t.BLOCK_TIMESTAMP

GROUP BY 3

)

SELECT

"COME BACK DATE",

COUNT(*) as "NUMBER OF COME BACK",

CASE

WHEN "COME BACK DATE" = -1 THEN 'E:DONT COME BACK'

WHEN "COME BACK DATE" = 0 THEN 'A:WITHIN A DAY'

WHEN "COME BACK DATE" BETWEEN 0 AND 8 THEN 'B:WITHIN A WEEK'

WHEN "COME BACK DATE" BETWEEN 7 AND 31 THEN 'C:WITHIN A MONTH'

ELSE 'D:AFTER A MONTH'

END AS LABEL

from COME_BACK

GROUP BY 1

ORDER BY 1