Como consultar sequências agregadas?

Você pode usar ROW_NUMBERpara determinar o último valor e, em seguida, usar a GROUP BYpara obter sua contagem. então, na próxima etapa, você poderá usar row_number novamente para determinar a tendência com a contagem mais alta.

a função da janela ROW_NUMBER()faz exatamente o que seu nome diz: fornece um número de linha para cada linha. Com uma função de janela, você consegue agrupar linhas virtualmente e fornecer um row_number para cada grupo, para que você possa ter vários 1 em seu conjunto de resultados. no nosso caso, queremos a última linha de cada grupo, então usamos como ordemDESC

A LAGfunção de janela em um grupo para o valor anterior, é frequentemente usada como aqui para comparar o valor atual com seu antecessor e detectar alterações.

mais sobre a função da janela pode estar no manual

Você pode adicionar à WHEREcláusula os coinids que deseja mostrar.
Como WHERE rn = 1 AND "coinid" = 'bitcoin'

A segunda consulta é um pouco mais simples, pois primeiro você detecta as alterações em cada "coinid", "quotesymbolo" e "tend".

A função Window sum então cria um grupo, já que você só está interessado no último, então nos preocupamos apenas com rn =m 1.

Seu resultado esperado me confundiu, mas depois de construir sua consulta vi seu erro

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

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

violino