Advanced SQL & TimescaleDB Analytics

Advanced query patterns for time-series financial data. CTEs, window functions, continuous aggregates, and performance optimization.

When to Apply

• Writing analytical queries for market data
• Building time-series aggregations
• Optimizing dashboard queries
• Creating continuous aggregates for real-time stats
• Writing complex JOINs across market_data, economic_data, technical_analysis

1. Time-Bucket Aggregations (CRITICAL)

OHLCV candlestick aggregation

SELECT
  time_bucket('1 hour', time) AS bucket,
  symbol,
  first(price, time) AS open,
  max(price) AS high,
  min(price) AS low,
  last(price, time) AS close,
  sum(volume_24h) AS volume,
  count(*) AS samples
FROM market_data
WHERE symbol = 'BTC'
  AND time > now() - interval '7 days'
GROUP BY bucket, symbol
ORDER BY bucket DESC;

Multi-resolution time buckets

-- Adaptive resolution: 5min for recent, 1h for older
(
  SELECT time_bucket('5 minutes', time) AS bucket, symbol,
         avg(price) AS price, avg(volume_24h) AS volume
  FROM market_data
  WHERE time > now() - interval '24 hours'
    AND symbol = 'BTC'
  GROUP BY bucket, symbol
)
UNION ALL
(
  SELECT time_bucket('1 hour', time) AS bucket, symbol,
         avg(price) AS price, avg(volume_24h) AS volume
  FROM market_data
  WHERE time BETWEEN now() - interval '30 days' AND now() - interval '24 hours'
    AND symbol = 'BTC'
  GROUP BY bucket, symbol
)
ORDER BY bucket DESC;

2. Window Functions (CRITICAL)

Price change percentage

SELECT
  time,
  symbol,
  price,
  LAG(price) OVER w AS prev_price,
  ROUND(
    ((price - LAG(price) OVER w) / NULLIF(LAG(price) OVER w, 0) * 100)::numeric, 2
  ) AS change_pct
FROM market_data
WHERE symbol = 'BTC'
  AND time > now() - interval '24 hours'
WINDOW w AS (PARTITION BY symbol ORDER BY time)
ORDER BY time DESC;

Moving averages

SELECT
  time,
  symbol,
  price,
  AVG(price) OVER (
    PARTITION BY symbol ORDER BY time
    ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
  ) AS sma_20,
  AVG(price) OVER (
    PARTITION BY symbol ORDER BY time
    ROWS BETWEEN 49 PRECEDING AND CURRENT ROW
  ) AS sma_50,
  AVG(price) OVER (
    PARTITION BY symbol ORDER BY time
    ROWS BETWEEN 199 PRECEDING AND CURRENT ROW
  ) AS sma_200
FROM market_data
WHERE symbol = 'BTC'
  AND time > now() - interval '30 days'
ORDER BY time DESC;

Ranking and percentiles

SELECT
  symbol,
  price,
  change_24h,
  RANK() OVER (ORDER BY change_24h DESC) AS rank_by_change,
  NTILE(4) OVER (ORDER BY volume_24h DESC) AS volume_quartile,
  PERCENT_RANK() OVER (ORDER BY change_24h) AS percentile
FROM (
  SELECT DISTINCT ON (symbol)
    symbol, price, change_24h, volume_24h
  FROM market_data
  WHERE time > now() - interval '1 hour'
  ORDER BY symbol, time DESC
) latest_prices
ORDER BY change_24h DESC;

Cumulative volume

SELECT
  time_bucket('1 hour', time) AS bucket,
  symbol,
  sum(volume_24h) AS hourly_volume,
  sum(sum(volume_24h)) OVER (
    PARTITION BY symbol ORDER BY time_bucket('1 hour', time)
  ) AS cumulative_volume
FROM market_data
WHERE symbol = 'BTC'
  AND time > now() - interval '7 days'
GROUP BY bucket, symbol
ORDER BY bucket;

3. CTEs (Common Table Expressions) (HIGH)

Multi-step analytics

WITH latest_prices AS (
  SELECT DISTINCT ON (symbol)
    symbol, price, change_24h, volume_24h, time
  FROM market_data
  WHERE time > now() - interval '1 hour'
  ORDER BY symbol, time DESC
),
daily_stats AS (
  SELECT
    symbol,
    avg(price) AS avg_price_24h,
    max(price) AS high_24h,
    min(price) AS low_24h,
    avg(volume_24h) AS avg_volume
  FROM market_data
  WHERE time > now() - interval '24 hours'
  GROUP BY symbol
),
whale_summary AS (
  SELECT
    period,
    total_transactions,
    total_volume_usd,
    unusual_activity
  FROM whale_activity_analysis
  WHERE analysis_timestamp > now() - interval '24 hours'
    AND period = 'daily'
  ORDER BY analysis_timestamp DESC
  LIMIT 1
)
SELECT
  lp.symbol,
  lp.price AS current_price,
  lp.change_24h,
  ds.high_24h,
  ds.low_24h,
  ds.avg_volume,
  ws.total_volume_usd AS whale_volume,
  ws.unusual_activity AS whale_alert
