Aggregation Functions

SELECT count() FROM trades;
-- Result: 1000000

SELECT symbol, count() 
FROM trades 
GROUP BY symbol;

SELECT count(price) FROM trades;
-- Counts only non-NULL prices

SELECT count_distinct(symbol) FROM trades;
-- Result: 50

SELECT sum(quantity) FROM trades;
-- Result: 5000000

SELECT 
    symbol,
    sum(quantity * price) as total_value
FROM trades
GROUP BY symbol;

SELECT nsum(price) FROM trades;
-- More accurate for large datasets with varying magnitudes

SELECT avg(price) FROM trades;
-- Result: 125.43

SELECT 
    date_trunc('hour', timestamp) as hour,
    avg(price) as avg_price
FROM trades
GROUP BY hour;

SELECT weighted_avg(price, quantity) FROM trades;
-- Average price weighted by quantity

SELECT min(price) FROM trades;
-- Result: 98.50

SELECT symbol, min(price), max(price)
FROM trades
GROUP BY symbol;

SELECT max(price) FROM trades;
-- Result: 152.75

SELECT stddev(price) FROM trades;
-- Result: 12.34

SELECT symbol, stddev(price) as volatility
FROM trades
GROUP BY symbol;

SELECT variance(price) FROM trades;

SELECT regr_slope(price, timestamp::LONG) FROM trades;
-- Price trend over time

SELECT string_agg(symbol, ', ') FROM trades;
-- Result: AAPL, GOOGL, MSFT, TSLA

SELECT 
    trader_id,
    string_agg(symbol, '|') as traded_symbols
FROM trades
GROUP BY trader_id;

SELECT string_distinct_agg(symbol, ', ') FROM trades;
-- Result: AAPL, GOOGL, MSFT (no duplicates)

SELECT mode(symbol) FROM trades;
-- Result: AAPL (most traded symbol)

SELECT 
    symbol,
    first(price) as opening_price
FROM trades
GROUP BY symbol;

SELECT 
    symbol,
    last(price) as closing_price
FROM trades
GROUP BY symbol;

SELECT ksum(price * quantity) FROM trades;
-- More accurate than sum() for floating-point

SELECT 
    symbol,
    count() as trade_count,
    sum(quantity) as total_qty,
    avg(price) as avg_price,
    min(price) as low,
    max(price) as high
FROM trades
WHERE timestamp > '2024-03-15T00:00:00Z'
GROUP BY symbol
ORDER BY total_qty DESC;

SELECT 
    timestamp,
    symbol,
    first(price) as open,
    max(price) as high,
    min(price) as low,
    last(price) as close,
    sum(quantity) as volume
FROM trades
SAMPLE BY 1h ALIGN TO CALENDAR;

SELECT 
    count() as total_trades,
    sum(quantity * price) as total_value,
    avg(price) as avg_price
FROM trades;