with swap_setup as (

select

to_address as wallet,

tx_id,

max(case when token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' and amount_in > 0 then 'ETH to ALCX'

when token_address = '0xdbdb4d16eda451d0503b854cf79d55697f90c8df' and amount_in > 0 then 'ALCX to ETH'

END) as trade_type,

sum(amount_in)/1e18 as amount_in,

sum(amount_out)/1e18 as amount_out,

sum(amount_usd) as amount_usd

from ethereum.dex_swaps etx

WHERE etx.block_timestamp >= '2021-02-21'

and pool_name = 'WETH-ALCX LP'

group by 1,2

, swaps_agg as (

select wallet

,sum(case when trade_type = 'ALCX to ETH' then amount_in else 0 END) as alcx_traded_to_eth_vol

,sum(case when trade_type = 'ETH to ALCX' then amount_in else 0 END) as eth_traded_to_alcx_vol

,sum(case when trade_type = 'ALCX to ETH' then amount_in else 0 END) / sum(case when trade_type = 'ETH to ALCX' then amount_in else 0.01 END) as alcx_to_eth_ratio

,count(tx_id) as num_trades

,sum(amount_usd) as swap_amount_usd

from swap_setup

group by 1

order by 2 desc)

,claims as (

SELECT

to_address as wallet,

min(block_timestamp) as claim_first,

max(block_timestamp) as claim_last,

count(tx_id) as claim_num,