adambalaUntitled Query
Updated 2022-09-16
999
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 bridge_erc20_to_near as (
select el.block_timestamp::date as date,
tx_hash,
event_inputs:accountId as receiver,
event_inputs:sender as sender,
event_inputs:token as token_address,
symbol,
(event_inputs:amount)/pow(10, decimals) as amount
from ethereum.core.fact_event_logs el
left join ethereum.core.dim_contracts_extended ec on el.event_inputs:token=ec.contract_address
where ORIGIN_TO_ADDRESS = '0x23ddd3e3692d1861ed57ede224608875809e127f' -- Near: Rainbow bridge
and CONTRACT_NAME = 'ERC20Locker'
and EVENT_NAME = 'Locked'
and ORIGIN_FUNCTION_SIGNATURE = '0x0889bfe7'
and symbol is not null
and receiver like '%.near'
), erc20_prices as (
select hour::date as date, symbol, token_address, avg(price) as price
from ethereum.core.fact_hourly_token_prices
where token_address in (select distinct token_address from bridge_erc20_to_near)
and date >= '2021-03-15'
group by 1,2,3
), near_prices as (
with swaps as (
select
block_timestamp::date as date,
logs[0] as log,
substring(log, 1, CHARINDEX(' wrap.near for', log)) as first_part,
regexp_replace(first_part, '[^0-9]', '')/pow(10, 24) as near_amount,
substring(log, CHARINDEX('for', log), 100) as second_part,
substring(second_part, 1, CHARINDEX('dac', second_part)-2) as second_part_amount,
regexp_replace(second_part_amount, '[^0-9]', '')/pow(10,6) as usdt_amount
from flipside_prod_db.mdao_near.receipts
where logs[0] like 'Swapped % wrap.near for % dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near'
and date >= '2021-03-15'
Run a query to Download Data