Skip to content

Instantly share code, notes, and snippets.

@LFSCamargo

LFSCamargo/Candlestick.sql

Created August 11, 2023 21:44
Show Gist options
  • Save LFSCamargo/2d86ab6b8b346a5e2d2042a1059f2aea to your computer and use it in GitHub Desktop.
Save LFSCamargo/2d86ab6b8b346a5e2d2042a1059f2aea to your computer and use it in GitHub Desktop.
Download ZIP
Candlestick
Raw
Candlestick.sql
WITH initial_price as (
SELECT transaction_hash,
index,
blocks.number as block_number,
blocks.timestamp as block_timestamp,
sqrt_ratio as sqrt_ratio_after
FROM initializations
JOIN blocks ON initializations.block_number = blocks.number
WHERE pool_key_hash = 3444297103257838275074422089987936420839083129914207634282919550416207684544
),
last_period_price as (
SELECT transaction_hash,
index,
blocks.number as block_number,
blocks.timestamp as block_timestamp,
sqrt_ratio_after
FROM swaps
JOIN blocks ON swaps.block_number = blocks.number
WHERE pool_key_hash = 3444297103257838275074422089987936420839083129914207634282919550416207684544
),
all_price_changes as (
SELECT *
from initial_price
union all
SELECT *
from last_period_price
),
windowed_price_changes AS (
SELECT block_timestamp,
sqrt_ratio_after,
LAST_VALUE(sqrt_ratio_after) OVER (
ORDER BY block_timestamp ASC
) AS last_sqrt_ratio
FROM all_price_changes
)
SELECT block_timestamp,
MAX(last_sqrt_ratio) AS high_price,
MIN(last_sqrt_ratio) AS low_price,
FIRST_VALUE(last_sqrt_ratio) OVER (
ORDER BY block_timestamp ASC
) AS open_price,
LAST_VALUE(last_sqrt_ratio) OVER (
ORDER BY block_timestamp ASC
) AS close_price
FROM windowed_price_changes
WHERE block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
GROUP BY block_timestamp,
last_sqrt_ratio
ORDER BY block_timestamp ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment