People Reached (Official)

0

Please login or register to vote for this query.

(click on this box to dismiss)

"Impact" from profile page, broken down to post-level detail - showing which posts were or were not included in the "People Reached" metric. All Q&A's included are listed for specified user except deleted.

Engineering

Q&A for professionals and students of engineering

/*  "People Reached" is the total [viewcount] from all of your Questions (except deleted)
    plus Answers : Views of the parent question for answers that are:
        - Non-deleted ...AND  
        - Score > 0   ...AND
        - ALSO meets one or more of the following criteria:
           (#1)  Is the Accepted Answer            ...OR
           (#2)  Score >=5                         ...OR
           (#3)  Has >=20% of the total vote count ...OR
           (#4)  Is within in the top 3 answers                                               v.2     
                                                            
                      Click to 2nd "tab" for total (as of "last Sunday").                         */

--not exactly my tidiest, most efficient query ever...  but I *think* it's calculating properly.
--(tough to confirm since it only matches the live site once a week)

--possible faster vote-tally method: https://data.stackexchange.com/stackoverflow/query/876984

set nocount on
declare @dt1 varchar(25) = convert(varchar,getdate(),21)
declare @dtUpdated datetime = (select min(create_date) from sys.tables)

select   -----------------------------> (list eligible answers)
  creationdate,
  id,
  parentid,
  score
into #eligibleAnswers from posts
where
  owneruserid = ##UserID## --my posts
  and posttypeid = 2       --answers only
  --and deletiondate is null
  --(Deleted questions are ALREADY EXCLUDED from this table)
  and score > 0            --positive score
--order by creationdate 

declare @dt2 varchar(25) = convert(varchar,getdate(),21)

