0xHaM-dUntitled Query
Updated 2022-11-14
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
34
35
36
›
⌄
⌄
--SQL credit to https://app.flipsidecrypto.com/velocity/queries/b7972841-0d45-4c91-ba5a-9456363a7c0a
with ab as
(with raw as
(with a as (select tx_hash from optimism.core.fact_event_logs where event_name like '%Swap%' or event_name like '%swap%' group by 1)
select b.tx_hash, event_inputs:value/10e17 as vol_op, block_timestamp as t , origin_from_address as swapper from optimism.core.fact_event_logs b
inner join a on a.tx_hash=b.tx_hash
where contract_address = '0x4200000000000000000000000000000000000042' and event_name = 'Transfer')
select date(t) as date, swapper, vol_op as volume_OP_swapped_out
from raw
)
, cd as (
with raw as (select origin_from_address as wallet_address, block_timestamp as t
,tx_hash ,event_inputs:amount/10e17 as OP_claimed
from optimism.core.fact_event_logs
where
event_name = 'Claimed' and
contract_address = '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de')
select wallet_address, date(t) as date , tx_hash, OP_claimed
from raw
)
, ef as (select cd. wallet_address, sum(volume_OP_swapped_out) ax, sum(OP_claimed) as bx
/*,
case when percent_swapped>=0.9 then 'Dumped most of their $OP Airdrop'
when percent_swapped<0.9 and percent_swapped>=0.1 then 'Held part of their $OP'
when percent_swapped<0.1 then 'Held Their $OP'
end as holdings*/
from cd left join ab on
cd.wallet_address=ab.swapper
--where claim_date>swap_date
Run a query to Download Data