FROM latest_prices lp
JOIN daily_stats ds ON lp.symbol = ds.symbol
CROSS JOIN whale_summary ws
ORDER BY lp.volume_24h DESC;

Recursive CTE for support/resistance levels

WITH RECURSIVE price_levels AS (
  -- Base: find local extremes
  SELECT
    time, price,
    CASE
      WHEN price > LAG(price) OVER w AND price > LEAD(price) OVER w THEN 'resistance'
      WHEN price < LAG(price) OVER w AND price < LEAD(price) OVER w THEN 'support'
    END AS level_type
  FROM market_data
  WHERE symbol = 'BTC'
    AND time > now() - interval '30 days'
  WINDOW w AS (ORDER BY time)
)
SELECT
  level_type,
  ROUND(AVG(price)::numeric, 2) AS level_price,
  COUNT(*) AS touches
FROM price_levels
WHERE level_type IS NOT NULL
GROUP BY level_type, ROUND(price / 500) * 500  -- Cluster nearby levels
HAVING COUNT(*) >= 2
ORDER BY level_price DESC;

4. Continuous Aggregates (HIGH)

Create for dashboard performance

-- Hourly market summary (auto-updates)
CREATE MATERIALIZED VIEW market_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  symbol,
  first(price, time) AS open,
  max(price) AS high,
  min(price) AS low,
  last(price, time) AS close,
  avg(price) AS avg_price,
  count(*) AS data_points
FROM market_data
GROUP BY bucket, symbol
WITH NO DATA;

-- Refresh policy: update every 30 min, keep 2h behind real-time
SELECT add_continuous_aggregate_policy('market_hourly',
  start_offset => INTERVAL '2 days',
  end_offset => INTERVAL '2 hours',
  schedule_interval => INTERVAL '30 minutes'
);

-- Force initial refresh
CALL refresh_continuous_aggregate('market_hourly', NULL, NULL);

Daily analytics aggregate

CREATE MATERIALIZED VIEW market_daily
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', time) AS bucket,
  symbol,
  first(price, time) AS open,
  max(price) AS high,
  min(price) AS low,
  last(price, time) AS close,
  avg(volume_24h) AS avg_volume,
  max(price) - min(price) AS range,
  stddev(price) AS volatility
FROM market_data
GROUP BY bucket, symbol
WITH NO DATA;

5. Compression (HIGH)

-- Enable compression on old data
ALTER TABLE market_data SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'symbol',
  timescaledb.compress_orderby = 'time DESC'
);

-- Auto-compress data older than 7 days
SELECT add_compression_policy('market_data', INTERVAL '7 days');

-- Check compression stats
SELECT
  hypertable_name,
  before_compression_total_bytes,
  after_compression_total_bytes,
  ROUND((1 - after_compression_total_bytes::numeric / before_compression_total_bytes) * 100, 1) AS compression_ratio
FROM hypertable_compression_stats('market_data');

6. Query Optimization (HIGH)

Use EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM market_data
WHERE symbol = 'BTC' AND time > now() - interval '1 hour';

Index strategies

-- Composite index for common query pattern
CREATE INDEX idx_market_symbol_time ON market_data (symbol, time DESC);

-- BRIN index for time-series (low overhead)
CREATE INDEX idx_market_time_brin ON market_data USING BRIN (time);

-- Partial index for active queries
CREATE INDEX idx_market_recent ON market_data (symbol, time DESC)
WHERE time > now() - interval '7 days';

Performance rules

• Always filter by time FIRST in WHERE clause
• Use time_bucket() instead of date_trunc() for TimescaleDB
• Prefer DISTINCT ON over subquery for latest values
• Avoid SELECT * - specify needed columns
• Use LIMIT early when possible
• Cache frequently-used aggregations in continuous aggregates

7. Useful Patterns

Gap filling (missing data points)

SELECT
  time_bucket_gapfill('1 hour', time) AS bucket,
  symbol,
  locf(avg(price)) AS price  -- Last observation carried forward
FROM market_data
WHERE symbol = 'BTC'
  AND time > now() - interval '24 hours'
GROUP BY bucket, symbol
ORDER BY bucket;

Correlation between assets

WITH btc AS (
  SELECT time_bucket('1 hour', time) AS bucket, avg(price) AS price
  FROM market_data WHERE symbol = 'BTC' AND time > now() - interval '30 days'
  GROUP BY bucket
),
eth AS (
  SELECT time_bucket('1 hour', time) AS bucket, avg(price) AS price
  FROM market_data WHERE symbol = 'ETH' AND time > now() - interval '30 days'
  GROUP BY bucket
)
SELECT
  corr(btc.price, eth.price) AS correlation,
  count(*) AS data_points
FROM btc
JOIN eth ON btc.bucket = eth.bucket;