Afonso_Diaz2023-04-11 07:22 PM
Updated 2023-05-05
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 t1 as (
select
timestamp::date as date,
token_contract,
symbol,
avg(price_usd) as price_usd
from near.core.fact_prices
where symbol not in ('WOO', 'WBTC')
group by 1, 2, 3
union all
select
hour::date as date,
'2260fac5e5542a773aa44fbcfedf7c193bc2c599.factory.bridge.near' as token_contract,
symbol,
avg(price) as price_usd
from ethereum.core.fact_hourly_token_prices
where symbol = 'WBTC'
group by 1, 2, 3
),
repay as (
select
block_timestamp,
tx_hash,
tx_signer as user,
case
when symbol in ('USDT.e', 'USDC.e') then try_parse_json(b.args):amount / 1e6
when symbol in ('WETH', 'USN', 'AURORA', 'DAI') then try_parse_json(b.args):amount / 1e18
when symbol in ('NEAR', 'wNEAR', 'STNEAR', 'LINEAR', 'NearX') then try_parse_json(b.args):amount / 1e24
when symbol = 'WBTC' then try_parse_json(b.args):amount / 1e8
end as amount,
amount * t1.price_usd as amount_usd,
symbol
from near.core.fact_transactions a join near.core.fact_actions_events_function_call b
Run a query to Download Data