How to query for aggregated streaks?

Question

My goal is to detect price trends for cryptocurrencies. For that I'm getting OHLC pricing data for multiple currencies vs. multiple quote symbols (e.g. candles for Bitcoin/USD, Bitcoin/EUR, Litecoin/USD etc.). To accomplish calculating trends I use the Supertrend indicator. I have the following 2 tables:

-- This is where price candles AKA OHLC data gets stored. Interval can be '1h', '4h', '1d', etc.
CREATE TABLE ohlc (
    id integer DEFAULT nextval('ohlc_id_seq'::regclass) PRIMARY KEY,
    open numeric(65,30) NOT NULL,
    high numeric(65,30) NOT NULL,
    low numeric(65,30) NOT NULL,
    close numeric(65,30) NOT NULL,
    coinid character varying(255) NOT NULL REFERENCES coin(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    closetime timestamp(3) without time zone NOT NULL,
    quotesymbol text NOT NULL,
    interval text NOT NULL
);

-- This is a caching table that gets automatically populated by a stored procedure that runs AFTER every ohlc insert. "trend" can be 'UP' or 'DOWN'.
CREATE TABLE supertrend (
    id integer DEFAULT nextval('supertrend_id_seq'::regclass) PRIMARY KEY,
    coinid character varying(255) NOT NULL REFERENCES coin(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    quotesymbol text NOT NULL,
    date timestamp(3) without time zone NOT NULL,
    trend text NOT NULL,
    interval text NOT NULL
);

Sample data for the supertrend table:

coinid	quotesymbol	date	trend	interval
'bitcoin'	'usd'	'2024-04-18 00:00:00'	'UP'	'1d'
'bitcoin'	'usd'	'2024-04-17 00:00:00'	'UP'	'1d'
'bitcoin'	'usd'	'2024-04-16 00:00:00'	'UP'	'1d'
'bitcoin'	'usd'	'2024-04-15 00:00:00'	'DOWN'	'1d'
'bitcoin'	'eur'	'2024-04-18 00:00:00'	'UP'	'1d'
'bitcoin'	'eur'	'2024-04-17 00:00:00'	'UP'	'1d'
'bitcoin'	'eur'	'2024-04-16 00:00:00'	'DOWN'	'1d'
'bitcoin'	'eur'	'2024-04-15 00:00:00'	'DOWN'	'1d'
'bitcoin'	'cny'	'2024-04-18 00:00:00'	'DOWN'	'1d'
'bitcoin'	'cny'	'2024-04-17 00:00:00'	'UP'	'1d'
'bitcoin'	'cny'	'2024-04-16 00:00:00'	'UP'	'1d'
'bitcoin'	'cny'	'2024-04-15 00:00:00'	'UP'	'1d'
'litecoin'	'usd'	'2024-04-18 00:00:00'	'DOWN'	'1d'
'litecoin'	'usd'	'2024-04-17 00:00:00'	'UP'	'1d'
'litecoin'	'usd'	'2024-04-16 00:00:00'	'DOWN'	'1d'
'litecoin'	'usd'	'2024-04-15 00:00:00'	'DOWN'	'1d'
'bitcoin'	'usd'	'2024-04-18 00:00:00'	'UP'	'4h'
'bitcoin'	'usd'	'2024-04-17 20:00:00'	'UP'	'4h'
'bitcoin'	'usd'	'2024-04-17 16:00:00'	'DOWN'	'4h'

Let's say I want to query for the latest (ORDER BY date DESC) trend for every coinid/quotesymbol with interval = '1d' (1 day). Or in other words I want to WHERE interval = '1d' GROUP BY coinid, quotesymbol Also, I want to know the trend streak, so for how many time periods that trend is already the same.

Given the above example supertrend table data, my result should look like:

coinid	quotesymbol	latest_trend	trend_streak
'bitcoin'	'usd'	'UP'	3
'bitcoin'	'eur'	'UP'	2
'bitcoin'	'cny'	'DOWN'	1
'litecoin'	'usd'	'DOWN'	1

To clarify the above result: 'Bitcoin', 'usd' is 'UP' for 3 periods, because the trend for Bitcoin/USD was up on April 18, April 17 and April 16. And 'DOWN' on April 15, see sample supertrend table.

Additionally, I would like to know the aggregated supertrend over all quotesymbols (via mode function). That result should look like this:

coinid	latest_trend	trend_streak
'bitcoin'	'UP'	2
'litecoin'	'DOWN'	1

To clarify the above result: The latest Bitcoin/USD trend is 'UP, Bitcoin/EUR is 'EUR' is 'UP' and Bitcoin/CNY is 'DOWN'. The mode function takes ('UP', 'UP', 'DOWN') and returns the most frequent occuring value ('UP').

How can I query for this data in an efficient way considering the supertrend table has millions or billions of rows?

I was thinking of a materialized view but the lowest possible intervals for those supertrends are 1 minute and the might be written at distinct times, so that might be too high of a needed refresh rate.

I'm trying to avoid using a separate caching solution like Redis to keep the amount of needed architecture/environments simple, but I guess that could be an option especially for counters.

Answer 1

You can use ROW_NUMBER to determine the last value, and then use the a GROUP BY to get your count. then in the next step you again can use row_number to determine the trend with the highest count.

the window function ROW_NUMBER() does exactly as its name says it gives a row_number to every row. With a window function, you get that you can virtual group together rows and give a row_number to each group, so that you can have multiple 1 in your result set. in our case we want the last row for ever group so we use as order DESC

The LAGwindow function in a group for the previous value, it is often used like here to compare the current value with its predecessor and detect changes.

more about window function ca be sound in the manual

You can add to the WHERE clause the coinids you want to show.
Like WHERE rn = 1 AND "coinid" = 'bitcoin'

The second Query is a bit simpler, because first you detect the changes in every "coinid", "quotesymbol" and "tend".

The Window function sum then creates group, as youi are only intereets in the last, so we care only about the rn =m 1.

Your expected result confused me, but after constructing your the query i saw your error

CREATE TABLE superstreak (
  "coinid" VARCHAR(10),
  "quotesymbol" VARCHAR(5),
  "date" date,
  "trend" VARCHAR(4),
  "interval" VARCHAR(4)
);

INSERT INTO superstreak
  ("coinid", "quotesymbol", "date", "trend", "interval")
VALUES
  ('bitcoin', 'usd', '2024-04-18 00:00:00', 'UP', '1d'),
  ('bitcoin', 'usd', '2024-04-17 00:00:00', 'UP', '1d'),
  ('bitcoin', 'usd', '2024-04-16 00:00:00', 'UP', '1d'),
  ('bitcoin', 'usd', '2024-04-15 00:00:00', 'DOWN', '1d'),
  ('bitcoin', 'eur', '2024-04-18 00:00:00', 'UP', '1d'),
  ('bitcoin', 'eur', '2024-04-17 00:00:00', 'UP', '1d'),
  ('bitcoin', 'eur', '2024-04-16 00:00:00', 'DOWN', '1d'),
  ('bitcoin', 'eur', '2024-04-15 00:00:00', 'DOWN', '1d'),
  ('bitcoin', 'cny', '2024-04-18 00:00:00', 'DOWN', '1d'),
  ('bitcoin', 'cny', '2024-04-17 00:00:00', 'UP', '1d'),
  ('bitcoin', 'cny', '2024-04-16 00:00:00', 'UP', '1d'),
  ('bitcoin', 'cny', '2024-04-15 00:00:00', 'UP', '1d'),
  ('litecoin', 'usd', '2024-04-18 00:00:00', 'DOWN', '1d'),
  ('litecoin', 'usd', '2024-04-17 00:00:00', 'UP', '1d'),
  ('litecoin', 'usd', '2024-04-16 00:00:00', 'DOWN', '1d'),
  ('litecoin', 'usd', '2024-04-15 00:00:00', 'DOWN', '1d'),
  ('bitcoin', 'usd', '2024-04-18 00:00:00', 'UP', '4h'),
  ('bitcoin', 'usd', '2024-04-17 20:00:00', 'UP', '4h'),
  ('bitcoin', 'usd', '2024-04-17 16:00:00', 'DOWN', '4h');

WITH CTE AS (SELECT
  "coinid", "quotesymbol", "trend",
    ROW_NUMBER() 
    OVER(PARTITION By "coinid", "quotesymbol"
        ORDER BY "date" DESC       
    ) rn
FROM superstreak
  WHERE "interval" = '1d'),
  CTE2 as (
SELECT  "coinid", "trend" , COUNT(*) trend_streak  
  FROM CTE 
WHERE rn = 1 
GROUP BY "coinid","trend"),
CTE3 As (SELECT  "coinid", "trend" ,  trend_streak
  ,     ROW_NUMBER() 
    OVER(PARTITION By "coinid"
        ORDER BY trend_streak DESC       
    ) rn2
FROM CTE2)
SELECT  "coinid", "trend" ,  trend_streak
  FROM CTE3
  WHERE rn2 = 1

coinid	trend	trend_streak
bitcoin	UP	2
litecoin	DOWN	1

WITH CTE As (SELECT
  "coinid", "quotesymbol", "trend","date",
    CASE WHEN "trend" = lag( "trend")
    OVER(PARTITION By "coinid", "quotesymbol"
        ORDER BY "date" DESC       
    ) THEN 0 ELSE 1 END rn
FROM superstreak
  WHERE "interval" = '1d'),
CTE2 AS (SELECT "coinid", "quotesymbol", "trend"
,SUM( rn)
    OVER(PARTITION By "coinid", "quotesymbol"
        ORDER BY "date" DESC       
    ) rn
FROM CTE)
SELECT
"coinid", "quotesymbol", "trend", COUNT(*) trend_streak
FROM CTE2
WHeRE rn = 1
GROUP BY "coinid", "quotesymbol", "trend"
ORDER BY trend_streak DESC

coinid	quotesymbol	trend	trend_streak
bitcoin	usd	UP	3
bitcoin	eur	UP	2
bitcoin	cny	DOWN	1
litecoin	usd	DOWN	1

fiddle