0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

DECLARE @MyStartDate DATETIME = ##MyStartDate:string?01/01/2015##
--MyStartDate: Start Date
DECLARE @MyEndDate   DATETIME = ##MyEndDate:string?01/01/2022##
--MyEndDate: End Date

/*
(1) Get Questions and Answers, Votes and Review Tasks Daily Time Series from
    the individual tables. Cannot find Page Views data.
(2) Aggregate into a single Traffic table and calcualte Moving Average by Day of Week
(3) Plot MA by day
*/

;WITH q_and_a AS (
  SELECT
      CAST(p.CreationDate AS DATE) AS cal_date 
     ,COUNT(DISTINCT (CASE WHEN pt.name = 'Question' THEN p.Id END)) AS "Questions"
     ,COUNT(DISTINCT (CASE WHEN pt.name = 'Answer' THEN p.Id END)) AS "Answers"
  FROM dbo.PostsWithDeleted AS p
  INNER JOIN dbo.PostTypes AS pt
    ON p.PostTypeId = pt.Id
  WHERE 1=1
    AND pt.name IN('Question','Answer')
    AND CAST(p.CreationDate AS DATE) BETWEEN @MyStartDate AND @MyEndDate
  GROUP BY CAST(p.CreationDate AS DATE)
),

votes AS (
 SELECT
       CAST(CreationDate AS DATE) AS cal_date
      ,COUNT(*) AS "Votes"
  FROM dbo.votes
  WHERE 1=1
    AND CAST(CreationDate AS DATE) BETWEEN @MyStartDate AND @MyEndDate
  GROUP BY CAST(CreationDate AS DATE)
),

review_tasks AS (
 SELECT
       CAST(CreationDate AS DATE) AS cal_date
      ,COUNT(*) AS "Review Tasks"
  FROM dbo.ReviewTasks
  WHERE 1=1
    AND CAST(CreationDate AS DATE) BETWEEN @MyStartDate AND @MyEndDate
  GROUP BY CAST(CreationDate AS DATE)
),

agg_traffic_timeseries AS (
  SELECT
     qna.cal_date
    ,qna."Answers"
    ,qna."Questions"
    ,v."Votes"
    ,r."Review Tasks"
    ,DATENAME(dw, qna.cal_date) AS dow
    ,DATENAME(month, qna.cal_date) AS month
    ,DATENAME(quarter, qna.cal_date) AS quarter
    ,DATENAME(day, qna.cal_date) AS day
    ,DATENAME(dayofyear,qna.cal_date) AS dayofyear
    ,DATENAME(year,qna.cal_date) AS year
    ,CAST((qna."Answers" + qna."Questions" + v."Votes" + r."Review Tasks") AS FLOAT) AS traffic
    ,CAST(AVG(LOG(qna."Answers" + qna."Questions" + v."Votes" + r."Review Tasks")) 
          OVER (PARTITION BY DATENAME(dw, qna.cal_date) -- Over DOW
          ORDER BY qna.cal_date ASC ROWS BETWEEN 175 PRECEDING AND 175 FOLLOWING
          ) AS FLOAT ) AS ma175_ln_traffic    
  FROM q_and_a AS qna
  INNER JOIN votes AS v
    ON qna.cal_date = v.cal_date
  INNER JOIN review_tasks AS r
    ON qna.cal_date = r.cal_date
)


/* Q2, Part I */ 
SELECT
   cal_date -- leave this unformatted so the graph works
  ,dow
  ,ma175_ln_traffic 
FROM agg_traffic_timeseries
ORDER BY cal_date ASC

fork query

edited mar 18 22

created mar 16 22 Dimitriy.V..Masterov