sallarUser Behavior, amount of tokens that users are swapping from in USD
Updated 2022-06-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
›
⌄
with initial_data_one as
(
select
dcl.contract_name as contract_name,
count(distinct fs.tx_id) as number_of_swaps,
sum(fs.token_out_amount) as total_amount
from flow.core.fact_swaps fs
left join flow.core.dim_contract_labels dcl
on fs.token_out_contract = dcl.event_contract
group by dcl.contract_name
),
initial_data_two as
(
select
token,
avg(price_usd) as average_price
from flow.core.fact_prices
group by token
),
initial_data_three as
(
select
id1.*,
iff(REGEXP_REPLACE(id1.contract_name, '(Teleported)|(Token)','') in ('Tether','FUSD','Fiat', 'Sportium') , iff(REGEXP_REPLACE(id1.contract_name, '(Teleported)|(Token)','') = 'Sportium' ,0.33, 1), id2.average_price) as average_price
from initial_data_one id1
left join initial_data_two id2
on REGEXP_REPLACE(id1.contract_name, '(Teleported)|(Token)','') = REGEXP_REPLACE(id2.token, '(Teleported)|(Token)','')
)
select
*,
total_amount * average_price as total_amount_in_usd
from initial_data_three
Run a query to Download Data