0xaimanTop 5 Biggest LP with New Depositor
Updated 2021-12-15
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 c as ( with a as(select to_address_name, origin_address,min(block_timestamp) as first --block_timestamp as t, TX_TO_ADDRESS_NAME as pool, event_inputs:user as sender --tx_to_label_subtype
from ethereum.udm_events
where to_label='sushiswap' AND TO_LABEL_SUBTYPE='pool' and
origin_function_name='execute' and
amount>0
group by 1,2
),
b as (
select block_timestamp as t,to_address_name, origin_address --block_timestamp as t, TX_TO_ADDRESS_NAME as pool, event_inputs:user as sender --tx_to_label_subtype
from ethereum.udm_events
where to_label='sushiswap' AND TO_LABEL_SUBTYPE='pool' and
origin_function_name='execute'
)
select --date_trunc('day',t) as date,
b.to_address_name, count ( distinct a.origin_address) as n_new_lp_depositor
--sum(n_new_lp_depositor) OVER(ORDER BY date asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_new_lp_depositor
from A
inner join b on a.first=b.T
where t>'2021-11-15'
group by 1 order by 2 desc),
d as
(
select to_address_name, count( origin_address) as n_user --block_timestamp as t, TX_TO_ADDRESS_NAME as pool, event_inputs:user as sender --tx_to_label_subtype
from ethereum.udm_events
where to_label='sushiswap' AND TO_LABEL_SUBTYPE='pool' and
origin_function_name='execute' and
block_timestamp>'2021-11-15'
group by 1 order by 1