traffic

0

Please login or register to vote for this query.

(click on this box to dismiss)

Skeptics

Q&A for scientific skepticism

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

/* The query is the same as in Parts I and II but I could not figure out how to make the temporary tables 
   permanent to make the second part II work
   
(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 (but not by Day of Week)

*/

;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
    ,CAST((qna."Answers" + qna."Questions" + v."Votes" + r."Review Tasks") AS FLOAT) AS 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
)

SELECT
    cal_date
   ,traffic AS "Traffic"
   ,AVG(traffic) OVER(ORDER BY cal_date ASC ROWS BETWEEN 175 PRECEDING AND 175 FOLLOWING) AS "75 Day MA Traffic"
FROM agg_traffic_timeseries

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results