Add documentation for "Aggregate function cannot be passed as an argument" limitation

[jerrinot]

The current QuestDB documentation for aggregate functions doesn't mention an important limitation: aggregate functions cannot be used as arguments to other functions. When testing MCP, Claude tended to use this and constantly tripped over it. Having this explicitly in the documentation should prevent it.

I'm not sure whether is the best place to document this, so I am just opening this issue instead of a PR:

Aggregate Function Limitations

Aggregate Functions as Arguments

QuestDB does not support using an aggregate function as an argument to other functions.
For example, the following query would fail:

-- Aggregate function as argument to another function
SELECT 
  MIN(timestamp) AS earliest_date,
  MAX(timestamp) AS latest_date,
  DATEDIFF('d', MIN(timestamp), MAX(timestamp)) AS days_of_data
FROM trades

Workarounds

There are two common ways to work around this limitation:

Using Common Table Expressions (CTEs)

You can use a CTE to perform aggregations in stages:

WITH date_ranges AS (
    SELECT 
        MIN(timestamp) AS earliest_date,
        MAX(timestamp) AS latest_date
    FROM trades
)
SELECT 
    earliest_date,
    latest_date,
    DATEDIFF('d', earliest_date, latest_date) AS days_of_data
FROM date_ranges

Using Subqueries

Alternatively, you can use subqueries to achieve the same result:

SELECT 
    earliest_date,
    latest_date,
    DATEDIFF('d', earliest_date, latest_date) AS days_of_data
FROM (
    SELECT 
        MIN(timestamp) AS earliest_date,
        MAX(timestamp) AS latest_date
    FROM trades
)