Weekly Streaks - Snowflake

Pothos45 0
Viewed 35

I have various questions about consecutive customer visits, it spans from a 90 days, to a monthly, quarterly, and currently the question is weekly. So I really am looking for easy to duplicate and edit code that can cover my bases.

I found some helpful articles to get me started, but am having some trouble deciphering the output, and is causing me some trouble with validating.

the code used is:

with output as 
    (select distinct
        unqclient
        ,dategroup
        ,datediff('week',min(weekofclass),max(weekofclass)) as streak
        ,min(weekofclass) as startdate
        ,max(weekofclass) as enddate
     
     from 
        (select 
            *
            ,dateadd('week',-rank,weekofclass) as dategroup
            
            from 
                (select distinct
                    concat(v.location, v.clientid) as unqclient
                    ,cast(v.classdate as date) as classdate2
                    ,date_trunc('week',classdate2) as weekofclass
                    ,dense_rank() over (partition by unqclient order by weekofclass) as rank
                 
                 from visit_data as v
                 where (missed=false and cancelled=false)
                 )
        )
    group by 1,2)
    
    
 select 
    output.*
    ,c.location
    ,c.emailname
    ,s.studioname
 
    from output 
        left join clients as c
            on concat(c.location,c.clientid)=output.unqclient
        left join studios as s
            on c.location=s.location
    
    
    where streak>1 
            and date_trunc('week',enddate)>=date_trunc('week',current_date()-7)
                
        order by streak desc

The days for the start and end dates are right (showing the monday of the week their first and last visit in the streak occurred). But I don't understand what 'dategroup' is outputting, they do not correspond to first visits of that client or take place during the streak.

Example of Output from code above:

Client Date Group Streak Start Date End Date
A 2020-03-02 116 2020-06-15 2022-09-05
B 2017-04-24 122 2020-05-04 2022-09-05
snowflake-cloud-data-platform
1 Answers

So if we start with some fake data with weekly streaks:

with visit_data(location, clientid, classdate, missed, cancelled ) as (
    select * from values
   -- (1, 10, '2022-06-15 10:12:13'::timestamp, false, false),
   -- (1, 10, '2022-06-22 10:12:13'::timestamp, false, false),
   -- (1, 10, '2022-06-29 10:12:13'::timestamp, false, false),
   -- (1, 10, '2022-07-06 10:12:13'::timestamp, false, false),
  --  (1, 10, '2022-07-13 10:12:13'::timestamp, false, false),
  --  (1, 10, '2022-07-13 10:12:13'::timestamp, false, false),
  --  (1, 10, '2022-07-20 10:12:13'::timestamp, false, false),
  --  (1, 10, '2022-07-27 10:12:13'::timestamp, false, false),
  --  (1, 10, '2022-08-03 10:12:13'::timestamp, false, false),
   -- (1, 10, '2022-08-10 10:12:13'::timestamp, false, false),
    (1, 10, '2022-08-17 10:12:13'::timestamp, false, false),
    (1, 10, '2022-08-24 10:12:13'::timestamp, false, false),
    (1, 10, '2022-08-31 10:12:13'::timestamp, false, false),
    (1, 10, '2022-09-7 10:12:13'::timestamp, false, false),
    (1, 10, '2022-09-14 10:12:13'::timestamp, false, false)
), clients(location, clientid, emailname) as (
    select * from values
    (1, 10, 'email_10@example.com')    
), studios(location, studioname) as (
    select * from values
    (1, 'location_1') 
)

we can move the two inner sub-select into one block and inspect it:

 select distinct
        v.location
        ,v.clientid
        ,date_trunc('week', v.classdate) as weekofclass
        ,dense_rank() over (partition by v.location, v.clientid order by weekofclass) as rank
        ,dateadd('week', -rank, weekofclass) as dategroup
    from visit_data as v
    where missed = false and cancelled = false

gives:

LOCATION CLIENTID WEEKOFCLASS RANK DATEGROUP
1 10 2022-08-15 00:00:00.000 1 2022-08-08 00:00:00.000
1 10 2022-08-22 00:00:00.000 2 2022-08-08 00:00:00.000
1 10 2022-08-29 00:00:00.000 3 2022-08-08 00:00:00.000
1 10 2022-09-05 00:00:00.000 4 2022-08-08 00:00:00.000
1 10 2022-09-12 00:00:00.000 5 2022-08-08 00:00:00.000

we can drop the timestamp -> date cast (if that is what is happening) as date trunc will reduce it down more, if it was a string inside visit_data it shouldn't be, can be inline case with:

,date_trunc('week', v.classdate::date) as weekofclass

next the date group can be calculated at the same time.

Datagroup is just a clustering group, it meaning is not really important, how it works is the dense_rank will give increasing numbers over the distinct truncated weeks, and minusing that rank from the week, if they are part of the same batch will end up with the same result

If we put some "gaps in the data"

