flipside

    Wallets ROI including holding key prices cohorts USD
    AMLBotWallets ROI including holding key prices cohorts USD
    Updated 2024-03-07
    -- forked from Wallets ROI including holding key prices cohorts % @ https://flipsidecrypto.xyz/edit/queries/6a91a2f3-46a1-4a69-950a-6d1deb338eb9

    -- forked from Wallets ROI including holding key prices @ https://flipsidecrypto.xyz/edit/queries/f97eb2bd-c1d7-4147-acd5-16728b996168

    with eth_price as(
    select
    hour,
    avg(price) price
    from ethereum.price.ez_hourly_token_prices
    where
    token_address = lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')
    and date_trunc('day', hour) >= '2023-08-10'
    group by hour
    ),
    raw_trades as(select
    block_timestamp,
    tx_hash,
    decoded_log:trader trader_wallet,
    case
    when decoded_log:isBuy::boolean = 'true' then 'Buy'
    when decoded_log:isBuy::boolean = 'false' then 'Sell'
    end trade_type,
    decoded_log:ethAmount/1e18 eth_amount,
    eth_amount*coalesce(price, lag(price) ignore nulls over(order by eth_price.hour)) amount_usd,
    decoded_log:shareAmount keys,
    decoded_log:supply keys_supply,
    decoded_log:subject subject_wallet,
    decoded_log:protocolEthAmount/1e18 protocol_revenue,
    protocol_revenue*coalesce(price, lag(price) ignore nulls over(order by eth_price.hour)) protocol_revenue_usd,
    decoded_log:subjectEthAmount/1e18 subject_revenue,
    subject_revenue*coalesce(price, lag(price) ignore nulls over(order by eth_price.hour)) subject_revenue_usd
    from base.core.fact_decoded_event_logs logs
    left join eth_price
    on date_trunc('hour', logs.block_timestamp) = eth_price.hour
    where contract_address = '0xcf205808ed36593aa40a44f10c7f7c2f67d4a4d4'
    and trader_wallet is not null
    Last run: about 1 year ago
    ROI USD cohort
    WALLETS
    1
    		c. Lost 5k-10k$755
    2
    		k. Earned 10k-100k$863
    3
    		b. Lost 10k-100k$715
    4
    		e. Lost 100-1000$12987
    5
    		l. Earned more 100k$35
    6
    		a. Lost more 100k$51
    7
    		g. Earned 0-100$59007
    8
    		j. Earned 5k-10k$811
    9
    		d. Lost 1k-5k$4483
    10
    		i. Earned 1k-5k$4340
    11
    		h. Earned 100-1000$11679
    12
    		f. Lost 0-100$81541
    12
    319B
    64s