0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Tridion developers and administrators

Select myuser.Id, myuser.Tenure,
share.Ans_before,
upv.upv_before,
edit.Edit_before,
com.com_before
FROM
(
SELECT DISTINCT Users.Id,
    DATEDIFF(day,Users.CreationDate, GETDATE()) as [Tenure]
    FROM Users
    inner join Posts p on p.owneruserid = Users.Id
    inner join Votes v on p.Id = v.postid
    WHERE (p.PostTypeId =2 AND v.votetypeid =2 AND ((AboutMe LIKE '%buymeacoffee%') OR (AboutMe LIKE '%ko-fi%')) 
    AND reputation > 10)
) as myuser
LEFT JOIN(
    SELECT p.OwnerUserId, COUNT(p.Id) AS [Ans_before] 
    FROM Posts as p
    WHERE p.CreationDate BETWEEN DATEADD(week,-2,'##Date1?2021-10-01##') AND '##Date1?2021-10-01##'
    AND p.PostTypeId =2
    GROUP BY p.OwnerUserId) as share ON myuser.Id = share.OwnerUserId
LEFT JOIN(
SELECT u.Id, COUNT(v.Id) AS [upv_before] 
    FROM votes v
   INNER JOIN posts p
    ON v.PostId = P.Id 
   INNER JOIN users u
    ON P.OwnerUserId = u.Id
   WHERE (v.CreationDate BETWEEN DATEADD(week,-2,'##Date1?2021-10-01##') AND '##Date1?2021-10-01##')
    AND p.PostTypeId =2
    AND v.VoteTypeId = 2
    GROUP BY u.Id) as upv ON myuser.Id = upv.Id
LEFT JOIN(
SELECT u.Id, COUNT(ph.Id) AS [Edit_before] 
    FROM PostHistory ph
   INNER JOIN posts p ON ph.PostId = p.Id 
   INNER JOIN users u ON p.OwnerUserId = u.Id 
   WHERE p.OwnerUserId = ph.UserId 
    AND (ph.CreationDate BETWEEN DATEADD(week,-2,'##Date1?2021-10-01##') AND '##Date1?2021-10-01##')
    AND p.PostTypeId =2
    AND ph.PostHistoryTypeId = 5
    GROUP BY u.Id) as edit ON myuser.Id = edit.Id
LEFT JOIN(
SELECT u.Id, COUNT(c.Id) as [com_before]
   FROM Comments c
   INNER JOIN Posts p on c.PostId = p.Id
   INNER JOIN users u ON P.OwnerUserId = u.Id
   WHERE p.OwnerUserId = c.UserId
    AND (c.CreationDate BETWEEN DATEADD(week,-2,'##Date1?2021-10-01##') AND '##Date1?2021-10-01##')
    AND p.PostTypeId =2
    GROUP BY u.Id) as com ON myuser.Id = com.Id

fork query

edited jan 28

created jan 28 bwhoslkw