longest_streak

1

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers

Q&A for Drupal developers and administrators

with dates as (
select
  distinct OwnerUserId, 
  CAST(CreationDate as DATE) as date
 from posts
 ), groups as (
 select
   OwnerUserId,
   date,
   row_number() over (partition by OwnerUserId order by date) as rn,
   dateadd(day, -row_number() over (partition by OwnerUserId order by date), date) as grp
 from dates
), agged as (
select
  grp,
  OwnerUserId,
  count(*) as consecutive_days,
  min(date) as first_date,
  max(date) as last_date
from groups
group by grp, OwnerUserId
)
select 
  OwnerUserId,
  max(consecutive_days) as longest_streak
from agged
group by OwnerUserId

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results