0

Please login or register to vote for this query.

(click on this box to dismiss)

A break down of view trends per MM/YY

Q&A for scientists using computers to solve scientific problems

-- View trends per MM/YY
-- A break down of view trends per MM/YY

WITH total_questions(yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), COUNT(*)
   FROM Posts
   WHERE PostTypeId = 1
   GROUP BY Year(CreationDate), Month(CreationDate)
),
total_views(yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), ROUND(AVG(CONVERT(DECIMAL(14,5),ViewCount)), 2)
   FROM Posts
   WHERE PostTypeId = 1
   GROUP BY Year(CreationDate), Month(CreationDate)
)
SELECT tq.yr AS Year, tq.mth AS Month, tq.cnt AS Questions, ac.cnt AS AvergeNoViews    
FROM total_questions tq
    JOIN total_views ac ON ac.yr = tq.yr AND ac.mth = tq.mth
ORDER BY tq.yr DESC, tq.mth DESC

fork query

created jan 31 11 xiaohouzi79