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, DELA int);
go

insert into #MV 
select u.Id UID, u.CreationDate UCD, p.Id FPID, p.CreationDate FPCD, 0, 0, 0 SPID, p.CreationDate SPCD, 0 DELA
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
--- 325000

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 order by UID   --- # 181007

update #MV 
set SPID = t2.SPID, SPCD = t2.SPCD, DELA = datediff(mi, #MV.FPCD, t2.SPCD) 
FROM (
select mv.FPID FPID, p3.Id SPID, p3.CreationDate SPCD
from Posts p3 inner join #MV mv on p3.OwnerUserId = mv.UID
where p3.CreationDate = (select min(CreationDate) from Posts p4 where p4.CreationDate > mv.FPCD 
and p4.OwnerUserId = mv.UID and p4.PostTypeId in (1, 2))) as t2
where t2.FPID = #MV.FPID

select top 50000 * from #MV where UID > 11923324 order by UID  -- e

Enter Parameters

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