QuestDB 9.3: Window joins, views, PIVOT, and AI in the console

Happy new year! Time to update those WHERE timestamp IN '2025' filters.

We are kicking off the year with QuestDB 9.3, which packs four major additions: window joins for correlating rows with aggregates from a surrounding time range, views for reusable query logic with overridable parameters, PIVOT to reshape rows into columns, and AI assistance built into the Web Console using your own API keys.

Let us walk through each one.

Window joins: range-based time alignment

Time-series analytics often require correlating events with data from a surrounding time window, for example looking at market prices around each trade, or sensor readings before and after an anomaly.

Previously, you would need complex subqueries or correlated joins to answer questions like "what was the average price in the 10 seconds after each trade?"

QuestDB 9.3 introduces window joins, a specialized join syntax for exactly this pattern. Each row from a primary table joins with a time window of rows from another table, and aggregations are computed over the matching rows.

Note this is an initial release of Window Joins, which will be improved at the upcoming 9.3.1 release.

Example: trades and surrounding prices

Consider an FX trading scenario where you want to correlate each trade with the average bid and ask prices from the 10 seconds following the trade:

SELECT
  t.*,
  avg(p.bid_price) AS avg_bid,
  avg(p.ask_price) AS avg_ask,
  min(p.timestamp) AS from_ts,
  max(p.timestamp) AS to_ts
FROM fx_trades t
WINDOW JOIN core_price p ON symbol
  RANGE BETWEEN 0 seconds FOLLOWING AND 10 seconds FOLLOWING
  INCLUDE PREVAILING
WHERE t.timestamp IN today();

The RANGE BETWEEN ... FOLLOWING clause defines the time window relative to each trade. INCLUDE PREVAILING ensures you get the most recent price even if there is no exact match at the window boundary.

This eliminates pages of subqueries for short-horizon analytics. Whether you are correlating trades with market data, IoT events with sensor baselines, or any other time-aligned pattern, window joins make the query direct and readable.

You can try this query yourself on our public demo.

Views: reusable query logic

QuestDB 9.3 introduces views, virtual tables defined by a SELECT statement. Unlike materialized views, they do not persist data. The underlying query executes as a subquery each time the view is referenced.

What makes QuestDB views distinctive is overridable parameters. You can define default values inside the view definition and override them at query time, turning views into flexible, reusable query templates.

Example: parameterized OHLC candles

Here is a view that generates minute candles with configurable time range and symbol:

CREATE OR REPLACE VIEW minute_candles AS (
  DECLARE
    OVERRIDABLE @ago_units := 'h',
    OVERRIDABLE @ago_amount := 1,
    OVERRIDABLE @symbol := 'EURUSD'
  SELECT timestamp, symbol,
    first(price) AS open,
    max(price) AS high,
    min(price) AS low,
    last(price) AS close
  FROM fx_trades
  WHERE
    timestamp >= dateadd(@ago_units, -1 * @ago_amount, now())
    AND symbol = @symbol
  SAMPLE BY 1m
);

Once created, you can query it with defaults or override specific parameters:

-- Use default values (last hour, EURUSD)
SELECT * FROM minute_candles;

-- Override time interval (last 30 minutes)
DECLARE @ago_units := 'm', @ago_amount := 30
SELECT * FROM minute_candles;

-- Override time interval and symbol
DECLARE @ago_units := 'h', @ago_amount := 3, @symbol := 'GBPUSD'
SELECT * FROM minute_candles;

This pattern is powerful for:

• Standardized reporting: Define your OHLC, VWAP, or aggregation logic once and reuse it across dashboards and applications.
• Self-service analytics: Give users a clean interface without exposing complex underlying queries.
• Query encapsulation: Keep complex business logic in one place instead of copy-pasting across multiple queries.

PIVOT: from rows to columns

The PIVOT keyword transforms rows into columns, moving from narrow to wide schemas. This is useful for reporting, dashboards, and systems that prefer column-oriented outputs.

Example: multi-dimensional aggregation

Suppose you want to analyze FX prices by symbol and ECN, with each combination becoming its own column:

(
  SELECT timestamp, symbol, ecn, SUM(bid_price) AS total_price,
    COUNT(*) AS entries
  FROM core_price
  WHERE timestamp IN today()
    AND symbol IN ('EURUSD', 'GBPUSD')
    AND ecn IN ('Hotspot', 'LMAX', 'EBS')
  SAMPLE BY 30m
)
PIVOT (
  SUM(total_price) AS total_price,
  SUM(total_price) / SUM(entries) AS avg_price
  FOR
    symbol IN ('EURUSD', 'GBPUSD')
    ecn IN ('Hotspot', 'LMAX', 'EBS')
  GROUP BY timestamp
);

The result is one row per 30-minute interval, with columns for each symbol-ECN combination. This flattens what would otherwise require multiple queries or post-processing into a single, efficient operation.

You can try this query yourself on our public demo.

Common use cases include:

• Cross-sectional reports: Compare metrics across dimensions in a single wide table.
• Dashboard exports: Feed BI tools that expect column-per-metric layouts.
• Time-series pivots: Turn long-format sensor or market data into wide format for specific analysis.

AI-assisted workflows in the Web Console

With QuestDB 9.3, LLMs now live directly inside the QuestDB Web Console.

You can bring your own keys for OpenAI, or Anthropic (Google Gemini is coming soon) and use AI assistance exactly where you are already working, while exploring data and writing queries.

This means:

• Asking questions about table schemas: "What columns does this table have and what do they represent?"
• Writing SQL, even if you don't remember the exact syntax: "Add linear interpolation to my SAMPLE BY"
• Debugging queries: "Why is this query returning NULL values?"
• Iterating on complex logic: "Help me optimize this join" or "Add a moving average calculation."

How it works

1. Open the Web Console and navigate to Settings.
2. Add your API key for OpenAI or Anthropic, and choose from the available models and permissions.
3. Use the AI assistant panel to start a conversation, or just click on the left of the query for a EXPLAIN on steroids.

Your keys stay on your machine and are sent directly to the provider. QuestDB does not proxy or store your API credentials.

Under the hood improvements

Beyond the headline features, QuestDB 9.3 includes a series of performance and robustness improvements:

• Column projection pushdown for Parquet: When reading Parquet files, QuestDB now pushes column projections down to the reader, reducing I/O and memory usage for queries that only need a subset of columns, speeding up execution times up to 100x.

• Parallel ORDER BY optimization: Large result sets with ORDER BY clauses now benefit from parallel sorting, improving response times for queries that return many rows up to 10x faster.

• Faster min/max on designated timestamps: Queries that compute min or max on the designated timestamp column are now optimized to use metadata when possible, avoiding full scans, and executing in O(1) time (instantly).

• JIT-compiled filters have undergone an optimisation pass, and will now execute up to 2x faster.

That is QuestDB 9.3. Window joins, views, PIVOT, and AI assistance expand what you can express and how you interact with the database, while the engine keeps getting faster underneath.

Read the full 9.3.0 release notes

Ready to try it?

Download QuestDB 9.3

Join our Slack or Discourse communities to share feedback and results. We are excited to see what you build with window joins, views, and AI-assisted workflows.