DiamondPools Overeiw copy
Updated 2023-06-04
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
›
⌄
-- forked from HBD / Pools Overeiw @ https://flipsidecrypto.xyz/HBD/q/2023-05-31-02-26-am-OWUAY4
with pairs as (select
EVENT_DATA:numPairs::number as pool_id,
EVENT_DATA:pairAddress as pairAddress,
SWAP_CONTRACT,
CASE
when substr(EVENT_DATA:token0Key,20,21) = 'FlowToken' then 'FLOW'
when substr(EVENT_DATA:token0Key,20,21) = 'FiatToken' then 'USDC'
when substr(EVENT_DATA:token0Key,20,21) = 'SloppyStakes' then 'Sloppy'
else substr(EVENT_DATA:token0Key,20,21)
end as token0,
CASE
when substr(EVENT_DATA:token0Key,20,21) = 'FlowToken' then 'FLOW'
when substr(EVENT_DATA:token0Key,20,21) = 'FiatToken' then 'USDC'
when substr(EVENT_DATA:token0Key,20,21) = 'SloppyStakes' then 'Sloppy'
else substr(EVENT_DATA:token0Key,20,21)
end as token1
from flow.core.fact_events a
join flow.core.dim_swap_pool_labels b on VAULT_ADDRESS = EVENT_DATA:pairAddress
where EVENT_CONTRACT = 'A.b063c16cac85dbd1.SwapFactory'
and EVENT_TYPE = 'PairCreated'
and TX_SUCCEEDED = 'true'
order by 1),
main as (
select
a.BLOCK_TIMESTAMP::date as date,
a.TX_ID,
pool_id,
SWAP_CONTRACT,
token0 || '/' || token1 as pair_name,
case
when EVENT_DATA:direction = '1' then token0
when EVENT_DATA:direction = '0' then token1
end as token_in,
Run a query to Download Data