SELECT
EXTRACT(YEAR FROM t3.t2date) AS YEAR,
EXTRACT(MONTH FROM t3.t2date) AS MONTH,
t3.user_type,
COUNT(DISTINCT t3.buyer_id) AS total_users,
COUNT(DISTINCT t3.order_id) AS total_orders,
SUM(t3.total) AS total_gmv
FROM
(WITH t1 AS
(SELECT
DISTINCT
DATE(payed_at) AS t1date,
buyer_id
FROM shared.orders),

 t2 AS
(SELECT
t1.buyer_id,
t1.t1date AS t2date,
countif(DATE(payed_at) BETWEEN DATE_ADD(t1.t1date, INTERVAL -60 DAY) AND DATE_ADD(t1.t1date, INTERVAL -31 DAY)  ) AS prev_order,
countif(DATE(payed_at) BETWEEN DATE_ADD(t1.t1date, INTERVAL -30 DAY) AND DATE_ADD(t1.t1date, INTERVAL -1 DAY)  ) AS current_order
FROM shared.orders
JOIN t1 ON t1.buyer_id=orders.buyer_id
GROUP BY 1,2)


SELECT
t1.buyer_id,
order_id,
t1.t1date ,
t2.t2date ,

DATE(first_order_at) AS first_order,
t2.current_order,
t2.prev_order,


CASE WHEN t1.t1date BETWEEN DATE(first_order_at) AND DATE_ADD(DATE(first_order_at), INTERVAL 29 DAY) THEN 'new'
WHEN t1.t1date>DATE_ADD(DATE(first_order_at), INTERVAL 29 DAY) AND t2.prev_order>0 THEN 'existing'
WHEN t1.t1date>DATE_ADD(DATE(first_order_at), INTERVAL 29 DAY) AND t2.prev_order=0 THEN 'reactivated'
ELSE 'wtf' END AS user_type,
total


FROM shared.orders
JOIN t1 ON t1.buyer_id=orders.buyer_id AND t1.t1date=DATE(orders.payed_at)
JOIN t2 ON t2.buyer_id=orders.buyer_id AND t2.t2date=DATE(orders.payed_at)
ORDER BY 1,2,3) AS t3
GROUP BY 1,2,3
ORDER BY 1,2,3,4