with visit_data(location, clientid, classdate, missed, cancelled ) as (
    select * from values
   (1, 10, '2022-06-15 10:12:13'::timestamp, false, false),
   (1, 10, '2022-06-22 10:12:13'::timestamp, false, false),
 --  (1, 10, '2022-06-29 10:12:13'::timestamp, false, false),
   (1, 10, '2022-07-06 10:12:13'::timestamp, false, false),
    (1, 10, '2022-07-13 10:12:13'::timestamp, false, false),
    (1, 10, '2022-07-13 10:12:13'::timestamp, false, false),
    (1, 10, '2022-07-20 10:12:13'::timestamp, false, false),
  --  (1, 10, '2022-07-27 10:12:13'::timestamp, false, false),
  --  (1, 10, '2022-08-03 10:12:13'::timestamp, false, false),
   -- (1, 10, '2022-08-10 10:12:13'::timestamp, false, false),
    (1, 10, '2022-08-17 10:12:13'::timestamp, false, false),
    (1, 10, '2022-08-24 10:12:13'::timestamp, false, false),
    (1, 10, '2022-08-31 10:12:13'::timestamp, false, false),
    (1, 10, '2022-09-7 10:12:13'::timestamp, false, false),
    (1, 10, '2022-09-14 10:12:13'::timestamp, false, false)
)

we get:

LOCATION CLIENTID WEEKOFCLASS RANK DATEGROUP
1 10 2022-06-13 00:00:00.000 1 2022-06-06 00:00:00.000
1 10 2022-06-20 00:00:00.000 2 2022-06-06 00:00:00.000
1 10 2022-07-04 00:00:00.000 3 2022-06-13 00:00:00.000
1 10 2022-07-11 00:00:00.000 4 2022-06-13 00:00:00.000
1 10 2022-07-18 00:00:00.000 5 2022-06-13 00:00:00.000
1 10 2022-08-15 00:00:00.000 6 2022-07-04 00:00:00.000
1 10 2022-08-22 00:00:00.000 7 2022-07-04 00:00:00.000
1 10 2022-08-29 00:00:00.000 8 2022-07-04 00:00:00.000
1 10 2022-09-05 00:00:00.000 9 2022-07-04 00:00:00.000
1 10 2022-09-12 00:00:00.000 10 2022-07-04 00:00:00.000

we see the rank steps in ones, but some week have a gap, and thus the minus gives a different number. This is called Gaps And Islands

After that, the GROUP BY and DISTINCT in the output is redunant, you should only need one or the other, not both

in the outer select, the date_trunc('week',enddate) is not needed as the date is already truncated to week, and functions should be avoid on WHERE clauses, and it can get pushed as a HAVING into the output CTE.

thus with the data CTE's included:

with visit_data(location, clientid, classdate, missed, cancelled ) as (
    select * from values
   (1, 10, '2022-06-15 10:12:13'::timestamp, false, false),
   (1, 10, '2022-06-22 10:12:13'::timestamp, false, false),
 --  (1, 10, '2022-06-29 10:12:13'::timestamp, false, false),
   (1, 10, '2022-07-06 10:12:13'::timestamp, false, false),
    (1, 10, '2022-07-13 10:12:13'::timestamp, false, false),
    (1, 10, '2022-07-13 10:12:13'::timestamp, false, false),
    (1, 10, '2022-07-20 10:12:13'::timestamp, false, false),
  --  (1, 10, '2022-07-27 10:12:13'::timestamp, false, false),
  --  (1, 10, '2022-08-03 10:12:13'::timestamp, false, false),
   -- (1, 10, '2022-08-10 10:12:13'::timestamp, false, false),
    (1, 10, '2022-08-17 10:12:13'::timestamp, false, false),
    (1, 10, '2022-08-24 10:12:13'::timestamp, false, false),
    (1, 10, '2022-08-31 10:12:13'::timestamp, false, false),
    (1, 10, '2022-09-7 10:12:13'::timestamp, false, false),
    (1, 10, '2022-09-14 10:12:13'::timestamp, false, false)
), clients(location, clientid, emailname) as (
    select * from values
    (1, 10, 'email_10@example.com')    
), studios(location, studioname) as (
    select * from values
    (1, 'location_1') 
), inner_a as(
    select distinct
        v.location
        ,v.clientid
        ,date_trunc('week', v.classdate) as weekofclass
        ,dense_rank() over (partition by v.location, v.clientid order by weekofclass) as rank
        ,dateadd('week', -rank, weekofclass) as dategroup
    from visit_data as v
    where missed = false and cancelled = false
    order by rank
)
--with 
,output as (
    select --distinct
        location
        ,clientid
        ,dategroup
        ,min(weekofclass) as startdate
        ,max(weekofclass) as enddate
        ,datediff('week', startdate, enddate) as streak
     from inner_a
    group by 1,2,3
    having enddate >= date_trunc('week',current_date()-7)
)
select 
    o.*
    ,c.location
    ,c.emailname
    ,s.studioname 
from output as o
left join clients as c
    on c.location = o.location 
        and c.clientid = o.clientid
left join studios as s
    on c.location = s.location
where streak>1 
order by streak desc

gives:

LOCATION CLIENTID DATEGROUP STARTDATE ENDDATE STREAK LOCATION EMAILNAME STUDIONAME
1 10 2022-07-04 00:00:00.000 2022-08-15 00:00:00.000 2022-09-12 00:00:00.000 4 1 email_10@example.com location_1
Simeon Pilgrim0
Related