create table #MV (UID int, UCD datetime, FPID int, FPCD d...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

create table #MV (UID int, UCD datetime, FPID int, FPCD datetime, NbrUV int, NbrDV int, SPID int, SPCD datetime);
go

insert into #MV 
select u.Id UID, u.CreationDate UCD, p.Id FPID, p.CreationDate FPCD, 0, 0, p.Id SPID, p.CreationDate SPCD
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 (p2.CreationDate) from Posts p2 where p2.OwnerUserId = u.Id and PostTypeId in (1,2))
and p.CreationDate > u.CreationDate  --- there are a couple of anomalies where p.Creationdate < u.CreationDate
and p.PostTypeId = 1

update #MV 
set NbrUV = t2.K
FROM (
select mv.FPID PID, count(v.Id) K
from Votes v inner join #MV mv on v.PostId = mv.FPID
where v.VoteTypeId = 2
group by mv.FPID) as t2
where t2.PID = #MV.FPID

update #MV 
set NbrDV = t2.K
FROM (
select mv.FPID PID, count(v.Id) K
from Votes v inner join #MV mv on v.PostId = mv.FPID
where v.VoteTypeId = 3
group by mv.FPID) as t2
where t2.PID = #MV.FPID

-- select count (*) from #MV where NbrUV = NbrDV

select top 50000 * from #MV  where UID > 11376624 order by UID

Enter Parameters

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