All the distinct days on which I created posts

0

Please login or register to vote for this query.

(click on this box to dismiss)

Open Data

Q&A for developers and researchers interested in open data

With groups (Userid,date,grp) as
(Select
Distinct
OwnerUserid,
cast(creationdate as date),
dateadd(day,-Dense_rank() over (partition by OwnerUserid order by cast(Creationdate as date)), cast(creationdate as date)) as grp

From Posts
where OwnerUserId = ##UserId##

),
Consecutive_info as
(
Select
UserId,
count(*) as consecutive_Days,
min(date) as min_date,
max(date) as max_date

From groups
Group by Userid, grp
),
consecutive_Ranked as
(
Select
*,
row_number() over (partition by Userid order by consecutive_days desc, min_Date desc) as row_num

From 
consecutive_info
)
Select
Userid,
consecutive_days,
min_Date,
max_date

From consecutive_ranked
Where row_num = 1;

Enter Parameters

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