Q&A about the site for members of the workforce navigating the professional setting
--estimated reputation from upvotes and downvotes --does not take into account: ---- 1. daily reputation cap ---- 2. reputation from suggested edits ---- 3. reputation deduced for downvoting ---- 4. offered bounties ---- 5. some posts might have been deleted since then - but still carry reputation ; with rep_cte as (SELECT p.OwnerUserId AS uid, --- p.OwnerDisplayName AS udn SUM(case v.votetypeid WHEN 1 THEN 15 -- accept WHEN 2 THEN 10 -- upvote --- CASE p.posttypeid --- WHEN 1 THEN 5 -- upvote question --- WHEN 2 THEN 10 -- upvote answer --- END WHEN 3 THEN -2 -- downvote WHEN 9 THEN BountyAmount -- collected bounty END) as Rep FROM Posts p INNER JOIN Votes v ON v.PostId=p.Id INNER JOIN Users u ON p.OwnerUserId=u.Id WHERE (DATEDIFF(day, u.CreationDate, v.CreationDate) <= ##n?365##) ---only votes/reputation in the first 365 days AND (p.CommunityOwnedDate IS NULL) --not CW posts GROUP BY p.OwnerUserId ) SELECT Year(u.CreationDate), ---COUNT(u.Id) AS [Total], ---SUM(Case When (r.rep>=##min?1000##) Then 1 Else 0 End) AS [Established] ROUND((CAST(SUM(Case When (r.rep>=##min?1000##) Then 1 Else 0 End) AS decimal))*100.0/(CAST(COUNT(u.Id) AS decimal)),3) FROM rep_cte r INNER JOIN Users u ON r.uid=u.Id GROUP BY Year(u.CreationDate) ORDER BY Year(u.CreationDate)