Snowflake - How to create summary table containing unique records

user_18162391 0
Viewed 81

I'm looking for some Snowflake syntax assistance in how to generate a summary table or view from an existing table. My summary table should have 1 row per unique id from the existing table along with boolean values indicating if the various milestones (as per the summary column names) have been hit. Any help is appreciated as I am a Snowflake novice. Thanks.

Existing Table

Desired Summary Table/View

snowflake-cloud-data-platform
2 Answers

So using Himanshu's data, thank you:

WITH fake_data(id, updated, pipeline_id, stage_id) AS (
    SELECT column1, to_date(column2,'mm/dd/yyyy hh:mm:ss'), column3, column4
    FROM VALUES
        (1111, '02/01/2022 09:01:00', 'A', '1' ),
        (1111, '02/01/2022 10:01:00', 'A', '2' ),
        (1111, '02/01/2022 11:01:00', 'B', '5' ),                          
        (2222, '02/02/2022 13:01:00', 'A', '1' ),
        (2222, '02/03/2022 18:01:00', 'B', '5' ),
        (2222, '02/04/2022 07:01:00', 'B', '6' ),                                         
        (3333, '02/02/2022 14:01:00', 'A', '1' ),
        (3333, '02/03/2022 18:01:00', 'A', '2' ),
        (3333, '02/03/2022 07:01:00', 'C', '7' ),
        (3333, '02/03/2022 21:01:00', 'C', '8' ),
        (3333, '02/05/2022 17:01:00', 'C', '9' )
)

we are doing an aggregation across each id and we want to use COUNT_IF to see how many row meet out criteria, and if it is >0 we are happy

SELECT 
    id, 
    count_if(pipeline_id='A')>0 AS hit_stage_a,
    count_if(pipeline_id='B')>0 AS hit_stage_b,
    count_if(pipeline_id='C')>0 AS hit_stage_c,
    count_if(stage_id='4')>0 AS hit_stage_4,
    count_if(stage_id='5')>0 AS hit_stage_5,
    count_if(stage_id='6')>0 AS hit_stage_6
FROM fake_data
GROUP BY 1
ORDER BY 1;

gives:

ID HIT_STAGE_A HIT_STAGE_B HIT_STAGE_C HIT_STAGE_4 HIT_STAGE_5 HIT_STAGE_6
1111 TRUE TRUE FALSE FALSE TRUE FALSE
2222 TRUE TRUE FALSE FALSE TRUE TRUE
3333 TRUE FALSE TRUE FALSE FALSE FALSE
user_439920

try this and see if this helps to get what you want.

SELECT ID, decode(HIT_PIPELINE_A, NULL,FALSE,TRUE) ,
           decode(HIT_PIPELINE_B, NULL,FALSE,TRUE),
           decode(HIT_PIPELINE_C, NULL,FALSE,TRUE),
           decode(HIT_STAGE_4, NULL,FALSE,TRUE),
           decode(HIT_STAGE_5, NULL,FALSE,TRUE),
           decode(HIT_STAGE_6, NULL,FALSE,TRUE) FROM 
(
SELECT * from tab1
PIVOT(MAx(PIPELINE_ID) FOR stage_id IN ('1','2','3','4','5','6')) 
 AS P(ID,DT,HIT_PIPELINE_A,HIT_PIPELINE_B,HIT_PIPELINE_C,HIT_STAGE_4,HIT_STAGE_5,HIT_STAGE_6)
) order by ID; 

                                        
 
  
create or replace table Tab1  (ID varchar2(100), updated date, pipeline_id varchar2(100), stage_id varchar2(10));
insert into tab1 values(1111, to_date('02/01/2022 09:01:00','mm/dd/yyyy hh:mm:ss'), 'A', '1' );
insert into tab1 values(1111, to_date('02/01/2022 10:01:00','mm/dd/yyyy hh:mm:ss'), 'A', '2' );
insert into tab1 values(1111, to_date('02/01/2022 11:01:00','mm/dd/yyyy hh:mm:ss'), 'B', '5' );

insert into tab1 values(2222, to_date('02/02/2022 13:01:00','mm/dd/yyyy hh:mm:ss'), 'A', '1' );
insert into tab1 values(2222, to_date('02/03/2022 18:01:00','mm/dd/yyyy hh:mm:ss'), 'B', '5' );
insert into tab1 values(2222, to_date('02/04/2022 07:01:00','mm/dd/yyyy hh:mm:ss'), 'B', '6' );

insert into tab1 values(3333, to_date('02/02/2022 14:01:00','mm/dd/yyyy hh:mm:ss'), 'A', '1' );
insert into tab1 values(3333, to_date('02/03/2022 18:01:00','mm/dd/yyyy hh:mm:ss'), 'A', '2' );
insert into tab1 values(3333, to_date('02/03/2022 07:01:00','mm/dd/yyyy hh:mm:ss'), 'C', '7' );
insert into tab1 values(3333, to_date('02/03/2022 21:01:00','mm/dd/yyyy hh:mm:ss'), 'C', '8' );
insert into tab1 values(3333, to_date('02/05/2022 17:01:00','mm/dd/yyyy hh:mm:ss'), 'C', '9' );
user_112279190
Related