WITH ranked as (
SELECT *, rank()over(partition by tx_sender ORDER by block_timestamp) as rank
FROM terra.core.fact_transactions
),
advance as (
SELECT b.block_timestamp as dates,
a.tx_sender as wallet
FROM ranked a, ranked b
WHERE a.rank = b.rank-1 and a.tx_sender = b.tx_sender
AND datediff (day, a.block_timestamp , a.block_timestamp)<=30
)
, active_wallets as (
SELECT wallet
FROM advance
WHERE date_trunc('day',dates) = current_date -30
),
tx_fee as (
SELECT tx_sender,
sum(fee) as tx_fee
FROM terra.core.fact_transactions
WHERE tx_sender IN (SELECT wallet FROM active_wallets)
GROUP BY tx_sender
)
SELECT avg(tx_fee) as avg_fee,
median(tx_fee) as median_fee
FROM tx_fee