WITH user_tab as
(
SELECT
date_trunc ('week',block_timestamp) as weekly_timestamps,
COUNT(date_trunc ('day',block_timestamp)) as day_timestamps,
TX_SENDER as Users,
COUNT(distinct tx_id) as transactions_count
FROM
terra.core.fact_transactions
WHERE
TX_SUCCEEDED = 'TRUE'
GROUP BY 1,3),
main_t as
(
SELECT
TX_SENDER as user,
COUNT(distinct tx_id) as tx_count
FROM
terra.core.fact_transactions
WHERE
TX_SUCCEEDED = 'TRUE' AND user IN (SELECT Users from user_tab)
group by 1),
user_last_t as
(
SELECT
distinct user as user_addresses,
case when tx_count = 1 then 'one transaction -unactive users-'
WHEN tx_count >= 2 and tx_count <= 50 then 'between 2 and 50 tx'
when tx_count > 50 and tx_count <= 100 then 'between 50 and 100 tx'
when tx_count > 100 and tx_count <= 500 then 'between 100 and 500 tx'
when tx_count > 500 and tx_count <= 1000 then 'between 500 and 1000 tx'
when tx_count > 1000 then 'more than 1000 transaction'
end as count_transactions
FROM