General (unsampled) aggregates and sampled one in the same query

Question

I have a query with three aggregates

select max(price), avg(price), min(price)
from trades_2024
WHERE timestamp IN '2024-08';

This returns:

max      avg          min
======== ===========  ========
61615.43 31598.71891  58402.01

And another query, to get event count per second, and then select the maximum

select max(count_sec) from (
select count() as count_sec from  trades
     WHERE timestamp IN '2024-08'
    sample by 1s
);

This returns

max
====
1241

I would like to somehow combine both results in a single query. If I do a Union I get this in two rows, but I want a single row, as in:

max      avg          min       max_count
======== ===========  ========  =========
61615.43 31598.71891  58402.01  1241

Answer 1

A CROSS JOIN in this case can join every row from the first query (1 row) with every row from the second (1 row), so we get a single row with all the aggregates combined.

WITH
sampled as (
  select timestamp, count() as count_sec from  trades
     WHERE timestamp IN '2024-08'
    sample by 1s
    order by 2 desc
    limit -1
)
select max(price), avg(price), min(price), count_sec as max_count 
from trades_2024 cross join sampled
WHERE trades_2024.timestamp IN '2024-08';