QuestDB SQL Window Functions Example

00-QuestDB SQL Window Functions Example - Intro.md

These queries were used at the video tutorial at https://www.youtube.com/watch?v=bYlcnkgH0zA&ab_channel=QuestDB.

All the queries can be ran at https://demo.questdb.io

01-all-trades.sql

SELECT * FROM trades;

02-trades-and-volume-still-no-window-functions.sql

SELECT timestamp, symbol, price * amount AS volume,
FROM trades;

03-global-window-function.sql

SELECT timestamp, symbol, price * amount AS volume,
sum(price * amount) OVER (ORDER BY timestamp) AS total_volume
 FROM trades;

04-window-function-per-symbol-since-beginning.sql

SELECT timestamp, symbol, side, price * amount AS volume,
SUM(price * amount) OVER (PARTITION BY symbol ORDER BY timestamp) AS total_volume
 FROM trades;

05-window-function-per-symbol-since-beginning-side-not-partitioned.sql

SELECT timestamp, symbol, side, price * amount AS volume,
SUM(price * amount) OVER (
   PARTITION BY symbol, side 
   ORDER BY timestamp
   ) AS total_volume
 FROM trades;

06-window-function-per-symbol-and-side-since-beginning.sql

SELECT timestamp, symbol, side, price * amount AS volume,
SUM(price * amount) OVER (
   PARTITION BY symbol, side 
   ORDER BY timestamp
   ) AS total_volume
 FROM trades
 WHERE symbol = 'ETH-USD' AND side='buy';

07-window-function-per-symbol-and-side-within-one-second.sql

SELECT timestamp, symbol, side, price * amount AS volume,
SUM(price * amount) OVER (
   PARTITION BY symbol, side 
   ORDER BY timestamp
   RANGE 1 second PRECEDING
   ) AS total_volume
 FROM trades
 WHERE symbol = 'ETH-USD' AND side='buy';

08-count-elements-in-window-per-symbol-and-side-within-one-second.sql

SELECT timestamp, symbol, side, price * amount AS volume,
SUM(1) OVER (
   PARTITION BY symbol, side 
   ORDER BY timestamp
   RANGE 1 second PRECEDING
   ) AS total_volume
 FROM trades
 WHERE symbol = 'ETH-USD' AND side='buy';

09-count-elements-in-window-per-symbol-and-side-within-10-rows.sql

SELECT timestamp, symbol, side, price * amount AS volume,
SUM(1) OVER (
   PARTITION BY symbol, side 
   ORDER BY timestamp
   ROWS 10 PRECEDING
   ) AS total_volume
 FROM trades
 WHERE symbol = 'ETH-USD' AND side='buy';

10-moving-average-elements-in-window-per-symbol-and-side-within-10-rows.sql

SELECT timestamp, symbol, side, price * amount AS volume,
AVG(price) OVER (
   PARTITION BY symbol, side 
   ORDER BY timestamp
   ROWS 10 PRECEDING
   ) AS total_volume
 FROM trades
 WHERE symbol = 'ETH-USD' AND side='buy';

11-multiple-moving-averages-in-single-query.sql

SELECT timestamp time, symbol, price as priceBtc
, avg(price) over (PARTITION BY symbol ORDER BY timestamp RANGE between 10 seconds PRECEDING AND CURRENT ROW) movingAvg10Sec
, avg(price) over (PARTITION BY symbol ORDER BY timestamp RANGE between 30 seconds PRECEDING AND CURRENT ROW) movingAvg30Sec
, avg(price) over (PARTITION BY symbol ORDER BY timestamp RANGE between 45 seconds PRECEDING AND CURRENT ROW) movingAvg45Sec
FROM trades WHERE dateadd('m', -5, now()) < timestamp AND symbol = 'BTC-USD'