How check value by consecutive date variable-CodePudding

I have database table in SNOWFLAKE, where I need check for each customer if there is FLAG_1 == 1 at minimum 3 days in row. Flag_1 indicates whether the order contained any specific goods. And create new table with customer_id and flag_2. I really don't know how to handle this problem.

Sample table:

CREATE TABLE TMP_TEST
(
CUSTOMER_ID INT,
ORDER_DATE DATE,
FLAG_1 INT
);


INSERT INTO TMP_TEST (CUSTOMER_ID, ORDER_DATE, FLAG_1)
VALUES
  (001, '2020-04-01', 0),
  (001, '2020-04-02', 1),
  (001, '2020-04-03', 1),
  (001, '2020-04-04', 1),
  (001, '2020-04-05', 1),
  (001, '2020-04-06', 0),
  (001, '2020-04-07', 0),
  (001, '2020-04-08', 0),
  (001, '2020-04-09', 1),
  (002, '2020-04-10', 1),
  (002, '2020-04-11', 0),
  (002, '2020-04-12', 0),
  (002, '2020-04-13', 1),
  (002, '2020-04-14', 1),
  (002, '2020-04-15', 0),
  (002, '2020-04-16', 1),
  (002, '2020-04-17', 1);

Expected output table:

CUSTOMER_ID   FLAG_2
001   1
002   0

CodePudding user response：

Maybe this can be help:

with calcflag as (
select customer_id, IFF( sum(flag_1) over (PARTITION by customer_id order by order_date rows between 3 preceding and 1 preceding) = 3, 1, 0 ) as new_flag 
from tmp_Test)
select customer_id, max(new_flag) flag_2
from calcflag
group by 1
order by 1;

 ------------- -------- 
| CUSTOMER_ID | FLAG_2 |
 ------------- -------- 
|           1 |      1 |
|           2 |      0 |
 ------------- --------

CodePudding user response：

using COUNT_IF also works:

with calcflag as (
    select 
        customer_id, 
        IFF(
            count_if(flag_1 = 1) over (
                PARTITION by customer_id 
                order by order_date 
                rows between 2 preceding and current row
            ) = 3, 1, 0
        ) as new_flag 
    from tmp_Test
)
select 
    customer_id, 
    max(new_flag) flag_2
from calcflag
group by 1

 ------------- -------- 
| CUSTOMER_ID | FLAG_2 |
|------------- --------|
|           1 |      1 |
|           2 |      0 |
 ------------- --------