Search code examples
sqlamazon-redshiftwindow-functions

Redshift SQL - Partition with CASE expression

I am attempting to create a rank with a partition which partitions by "cust_id" and ordered by "id" however I need the "rank" to increase based on the "variable" column.

enter image description here

Above is a screenshot of how I would like the result set to appear - Can anyone point me in the right direction on how I would go about this?

Solution

  • Use SUM(variable) as the window function instead of RANK / ROW_NUMBER:

    Schema (PostgreSQL v15)

    CREATE TABLE t(
  cust_id int,
  id int,
  variable int
);


INSERT INTO t VALUES
(1, 1, 1),
(1, 2, 0),
(1, 3, 1),
(1, 4, 1),
(1, 5, 0),
(1, 6, 0),
(1, 7, 1),
(2, 1, 1),
(2, 2, 0),
(2, 3, 1);

    Query #1

    SELECT *
     , SUM(variable) OVER (
         PARTITION BY cust_id 
         ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
FROM t
ORDER BY cust_id, id;
    cust_id id variable sum
    1 1 1 1
    1 2 0 1
    1 3 1 2
    1 4 1 3
    1 5 0 3
    1 6 0 3
    1 7 1 4
    2 1 1 1
    2 2 0 1
    2 3 1 2

    View on DB Fiddle