2

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.

Q&A about the site for power users of web applications

/*  "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 -----------------------------> Make a temp tables of relevant #Votes 
id, postid, votetypeid 
into #votes from votes 
where postid in (select id from #eligibleAnswers)
  and votetypeid in (2,3)

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],
  (  select 
          count(vv.id) as [Votes:all]
        from
          posts qq left outer join posts aa
          on qq.id = aa.parentid
          left outer join #votes vv
          on aa.id = vv.postid
        where 
          aa.parentid = q.id
          and vv.votetypeid in (2,3)
          --and aa.id in (select id from #eligibleAnswers)
     ) as [Votes:all], --total +&- vote count
  (  select count(vw.id) as [Votes:me]
        from #votes vw
        where vw.postid = a.id and vw.votetypeid in (2,3)
     ) as [Votes:me]  --votes +&- on my answer
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)
  [creationdate],    --answer date
  [aID],             --answer ID
  [parentid],        --question ID
  [Link],       --link to answer
  [viewcount],       --question #views
  [1of4:IsAccepted], --my answer = accepted ?
  [score],           --answer score
  [2of4:ScoreMin5],  --score >5 ?
  [Votes:all],       --total answer vote count
  [Votes:me],        --my answer vote count
  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)

select   --create list of all answers per question (to determine if answer is in Top 3)
   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
--order by pmine.parentid,pmine.id

declare @dt5 varchar(25) = convert(varchar,getdate(),21)
 
select    -----------------------------> populate #4
  a3.[creationdate],    --answer date
  a3.[aID],             --answer ID
  a3.[parentid],        --question ID
  a3.[Link],            --link to answer
  a3.[viewcount],       --question #views
  a3.[1of4:IsAccepted], --my answer = accepted ?
  a3.[score],           --answer score
  a3.[2of4:ScoreMin5],  --score >5 ?
  a3.[Votes:all],       --total answer vote count
  a3.[Votes:me],        --my answer vote count
  a3.[3of4:20pVotes],   --min 20% of total vote count ?    
  vr.rank,              --rank among answers for question
  case when vr.rank <= 3 then 1 else 0 end as [4of4:IsInTop3] --in the top 3 answers for question ?
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)

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##  -----------------------------> (added questions)


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'."

fork query

edited aug 5 18

created jun 15 18 ashleedawg