select   -----------------------------> (populate #1,#2)
  a.creationdate,
  a.id as aID, --answer ID
  a.parentid,  --question ID
  'site://a/'+cast(a.id as varchar)+'|A:'+q.title as [Link],
  q.viewcount, --question #views
  case when a.id = q.acceptedanswerid then 1 else 0 end as [1of4:IsAccepted],
  a.score,     --answer score
  case when a.score >= 5 then 1 else 0 end as [2of4:ScoreMin5],
  --total vote count of all answers of this question
  (  select sum(aa.Score) as [Votes:all]
        from posts aa
        where aa.parentid = q.id
     ) as [Votes:all],
  --total votes on my answer
  (  select a.Score as [Votes:me] ) as [Votes:me]
into #answers2 from #eligibleAnswers a 
  left outer join posts q on a.parentid = q.id
where q.posttypeid = 1 

declare @dt3 varchar(25) = convert(varchar,getdate(),21)

print '1-2: '+format(datediff(ms,@dt1,@dt2),'0ms')

select *, -----------------------------> (populate #3)
  case when (cast([Votes:me] as decimal) / [Votes:all]) >= 0.2 
    then 1 else 0 end as [3of4:20pVotes]
into #answers3 from #answers2  

declare @dt4 varchar(25) = convert(varchar,getdate(),21)

--create list of all answers per question
--(to determine if answer is in Top 3)
select
   pall.parentid as [qID], 
   pall.id as [aID], 
   pall.owneruserid, 
   rank() over (
     partition by pall.parentid
     order by pall.score desc
   ) as [Rank]
into #voteRanks
from posts pmine left outer join posts pall
on pmine.parentid = pall.parentid
where pmine.owneruserid=##UserID##
  and pall.parentid is not null
  and pmine.posttypeid=2

declare @dt5 varchar(25) = convert(varchar,getdate(),21)
 
select a3.*,   -----------------------------> populate #4  
  vr.rank,              --rank among answers for question
  --in the top 3 answers for question ?
  case when vr.rank <= 3 then 1 else 0 end as [4of4:IsInTop3]
into #answers4
from
  #answers3 a3 left outer join #voteRanks vr
  on a3.[aID] = vr.[aID]
order by
  a3.[aID], vr.[aID]

declare @dt6 varchar(25) = convert(varchar,getdate(),21)

select 
  *,
  case when 
    [1of4:IsAccepted]+
    [2of4:ScoreMin5]+
    [3of4:20pVotes]+
    [4of4:IsInTop3] > 0 then [viewcount] else 0 end as [People],
  case when
    [1of4:IsAccepted]+
    [2of4:ScoreMin5]+
    [3of4:20pVotes]+
    [4of4:IsInTop3] = 0 then 1 else 0 end as [No1234]
into #answers5
from #answers4  -----------------------------> (now has 1,2,3,4)

declare @dt7 varchar(25) = convert(varchar,getdate(),21)

-----------------------------> (added questions)
insert into #answers5 (creationdate,aID,parentid,[Link],score,viewcount,people,[1of4:IsAccepted],[2of4:ScoreMin5],[3of4:20pVotes],[4of4:IsInTop3],[Votes:all],[Votes:me],[Rank],[No1234])
select 
  [creationdate],
  0 as [aId],
  id as [parentid],
  'site://q/'+cast(id as varchar)+'|Q:'+title as [Link],
  [score], 
  [viewcount],
  viewcount as [People],
  0 as [1of4:IsAccepted], 0 as [2of4:ScoreMin5], 0 as [3of4:20pVotes],
  0 as [4of4:IsInTop3], 0 as [Votes:all], 0 as [Votes:me], 0 as [Rank], 0 as [No1234]
from posts
where 
  --deletiondate is null and --already excluded from source table
  posttypeid=1
  and owneruserid=##UserID##


declare @dt8 varchar(25) = convert(varchar,getdate(),21)

---TAB#1----
declare @PASS nvarchar(1) = nchar(10004) --post eligible because of this
declare @FAIL nvarchar(1) = nchar(10006) --post not eligile because of this
declare @YES nvarchar(1) = nchar(10003)  --this item NA
declare @NA nvarchar(1) = nchar(8208)    --this item NA

  select ------> FINALIZE LIST
    format(creationdate,'yyyy-MM-dd') as [Post Date], 
    [Link],
    People as [IMPACT],
    case when substring(Link,8,1)='q' then @PASS else @NA end as [Question], 
    case when substring(Link,8,1)='q' then @NA else @YES end as [Score>0],
    case when [No1234]=1 then @FAIL else case when [1of4:IsAccepted]=1 then @YES else @NA end end as [Accptd], 
    case when [No1234]=1 then @FAIL else case when [2of4:ScoreMin5]=1 then @YES else @NA end end as [Score5+], 
    case when [No1234]=1 then @FAIL else case when [3of4:20pVotes]=1 then @YES else @NA end end as [20%Votes], 
    case when [No1234]=1 then @FAIL else case when [4of4:IsInTop3]=1 then @YES else @NA end end as [Top3]
  from #answers5
UNION
  select ------> (list EXCLUDED answers - these all failed)
    format(a.creationdate,'yyyy-MM-dd') as [Post Date], 
    'site://a/'+cast(a.id as varchar)+'|A:'+q.title as [Link],
    0 as IMPACT,
    @NA as Question,
    @FAIL as [Score>0],
    @NA as [Accptd], @NA as [Score5+], @NA as [20%Votes], @NA as [Top3]
from posts a join posts q on q.id = a.parentid
where
  a.owneruserid = ##UserID## --my posts
  and a.posttypeid = 2       --answers only
  and a.score <= 0           --NOT positive score
  
order by format(creationdate,'yyyy-MM-dd') desc

declare @dt9 varchar(25) = convert(varchar,getdate(),21)

  ---TAB#2---  Total
  select 
  format(sum(People),'#,##0') as [IMPACT:People Reached], 
    format(datediff(hh,@dtUpdated,getdate())/24.0,'0.0')+' days ago' as [Data Updated] 
  from #answers5
            
  ---TAB#3---  Total by Month
  select 
    format(creationdate,'yyyy-MM') as [Month],
    format(sum(People ),'#,##0') as [IMPACT:People Reached]
  from #answers5
  group by format(creationdate,'yyyy-MM')
  order by format(creationdate,'yyyy-MM')

declare @dtEnd varchar(25) = convert(varchar,getdate(),21)

print @dt1
print '1-2: '+format(datediff(ms,@dt1,@dt2),'0ms')
print '2-3: '+format(datediff(ms,@dt2,@dt3),'0ms')
print '3-4: '+format(datediff(ms,@dt3,@dt4),'0ms')
print '4-5: '+format(datediff(ms,@dt4,@dt5),'0ms')
print '5-6: '+format(datediff(ms,@dt5,@dt6),'0ms')
print '6-7: '+format(datediff(ms,@dt6,@dt7),'0ms')
print '7-8: '+format(datediff(ms,@dt7,@dt8),'0ms')
print '8-9: '+format(datediff(ms,@dt8,@dt9),'0ms')
print '9-E: '+format(datediff(ms,@dt9,@dtEnd),'0ms')
print 'ALL: '+format(datediff(ms,@dt1,@dtEnd),'0ms')
print @dtEnd

--query:\\863257
--UserID:Enter a User ID:"Enter a numeric user ID to query 'People Reached'."

Enter Parameters

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