KeyrockAptos - Liquidswap - Pool TVL APT/USDC
Updated 2024-10-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
›
⌄
-- forked from Aptos - Pool TVL APT/USDC + Swaps @ https://flipsidecrypto.xyz/edit/queries/cd6676cc-a52e-4ef1-a607-d063df864ceb
WITH
apt_CTE AS (
WITH raw_data AS (
SELECT livequery.live.udf_api('https://api.coingecko.com/api/v3/coins/aptos/market_chart?vs_currency=usd&days=max') AS resp
)
SELECT
'prices' AS key,
TO_TIMESTAMP(value[0]::string) AS day_price,
value[1] AS apt_price
FROM
raw_data,
LATERAL FLATTEN (input => resp:data:prices)
),
USDC_CTE AS (
WITH raw_data AS (
SELECT livequery.live.udf_api('https://api.coingecko.com/api/v3/coins/usd-coin/market_chart?vs_currency=usd&days=max') AS resp
)
SELECT
'prices' AS key,
TO_TIMESTAMP(value[0]::string) AS day_price,
value[1] AS usdc_price
FROM
raw_data,
LATERAL FLATTEN (input => resp:data:prices)
),
event_summary AS (
SELECT
date_trunc('day', BLOCK_TIMESTAMP) AS day,
-- Added
SUM(CASE WHEN EVENT_TYPE = '0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::liquidity_pool::LiquidityAddedEvent<0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC, 0x1::aptos_coin::AptosCoin, 0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::curves::Uncorrelated>' THEN EVENT_DATA['added_x_val'] / 1000000 ELSE 0 END) AS total_added_USDC,
SUM(CASE WHEN EVENT_TYPE = '0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::liquidity_pool::LiquidityAddedEvent<0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC, 0x1::aptos_coin::AptosCoin, 0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::curves::Uncorrelated>' THEN EVENT_DATA['added_y_val'] / 100000000 ELSE 0 END) AS total_added_APT,
QueryRunArchived: QueryRun has been archived