create table #u19 (UID int, UCD datetime); go insert int...

0

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

create table #u19 (UID int, UCD datetime);
go

insert into #u19
select u.Id UID, u.CreationDate UCD from Users u 
where u.CreationDate >= '2019-01-01' and u.CreationDate < '2020-01-01'
and 
exists (select * from Posts p2 where p2.OwnerUserId = u.Id and p2.Creationdate >= '2019-01-01' and p2.PostTypeId in (1,2))
--- 409753

-- select count (*) from #u19

create table #fpq3 (UID int, UCD datetime, FPID int, FPCD datetime);
go

insert into #fpq3
select u.UID UID, u.UCD UCD, p.Id FPID, p.CreationDate FPCD 
from #u19 u inner join Posts p on u.UID = p.OwnerUserId
where p.PostTypeId = 1 and p.CreationDate >= '2019-01-01'
and p.CreationDate = (select min (p2.CreationDate) from Posts p2 where p2.OwnerUserId = u.UID 
and p2.CreationDate > u.UCD and p2.PostTypeId in (1,2))
and not exists 
(select * from Posts p3 where p3.PostTypeId = 2 and p3.ParentId = p.Id)
           
select count (*) from #fpq3

select f.UID, f.UCD, f.FPID, f.FPCD, p.Id SPID, p.CreationDate SPCD, datediff (mi, f.FPCD, p.CreationDate)
from #fpq3 f inner join Posts p on f.UID = p.OwnerUserId
where p.CreationDate > f.FPCD 
and p.CreationDate =  (select min(CreationDate) from Posts p3 where p3.OwnerUserId = f.UID 
and p3.CreationDate > f.FPCD
and p3.PostTypeId in (1,2))

Enter Parameters

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