Tag Activity By Month - questions, answers, comments, votes

1

Please login or register to vote for this query.

(click on this box to dismiss)

Summarize activity for a given list of tags by month for the previous X number of days. Activity includes questions, answers, comments, votes and a total of all of them.

Physics

Q&A for active researchers, academics and students of physics

-- List activity by tags

-- tags: List of tags "Comma separated list of tags to query"
-- startDate: Start date "Will use relative number of days as relative stard date if not provided"
-- endDate: End date "Defaults to today's date if not provided"
-- numDays: Number of days "Relative to today's date - only used if start date isn't specified"

declare @tags varchar(max) = ##tags:string?applepay,google-pay,paypal,stripe-payments##;
declare @startDateInput varchar(50) = ##startDate:string?-##;
declare @endDateInput varchar(50) = ##endDate:string?-##;
declare @startDate date;
declare @endDate date;
declare @numDays int = ##numDays?365##;

if @startDateInput = '-'
  begin
    set @startDate = convert(date, GetDate() - @numDays);
  end;
else
  begin
    set @startDate = convert(date, @startDateInput);
  end;

if @endDateInput = '-'
  begin
    set @endDate = convert(date, GetDate());
  end;
else
  begin
    set @endDate = convert(date, @endDateInput);
  end;


with QuestionAnswers as (
  select
    p.Id PostId,
    t.Id TagId,
    t.TagName,
    convert(date, p.CreationDate) CreationDate,
    substring(convert(varchar, p.CreationDate, 23), 1, 7) CreationMonth,
    1 IsQuestion,
    0 IsAnswer
  from string_split(@tags, ',') s
  inner join Tags t on s.value = t.TagName
  inner join PostTags pt on t.Id = pt.TagId
  inner join Posts p on pt.PostId = p.Id
  union all
  select
    p.Id PostId,
    t.Id TagId,
    t.TagName,
    convert(date, p.CreationDate) CreationDate,
    substring(convert(varchar, p.CreationDate, 23), 1, 7) CreationMonth,
    0 IsQuestion,
    1 IsAnswer
  from string_split(@tags, ',') s
  inner join Tags t on s.value = t.TagName
  inner join PostTags pt on t.Id = pt.TagId
  inner join Posts p on pt.PostId = p.ParentId
),
TagComments as (
  select
    qa.TagId,
    qa.TagName,
    convert(date, c.CreationDate) CreationDate,
    substring(convert(varchar, c.CreationDate, 23), 1, 7) CreationMonth,
    count(*) CommentCount
  from QuestionAnswers qa
  inner join Comments c on qa.PostId = c.PostId
  where c.CreationDate >= @startDate
  and c.CreationDate < @endDate
  group by qa.TagId, qa.TagName, convert(date, c.CreationDate), substring(convert(varchar, c.CreationDate, 23), 1, 7)
),
TagVotes as (
  select
    qa.TagId,
    qa.TagName,
    convert(date, v.CreationDate) CreationDate,
    substring(convert(varchar, v.CreationDate, 23), 1, 7) CreationMonth,
    count(*) VoteCount
  from QuestionAnswers qa
  inner join Votes v on qa.PostId = v.PostId
  where v.CreationDate >= @startDate
  and v.CreationDate < @endDate
  group by qa.TagId, qa.TagName, convert(date, v.CreationDate), substring(convert(varchar, v.CreationDate, 23), 1, 7)
),
Activity as (
  select
    qa.TagName,
    qa.CreationDate,
    qa.CreationMonth,
    sum(qa.IsQuestion) Questions,
    sum(qa.IsAnswer) Answers,
    0 Comments,
    0 Votes
  from QuestionAnswers qa
  where qa.CreationDate >= @startDate
  and qa.CreationDate < @endDate
  group by qa.TagName, qa.CreationDate, qa.CreationMonth
  union all
  select
    c.TagName,
    c.CreationDate,
    c.CreationMonth,
    0 Questions,
    0 Answers,
    c.CommentCount Comments,
    0 Votes
  from TagComments c
  union all
  select
    v.TagName,
    v.CreationDate,
    v.CreationMonth,
    0 Questions,
    0 Answers,
    0 Comments,
    v.VoteCount Votes
  from TagVotes v
),
ActivitySummary as (
  select
    substring(convert(varchar, a.CreationDate, 23), 1, 10) CreationDate,
    a.CreationMonth,
    a.TagName,
    sum(a.Questions) Questions,
    sum(a.Answers) Answers,
    sum(a.Comments) Comments,
    sum(a.Votes) Votes
  from Activity a
  group by a.TagName, a.CreationDate, a.CreationMonth
)
select
  s.*,
  s.Questions + s.Answers + s.Comments + s.Votes TotalActivity
from ActivitySummary s
order by s.CreationDate, s.TagName
;

Enter Parameters

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