jackguyAREO 1 copy
Updated 2023-09-03
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
›
⌄
-- forked from AREO 1 @ https://flipsidecrypto.xyz/edit/queries/bbbda025-5d87-4d42-ba0b-81e9011ca5ba
SELECT
date_trunc('hour', block_timestamp) as hour,
median(AMOUNT_USDBC / AMOUNT_AERO) as areo_price,
count(*) as swaps
FROM (
SELECT
block_timestamp,
tx_hash,
sum(CASE when contract_address LIKE lower('0xd9aAEc86B65D86f6A7B5B1b0c42FFA531710b6CA') then raw_amount / power(10, 6) else 0 end) as amount_usdbc,
sum(CASE when contract_address LIKE lower('0x940181a94A35A4569E4529A3CDfB74e38FD98631') then raw_amount / power(10, 18) else 0 end) as amount_aero
FROM base.core.fact_token_transfers
WHERE tx_hash in (
SELECT
tx_hash
FROM base.core.fact_transactions
where to_address LIKE lower('0xcF77a3Ba9A5CA399B7c97c74d54e5b1Beb874E43')
)
AND (
to_address LIKE '0x2223f9fe624f69da4d8256a7bcc9104fba7f8f75'
or from_address LIKE '0x2223f9fe624f69da4d8256a7bcc9104fba7f8f75'
)
GROUP BY 1,2
)
WHERE AMOUNT_USDBC > 0
AND AMOUNT_AERO > 0
GROUP BY 1
Run a query to Download Data