permarymodern-emerald
Updated 2024-11-25
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
prices as (
SELECT
HOUR::date as day,
symbol,
avg(PRICE) as price
FROM aptos.price.ez_prices_hourly
WHERE SYMBOL IN ('APT', 'WETH', 'USDC')
AND day >= '2023-11-01'
GROUP by 1,2
),
aries_pontem_deposits_kanalabs_swapgpt as (
SELECT
distinct x.tx_hash,
x.block_timestamp,
case when y.payload:type_arguments[1] ilike '%USDC%' then 'USDC'
when y.payload:type_arguments[1] ilike '%ETH%' then 'WETH'
else 'APT' end as symbol,
x.event_data:amount/pow(10,8) as volume_in,
x.account_address as user
from aptos.core.fact_events x
join aptos.core.fact_transactions y on x.tx_hash=y.tx_hash
where x.payload_function='0xc0deb00c405f84c85dc13442e305df75d1288100cdd82675695f6148c7ece51c::user::deposit_from_coinstore'
and x.event_type='0x1::coin::WithdrawEvent'
union
SELECT
distinct x.tx_hash,
x.block_timestamp,
case when y.payload:type_arguments[1] ilike '%USDC%' then 'USDC'
when y.payload:type_arguments[1] ilike '%ETH%' then 'WETH'
else 'APT' end as symbol,
x.event_data:amount/pow(10,8) as volume_in,
x.account_address as user
from aptos.core.fact_events x
QueryRunArchived: QueryRun has been archived