2

In other words, how do I change this query to get a cumulative result? i.e. instead of seeing a graph of questions/day vs. time, I want to see (all the questions asked until today) vs. time.

For example, if in Day1 10 questions were asked an in Day2 another 10, the output should be 10 for Day1 and 20 for Day2.

4
  • 1
    It is kind of hard to understand your question here Feb 11, 2015 at 1:23
  • @PythonMaster, what I mean is: instead of seeing a graph of questions/day vs. time, I want to see (all the questions asked until today) vs. time.
    – Sparkler
    Feb 11, 2015 at 1:48
  • In that case, please improve your question to clarify it and prevent more downvotes unless you want the badge Peer Pressure, which is highly unlikely. :-) Feb 11, 2015 at 1:49
  • What do you mean 'vs time'? You say you want to see all questions asked until today - surely that is your time criteria already?
    – JonW
    Feb 11, 2015 at 11:33

1 Answer 1

4

Based on the logic of the query you linked to I assume this query answers your question, if you don't care about deleted posts1

Notice that I don't use any temporary tables to store intermediate results (something the other query does). Instead I used the OVER clause offered by the T-Sql if SqlServer to calculate the running total.

  DECLARE @Increments int = ##Increments?30##
-- Increments: Number of days per group "Default is 30"
DECLARE @Results int = ##Results?12##
-- Results: Number of groups to pull "Default is 12"

-- based on increments and results determine the 
-- start date of posts by substracting from max creationdate
declare @lowest datetime
select @lowest =  dateadd(d, - (@Increments * @Results), max(creationdate)) 
                 from posts;

declare @toZeroQ int
declare @toZeroA int
select @toZeroQ = sum(case when posttypeid=1 then 1 else 0 end)
     , @toZeroA = sum(case when posttypeid=2 then 1 else 0 end)
from posts
where datediff(d,  @lowest, creationdate) / @Increments  = 0
and creationdate >= @lowest
and posttypeid in (1,2)

select dateadd(d, (days+1) * @increments, @lowest) as [date] -- recreate a date
     , sum(qcnt) over ( order by days) - @toZeroQ as Questions -- sum of question count
     , sum(acnt) over ( order by days) - @toZeroA as Answers -- sum of answercount
from (
    -- calc the sum for questions and answer per number of days per group
    select
           datediff(d,  @lowest, creationdate) / @Increments as days
         , sum(case when posttypeid=1 then 1 else 0 end) as qcnt
         , sum(case when posttypeid=2 then 1 else 0 end) as acnt
    from posts
    where creationdate between @lowest 
                           and dateadd(d, @Increments * @Results -1 ,@lowest)
    and posttypeid in (1,2)
    group by datediff(d,  @lowest, creationdate) / @Increments
    ) as counts

The graph produced by this query shows the following result at the time of posting:

Cumulative count of questions and answers

1. Notice that deleted posts are not available in SEDE so technically more questions (and answers) are posted but those are only available for SE Employees.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .