Data Explorer Reputation Analysis

Question

I am trying to gather information on

1. the number of questions posted per week for a user with fewer than 25 reputation points from the math.stackexchange site

2. the weekly average number of up-votes per question for a user with fewer than 25 reputation points.

My approach for the first part is unfortunately not working well: https://data.stackexchange.com/math/query/edit/472455

For the second part, I think I may have been able to accomplish what I have wanted, but I cannot be sure. I am unable to show the date "week + year" of each result. https://data.stackexchange.com/math/query/edit/471895#resultSets

Also, the first part returns 306 rows while the second one returns 304 rows so I suspect that the dates are mismatched.

How would I fix these problems? Also, I was unable to find a "manual" of some sort for the commands.

Answer 1

The following query calculates the reputation and votes totals per week for users with less then 25 reputation:

-- CTE with year, week and start of week dates
with yw as (
select distinct 
       year(creationdate) as y 
     , datepart(week, creationdate) as w
     -- http://stackoverflow.com/a/3936891/578411
     , cast(
         DATEADD(
              day, 
              1 - DATEPART(dw, creationdate), 
              creationdate) 
          as date
        ) as sdow
from posts
)

-- grouping over the union
select sdow
     , sum(questions) as [questions]
     , sum(votes) as [up votes]
from 
(
  -- questions from users < 25 rep
  select sdow
       , count(*) as [questions]
       , 0 as [votes]
  from posts p
  inner join users u on u.id = p.owneruserid
  right outer join yw 
     on yw.y = year(p.creationdate) 
    and yw.w = datepart(week, p.creationdate) 
  where p.posttypeid = 1 -- Q 
  and u.reputation < 25 -- rep
  group by sdow
  union 
  -- up votes on questions from users < 25 rep
  select sdow
       , 0
       , count(*)
  from posts p
  inner join users u on u.id = p.owneruserid
  inner join votes v
     on v.postid = p.id
  right outer join yw 
     on yw.y = year(v.creationdate) 
    and yw.w = datepart(week, v.creationdate) 
  where p.posttypeid = 1 -- Q 
  and u.reputation < 25 -- rep
  and v.votetypeid = 2 -- upmod
  group by sdow
) as   total
group by sdow
order by sdow

At the day of posting the resulting graph looks like this:

Find more about the Database schema here. To learn more about the SQL dialect used, make sure to study the Transact-SQL Reference as SEDE runs on top of an Microsoft SQL Server database instance.