CODEWITHSUNDEEP
databasetime-seriesquestdb
Javier Ramirez
Javier Ramirez

Reputation: 4032

Calculate cumulative Tick and Trin in QuestDB for market sentiment analysis

I am trying to calculate the tick and trin as seen on this python script by Databento, but using QuestDB's SQL.

The below sql works fine (create table SQL and demo rows below), but gives me only the total Tick and Trin. I would like instead to have the cumulative values at every row in the day.

WITH tick_vol AS (
    SELECT 
        SUM(CASE WHEN side = 'sell' THEN 1 END)  as downtick,
        SUM(CASE WHEN side = 'buy' THEN 1 END)  as uptick,
        SUM(CASE WHEN side = 'sell' THEN amount END)  as downvol,
        SUM(CASE WHEN side = 'buy' THEN amount END)  as upvol
    FROM trades
    where timestamp in '2023-12-01'
)
SELECT 
    *, 
    uptick::double / downtick as tick, 
    (uptick::double / downtick) / (upvol::double / downvol) as trin 
FROM tick_vol;

query results: downtick 2, uptick 3, downvol 250, upvol 350, tick 1.5, trin 1.071428571428

CREATE TABLE 'trades' (
  symbol SYMBOL capacity 256 CACHE,
  side SYMBOL capacity 256 CACHE,
  price DOUBLE,
  amount DOUBLE,
  timestamp TIMESTAMP
) timestamp (timestamp) PARTITION BY DAY WAL;

INSERT INTO trades (timestamp, side, amount)
VALUES 
('2023-12-01T10:00:00.000Z', 'sell', 100),
('2023-12-01T10:01:00.000Z', 'buy', 50),
('2023-12-01T10:02:00.000Z', 'sell', 150),
('2023-12-01T10:03:00.000Z', 'buy', 100),
('2023-12-01T10:04:00.000Z', 'buy', 200);

Upvotes: 0

Views: 38

Answers (1)

Javier Ramirez
Javier Ramirez

Reputation: 4032

We can repurpose the SUM we already have as a window function, and it works. Notice I am already setting the downtick/uptick as 1.0, so it will be interpreted as double and we don't need to do any explicit casting anymore

WITH tick_vol AS (
    SELECT 
        timestamp, 
        side, 
        amount,
        SUM(CASE WHEN side = 'sell' THEN 1.0 END) OVER (ORDER BY timestamp) as downtick,
        SUM(CASE WHEN side = 'buy' THEN 1.0 END) OVER (ORDER BY timestamp) as uptick,
        SUM(CASE WHEN side = 'sell' THEN amount END) OVER (ORDER BY timestamp) as downvol,
        SUM(CASE WHEN side = 'buy' THEN amount END) OVER (ORDER BY timestamp) as upvol
    FROM trades
    where timestamp in '2023-12-01'
)
SELECT 
    *, 
    uptick / downtick as tick, 
    (uptick / downtick) / (upvol / downvol) as trin 
FROM tick_vol;

The query returns the rolling tick and trin and, as expected, the last row has identical results to the initial query

"timestamp","side","amount","downtick","uptick","downvol","upvol","tick","trin"
"2023-12-01T10:00:00.000000Z","sell",100.0,1.0,,100.0,,,
"2023-12-01T10:01:00.000000Z","buy",50.0,1.0,1.0,100.0,50.0,1.0,2.0
"2023-12-01T10:02:00.000000Z","sell",150.0,2.0,1.0,250.0,50.0,0.5,2.5
"2023-12-01T10:03:00.000000Z","buy",100.0,2.0,2.0,250.0,150.0,1.0,1.666666666666
"2023-12-01T10:04:00.000000Z","buy",200.0,2.0,3.0,250.0,350.0,1.5,1.071428571428

Upvotes: 0

Related Questions