cloudr3nNew CL Trader New to avax on eth
Updated 2023-04-19
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
›
⌄
with
buyer_tb as (select event_inputs:taker as address, date(block_timestamp) as day
from avalanche.core.fact_event_logs
where origin_to_address='0xae079eda901f7727d0715aff8f82ba8295719977' and event_name in ('TakerBid','TakerAsk') and block_timestamp<='2022-08-25 16:00:00.000'
),
seller_tb as (select event_inputs:maker as address, date(block_timestamp) as day
from avalanche.core.fact_event_logs
where origin_to_address='0xae079eda901f7727d0715aff8f82ba8295719977' and event_name in ('TakerBid','TakerAsk') and block_timestamp<='2022-08-25 16:00:00.000'
),
combined_trader as (
select distinct address,'yes' as prev from buyer_tb
UNION
select distinct address,'yes' as prev from seller_tb
),
buyer_cl as (select event_inputs:taker as address, date(block_timestamp) as day
from avalanche.core.fact_event_logs
where origin_to_address='0xae079eda901f7727d0715aff8f82ba8295719977' and event_name in ('TakerBid','TakerAsk') and block_timestamp>'2022-08-25 16:00:00.000' and event_inputs:collection=lower('0xB842344669579ECf4Cee12f740520376c4CBC6d1')
),
seller_cl as (select event_inputs:maker as address, date(block_timestamp) as day
from avalanche.core.fact_event_logs
where origin_to_address='0xae079eda901f7727d0715aff8f82ba8295719977' and event_name in ('TakerBid','TakerAsk') and block_timestamp>'2022-08-25 16:00:00.000' and event_inputs:collection=lower('0xB842344669579ECf4Cee12f740520376c4CBC6d1')
),
combined_cl as (
select distinct address, day, 'yes' as cl from buyer_cl
UNION
select distinct address, day, 'yes' as cl from seller_cl
),
-- tb for CL trader
-- who didn't trader prior to Cl collection
sort as (
select agg_address, min(block_timestamp) as min_time, datediff('day',min_time,'2022-08-25 16:00:00.000') as date_diff, count(*) as tx_count
from (
select cl.address as cl_address, ct.address as ct_address, cl, prev,
case when cl='yes' and prev='yes' then 'old trader'
when cl='yes' and prev is null then 'new trader'
Run a query to Download Data