Conditional Row Numbering - PrestoDB

Question

I need help with my PrestoDB Query. I need to only change the row number to the next if my column_1 like statement is true. Current I have set it to 0 but ive tried using "lag" "lead" functions and a few other way out the window ideas with no success.

My query:

WITH
  "FileCTE" AS (
   SELECT
     "tipo"
   , "column_1"
   , "Debe"
   , "Haber"
   , "Fecha"
   , "Numero"
   , "ROW_NUMBER"() OVER () "row"
   , "Saldo"
   , "Saldo_inicial"
   , "file_name"
   FROM
     "e90df27ca05f47da96d0b3966744739b"."d1c533d450b2490f945966ae78b5f830"
) 
SELECT
  "FileCTE"."tipo"
, (CASE WHEN ("FileCTE"."column_1" LIKE '%Cuenta%') THEN "row_number"() OVER () ELSE 0 END) "row"
, "FileCTE"."column_1"
, "FileCTE"."Debe"
, "FileCTE"."Haber"
, "FileCTE"."Fecha" "Date"
, "FileCTE"."Numero" "number"
, "Saldo"
, "Saldo_inicial"
, "file_name"
FROM
  "FileCTE"

Currently Getting:

I need this ->

Answer 1

PostgreSQL Query

This query analyzes the tblRowNum_Test table, calculating a cumulative group number based on the presence of the text 'value' in column_1, and assigns a dense rank to rows based on this group number, resulting in a final row number output.

Change the table and case condition according to yours. You will need some field to do an order to get the cumulative group number.

-- Create the table
DROP TABLE IF EXISTS tblRowNum_Test;

CREATE TABLE tblRowNum_Test (
    id SERIAL PRIMARY KEY,    
    column_1 TEXT
);

-- Insert 10 records with varying empty values
INSERT INTO tblRowNum_Test (column_1)
VALUES
    ('Value1'), (''), ('Value2'),     
    (''), (''), ('Value3');            

-- Analyze the data with row numbering and grouping
WITH RankedData AS (
    SELECT
        id,
        column_1,
        SUM(CASE WHEN lower(column_1) LIKE '%value%' THEN 1 ELSE 0 END)
            OVER (ORDER BY id) AS group_number -- Calculate a cumulative group number based on 'value' presence
    FROM tblRowNum_Test
)
SELECT
    id,
    column_1,
    DENSE_RANK() OVER (ORDER BY group_number) AS final_row_number   -- Assign a dense rank based on the group number
FROM RankedData;

Results