First posts closed compared to second posts closed

0

Please login or register to vote for this query.

(click on this box to dismiss)

Software Engineering

Q&A for professionals, academics, and students working within the systems development life cycle

with PostsClosedInLastYear as (
  select u.Id as UserId,
  u.CreationDate as UserCreationDate,
  p.ClosedDate as ClosedDate,
  p.CreationDate as CreationDate,
  p.Id as PostId,
  RANK() OVER 
    (PARTITION BY u.Id ORDER BY p.CreationDate ASC) AS Rank
  from
    Posts p inner join
    Users u on p.OwnerUserId = u.Id
    inner join PostTypes pt on pt.Id = p.PostTypeId
    where u.CreationDate > DateAdd(year, -1, GetDate())

    and pt.Id = 1 --question
), FirstPosts as (
  select UserId, UserCreationDate, PostId,
    ClosedDate, CreationDate
  from PostsClosedInLastYear
  where PostsClosedInLastYear.ClosedDate is not null
  and PostsClosedInLastYear.ClosedDate <=
      DateAdd(hour, 48, PostsClosedInLastYear.CreationDate)
  and Rank = 1
), SecondPosts as (
  select UserId, UserCreationDate, PostId,
    ClosedDate, CreationDate
  from PostsClosedInLastYear
  where Rank = 2
)

select u.Id,
FirstPosts.PostId as FirstPostId,
FirstPosts.CreationDate as FirstPostCreationDate,
FirstPosts.ClosedDate as FirstPostClosedDate,
SecondPosts.PostId as SecondPostId,
SecondPosts.CreationDate as SecondPostCreationDate,
SecondPosts.CreationDate as SecondPostCreationDate,
SecondPosts.ClosedDate as SecondPostClosedDate,
(case when SecondPosts.ClosedDate is not null
  and SecondPosts.ClosedDate >
  DateAdd(hour, 48, SecondPosts.CreationDate)
  then 1 else 0 end) as SecondPostClosedAfter48Hours,
(case when SecondPosts.ClosedDate is not null
  and SecondPosts.ClosedDate <=
  DateAdd(hour, 48, SecondPosts.CreationDate)
  then 1 else 0 end) as SecondPostClosedWithin48Hours

from Users u
  inner join FirstPosts on FirstPosts.UserId = u.Id
left join SecondPosts on SecondPosts.UserId = u.Id

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results