superflyUntitled Query
Updated 2022-08-25Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
›
⌄
with min as (SELECT * from ethereum.core.dim_dex_liquidity_pools where PLATFORM='sushiswap'),
sos as (SELECT ORIGIN_FROM_ADDRESS as LP1, sum(amount_USD) as ADD_USD,min(BLOCK_TIMESTAMP) as add_time
from ethereum.core.ez_token_transfers where TO_ADDRESS in (select POOL_ADDRESS from min)
and ORIGIN_TO_ADDRESS='0xd9e1ce17f2641f24ae83637ab66a2cca9c378b9f' and origin_function_signature in ('0xe8e33700', '0xded9382a', '0xf305d719')
and BLOCK_TIMESTAMP>=CURRENT_DATE-365 group by 1),
kka as (SELECT ORIGIN_FROM_ADDRESS as LP2, sum(amount_USD) as remove_usd,
max(BLOCK_TIMESTAMP) as remove_time from ethereum.core.ez_token_transfers
where ORIGIN_TO_ADDRESS='0xd9e1ce17f2641f24ae83637ab66a2cca9c378b9f' and origin_function_signature in ('0xbaa2abde', '0x2195995c', '0x02751cec')
and BLOCK_TIMESTAMP>=CURRENT_DATE-365 group by 1),
morad as (select sos.LP1 as LP,(remove_usd - ADD_USD) as USD_proit, datediff('day', add_time, remove_time) as days
from kka left outer join sos on kka.LP2=sos.LP1 where USD_proit is not nullhaving days>0)
select case when days<7 then '1. Under 1 week'
when days>=7 and days< 30 then '2. between 1 week to 1 month'
when days>=30 and days<90 then '3. between 1 to 3 month'
when days>=90 then '4. Over 3 month'
end as gp, sum(USD_proit) as profit from morad group by 1
Run a query to Download Data