with id_users as ( select date_trunc('day',BLOCK_TIMESTAMP) date, from_address user, sum(ETH_VALUE) vol from ethereum.core.fact_transactions WHERE TO_ADDRESS = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' and status = 'SUCCESS' AND ETH_VALUE > 0 --and date >= '2022-01-01' group by date, user order by date ), hold_sell as ( select date_trunc('day',BLOCK_TIMESTAMP) date, origin_from_address user, sum(amount_in) sell_vol from ethereum.core.ez_dex_swaps where symbol_in = 'stETH' and user in (select user from id_users) group by date, user order by date ), raw_data as ( select id_users.user, id_users.date stake_date, id_users.vol stake_vol, hold_sell.date sell_date, hold_sell.sell_vol from id_users full join hold_sell on id_users.user = hold_sell.user ), table1 as ( select raw_data.user, raw_data.stake_date, raw_data.stake_vol, case when raw_data.sell_date is NULL then current_date else raw_data.sell_date end as sell_date, raw_data.sell_vol, datediff('day',raw_data.stake_date,raw_data.sell_date) hold_duration, case when sell_date = current_date then 'Held' else 'Sold' end as Held_Sold from raw_data ), table2 as ( select distinct(user) user, sum(stake_vol) staked_vol, sum(sell_vol) sold_vol, avg(hold_duration) hold_duration from table1 where hold_duration > 0 group by user ), table3 as ( select user, hold_duration, row_number() over (order by hold_duration DESC) "Row Number" from table2 order by hold_duration desc ) select '<= 100' "Average Hold Duration in Days", count(user) "No. of Users", round(100*count(user)/(select count(user) from table3),2) "Percent of Total Users" from table3 where hold_duration <= 100 union all select '> 100 and <= 200' "Average Hold Duration in Days", count(user) "No. of Users", round(100*count(user)/(select count(user) from table3),2) "Percent of Total Users" from table3 where hold_duration > 100 and hold_duration <= 200 union all select '> 200 and <= 300' "Average Hold Duration in Days", count(user) "No. of Users", round(100*count(user)/(select count(user) from table3),2) "Percent of Total Users" from table3 where hold_duration > 200 and hold_duration <= 300 union all select '> 300 and <= 400' "Average Hold Duration in Days", count(user) "No. of Users", round(100*count(user)/(select count(user) from table3),2) "Percent of Total Users" from table3 where hold_duration > 300 and hold_duration <= 400 UNION ALL select '> 400 and <= 500' "Average Hold Duration in Days", count(user) "No. of Users", round(100*count(user)/(select count(user) from table3),2) "Percent of Total Users" from table3 where hold_duration > 400 and hold_duration <= 500 union all select '> 500' "Average Hold Duration in Days", count(user) "No. of Users", round(100*count(user)/(select count(user) from table3),2) "Percent of Total Users" from table3 where hold_duration > 500

Lido Bounties: HODL or Sell?