CREATE TABLE #FPA (UID int NOT NULL, UCD DATETIME, FPID i...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Philosophy Meta

Q&A about the site for those interested in the study of the fundamental nature of knowledge, reality, and existence

CREATE TABLE #FPA (UID int NOT NULL, UCD DATETIME, FPID int, FPCD DATETIME);
go

CREATE TABLE #FPA2 (UID int NOT NULL, UCD DATETIME, FPID int, FPCD DATETIME, SPID int, SPCD DATETIME, PCDDiff int);
go


insert into #FPA 
select u.ID as UID, u.CreationDate as UCD, p.Id as FPID, p.CreationDate as FPCD
from Users u inner join Posts p on u.Id = p.OwnerUserId
where u.Creationdate >= '2019-01-01' and u.CreationDate < '2020-01-01'
and p.CreationDate = (select min (Creationdate) from Posts p2 where p2.OwnerUserId = u.Id and p2.PostTypeId in (1,2))
and p.PostTypeId = 2

-- select count (*) from #FPA  --  84398

--select top 43000 * from #FPA order by UID, UCD, FPID, FPCD

select v.PostId, count(distinct v.Id) K -- count how many upvotes each PostId has got
from #FPA fpa inner join Votes v on v.PostId = fpa.FPID where v.VoteTypeId = 2 -- upvotes : 2    downvotes : 3
and v.CreationDate > fpa.FPCD
and v.CreationDate < (select min(Creationdate) from Posts p2 where p2.OwnerUserId = fpa.UID and p2.Creationdate > fpa.FPCD) -- CD of second Post
group by v.PostId
order by v.PostId

Enter Parameters

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