0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for experienced flyers, pilots, and builders of drones and model aircraft

-- Enter Query Title
-- Enter Query Description
WITH post_counts (yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), COUNT(*)
   FROM Posts
   GROUP BY Year(CreationDate), Month(CreationDate)
),
post_accepted (yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), COUNT(*)
   FROM Posts
   WHERE AcceptedAnswerId IS NOT NULL
   GROUP BY Year(CreationDate), Month(CreationDate)
),
answer_counts (yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), COUNT(*)
   FROM Posts
   WHERE AnswerCount >= 1
   GROUP BY Year(CreationDate), Month(CreationDate)
),
post_views (yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), SUM(ViewCount)
   FROM Posts
   GROUP BY Year(CreationDate), Month(CreationDate)
),
answers_total (yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), SUM(AnswerCount)
   FROM Posts
   GROUP BY Year(CreationDate), Month(CreationDate)
),
answers_avg (yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), CONVERT(DECIMAL(14,5), AVG(AnswerCount))
   FROM Posts
   GROUP BY Year(CreationDate), Month(CreationDate)
)
SELECT pc.yr, pc.mth, pc.cnt AS total, pa.cnt AS accepted,
   ac.cnt AS answered, CONVERT(DECIMAL(14,5),pa.cnt)/pc.cnt * 100 AS accept_rate,
   CONVERT(DECIMAL(14,5),ac.cnt)/pc.cnt * 100 AS answer_rate,
   pv.cnt AS views, at.cnt as total_answers, aa.cnt AS avg_answers
FROM post_counts pc
   JOIN post_accepted pa ON pa.yr = pc.yr AND pa.mth = pc.mth
   JOIN answer_counts ac ON ac.yr = pc.yr AND ac.mth = pc.mth
   JOIN post_views pv ON pv.yr = pc.yr AND pv.mth = pc.mth
   JOIN answers_total at ON at.yr = pc.yr AND at.mth = pc.mth
   JOIN answers_avg aa ON aa.yr = pc.yr AND aa.mth = pc.mth
ORDER BY pc.yr DESC, pc.mth DESC

fork query

created jan 20 11 anonymous