Candlestick

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