select block_date,count(*) as trade_count,count(distinct taker) as active_user_count,sum(amount_usd) as trade_amountfrom dex.tradeswhere block_date >=date('2022-10-01')group by1order by1
with trade_summary as (select block_date,count(*) as trade_count,count(distinct taker) as active_user_count,sum(amount_usd) as trade_amountfrom dex.tradeswhere blockchain ='ethereum'and block_date >=date('2021-01-01')and token_pair <>'POP-WETH'-- Exclude outlier that has wrong amountgroup by1order by1),user_initial_trade as (select taker,min(block_date) as initial_trade_datefrom dex.tradeswhere blockchain ='ethereum'and block_date >=date('2021-01-01')and token_pair <>'POP-WETH'-- Exclude outlier that has wrong amountgroup by1),new_user_summary as (select initial_trade_date,count(taker) as new_user_countfrom user_initial_tradegroup by1order by1)select t.block_date, trade_count, active_user_count, trade_amount, new_user_count, active_user_count - new_user_count as existing_user_count,sum(trade_count) over (order by t.block_date) as accumulate_trade_count,sum(trade_amount) over (order by t.block_date) as accumulate_trade_amount,sum(new_user_count) over (order by u.initial_trade_date) as accumulate_new_user_count, (sum(trade_count) over ()) / 1e6 as total_trade_count, (sum(trade_amount) over ()) / 1e9 total_trade_amount, (sum(new_user_count) over ()) / 1e6 as total_new_user_countfrom trade_summary tleft join new_user_summary u on t.block_date = u.initial_trade_dateorder by t.block_date
with top_token_pair as (select token_pair,count(*) as transaction_countfrom dex.tradeswhere blockchain ='ethereum'and block_date >=date('2021-01-01')and token_pair <>'POP-WETH'-- Exclude outlier that has wrong amountgroup by1order by2desclimit20)select date_trunc('month', block_date) as block_date, token_pair,count(*) as trade_count,count(distinct taker) as active_user_count,sum(amount_usd) as trade_amountfrom dex.tradeswhere blockchain ='ethereum'and block_date >=date('2021-01-01')and token_pair in (select token_pair from top_token_pair )group by1, 2order by1, 2
select block_date,count(*) as trade_count,count(distinct taker) as active_user_count,sum(amount_usd) as trade_amountfrom uniswap_ethereum.tradeswhere block_date >=date('2022-01-01')group by1order by1
with top_token_pair as (select token_pair,count(*) as transaction_countfrom uniswap_ethereum.tradeswhere blockchain ='ethereum'and block_date >=date('2022-01-01')group by1order by2desclimit20)select date_trunc('month', block_date) as block_date, token_pair,count(*) as trade_count,count(distinct taker) as active_user_count,sum(amount_usd) as trade_amountfrom uniswap_ethereum.tradeswhere blockchain ='ethereum'and block_date >=date('2022-01-01')and token_pair in (select token_pair from top_token_pair )group by1, 2order by1, 2
with monthly_active_user as (select distinct taker asaddress, date_trunc('month', block_date) as active_trade_monthfrom uniswap_v3_ethereum.trades),user_initial_trade as (select taker asaddress,min(date_trunc('month', block_date)) as initial_trade_monthfrom uniswap_v3_ethereum.tradesgroup by1),user_status_detail as ( select coalesce(c.active_trade_month, date_trunc('month', p.active_trade_month + interval '45' day)) as trade_month,
coalesce(c.address, p.address) asaddress, (casewhen n.address is not nullthen1else0end) as is_new, (case when n.address is null and c.address is not null and p.address is not null then 1 else 0 end) as is_retained,
(casewhen n.address isnulland c.address isnulland p.address is not nullthen1else0end) as is_churned, (casewhen n.address isnulland c.address is not nulland p.address isnullthen1else0end) as is_returnedfrom monthly_active_user c full join monthly_active_user p on p.address = c.address and p.active_trade_month = date_trunc('month', c.active_trade_month - interval '5' day)
left join user_initial_trade n on n.address = c.address and n.initial_trade_month = c.active_trade_monthwherecoalesce(c.active_trade_month, date_trunc('month', p.active_trade_month + interval '45'day)) < current_date),user_status_summary as (select trade_month,address, (casewhensum(is_new) >=1then'New'whensum(is_retained) >=1then'Retained'whensum(is_churned) >=1then'Churned'whensum(is_returned) >=1then'Returned'end) as user_statusfrom user_status_detailgroup by1, 2),monthly_summary as (select trade_month, user_status,count(address) as user_countfrom user_status_summarygroup by1, 2)select trade_month, user_status, (casewhen user_status ='Churned'then-1* user_count else user_count end) as user_countfrom monthly_summaryorder by1, 2