ElBCopy of ALCX Claims by Wallet
Updated 2022-11-28
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
›
⌄
⌄
with 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
--case when token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then amount_in END as eth_for_alcx
--case when token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then amount_out END as alcx_for_eth
--case when token_address = '0xdbdb4d16eda451d0503b854cf79d55697f90c8df' then amount_in END as alcx_for_eth
from ethereum.dex_swaps etx
WHERE etx.block_timestamp >= '2021-02-27'
and pool_name = 'WETH-ALCX LP'
group by 1,2
)
--, setup2 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 amount_usd
from setup
group by 1
order by 2 desc
/*select wallet, trade_type
, count(tx_id) as num_trades, sum(amount_in), sum(amount_out), sum(amount_usd)
from setup
group by 1,2)*/
Run a query to Download Data