count consecutive column values with same other column value-CodePudding

I have a WITH SQL statement who select user's activities from a view.

WITH activities AS (
    SELECT a.user_uuid as user_uuid,
           EXTRACT(WEEK FROM a.activity_at) as week_number,
           EXTRACT(DOW FROM a.activity_at) AS dow
    FROM activities_view_validated a
    WHERE a.activity_at > '2022-01-01 00:00:00'
      AND a.activity_type = 'xxx'
    GROUP BY a.user_uuid, a.activity_at
)
SELECT a.user_uuid, a.week_number, a.dow as dow
FROM activities a
GROUP BY 1, 2, 3
ORDER BY 1, 2 , 3
;

The output look like this (uuid's are truncated)

  user_uuid   | week_number | dow
-------------- ------------- -----
 000debd96628 |           4 |   2
 000debd96628 |          11 |   6
 001034f0d828 |           3 |   5
 001e8576e9a7 |          11 |   1
 001e8576e9a7 |          12 |   1
 001e8576e9a7 |          13 |   1
 0023e9905086 |          12 |   6
 0025cfcb6a1e |           1 |   3
 0025cfcb6a1e |           2 |   5
 0025cfcb6a1e |           3 |   0
 0025cfcb6a1e |           4 |   0
 0025cfcb6a1e |           6 |   0
 00277fec3a8f |           5 |   4
 002cf17fa0da |          13 |   1
 0041f6e950f8 |           5 |   6
 0041f6e950f8 |           6 |   6
 0041f6e950f8 |           7 |   6
 0041f6e950f8 |          10 |   2
 0041f6e950f8 |          10 |   6
 0041f6e950f8 |          11 |   2
 0041f6e950f8 |          13 |   2
 0050c9c16ec4 |           6 |   6
 0050c9c16ec4 |          10 |   0
 00614a48d8de |           7 |   1
 00614a48d8de |          11 |   1
 00614a48d8de |          12 |   1
 00614a48d8de |          13 |   1
 006495e46a1a |          12 |   6

I want to query user_uuid with their consecutive week_number count having the same day of the week (dow).

So something like that in output, for the three consecutive tuesday of activity on weeks 11, 12, 13 of 001e8576e9a7 user_uuid

  user_uuid   | count 
-------------- -------
 001e8576e9a7 |     3

Is there a solution that can handle to loop to the first one if the curent week is the last.

CodePudding user response：

Here we use the difference between dense_rank and week_number as group number, which means that the group number changes if there is a gap in the data.
The user with user_uuid '00614a48d8de' was present week 7 day 1 then weeks 11,12 & 13 day 1 so we have 2 groups: 1 with count 1 and 1 with count 3.

;with rn as
(select
 user_uuid   , week_number , dow,
 dense_rank() over (partition by user_uuid, dow order by week_number) dr
 from activities)
select 
  user_uuid,
  dow,
  (week_number - dr ) as "group",
  count(*)
from rn
where user_uuid='00614a48d8de'
group by 
  user_uuid,
  dow,
  week_number - dr 
order by
  user_uuid,
  dow;

user_uuid    | dow | group | count
:----------- | --: | ----: | ----:
00614a48d8de |   1 |     6 |     1
00614a48d8de |   1 |     9 |     3

db<>fiddle here