Forum status

gistfile1.sql

SELECT 
all_questions.created_date, 
all_questions.threads as all_threads,
no_replies.threads as not_replied,
needs_attenion.threads as needing_attention, 
responded.threads as responded_threads,
solved.threads as solved_threads,
locked.threads as locked_threads,
round((no_replies.threads / all_questions.threads*100),3) as no_replies_percentage,
round((needs_attenion.threads / all_questions.threads*100),3) as ball_dropped_percentage,
round((responded.threads / all_questions.threads*100),3) as responded_percentage,
round((solved.threads / all_questions.threads*100),3) as solved_percentage,
round((locked.threads / all_questions.threads*100),3) as locked_percentage

FROM 
(
SELECT DATE(qq.`created`) as created_date, count(*) as threads
FROM `questions_question` qq
WHERE qq.`created` BETWEEN '2013-01-01 0' AND DATE(CURDATE())
GROUP BY DATE(qq.`created`)
) as all_questions

LEFT JOIN
(
SELECT DATE(qq.`created`) as created_date, count(*) as threads
FROM `questions_question` qq
JOIN `questions_answer` qa ON qq.`last_answer_id` = qa.`id`
WHERE qq.`created` BETWEEN '2013-01-01 0' AND DATE(CURDATE())
AND qq.`creator_id` = qa.`creator_id`
AND qq.`is_locked`=0
AND qq.`solution_id` IS NULL
GROUP BY DATE(qq.`created`)
) as needs_attenion
ON all_questions.created_date = needs_attenion.created_date

LEFT JOIN 
(
SELECT DATE(qq.`created`) as created_date, count(*) as threads
FROM `questions_question` qq
JOIN `questions_answer` qa ON qq.`last_answer_id` = qa.`id`
WHERE qq.`created` BETWEEN '2013-01-01 0' AND DATE(CURDATE())
AND qq.`creator_id` != qa.`creator_id`
AND qq.`solution_id` IS NULL
AND qq.`is_locked`=0
GROUP BY DATE(qq.`created`)
) as responded
ON all_questions.created_date = responded.created_date

LEFT JOIN 
(
SELECT DATE(qq.`created`) as created_date, count(*) as threads
FROM `questions_question` qq
WHERE qq.`created` BETWEEN '2013-01-01 0' AND DATE(CURDATE())
AND qq.`solution_id` IS NOT NULL
GROUP BY DATE(qq.`created`)
) as solved
ON all_questions.created_date = solved.created_date

LEFT JOIN 
(
SELECT DATE(qq.`created`) as created_date, count(*) as threads
FROM `questions_question` qq
WHERE qq.`created` BETWEEN '2013-01-01 0' AND DATE(CURDATE())
AND qq.`last_answer_id` IS NULL
AND qq.`is_locked`=0
GROUP BY DATE(qq.`created`)
) as no_replies
ON all_questions.created_date = no_replies.created_date

LEFT JOIN 
(
SELECT DATE(qq.`created`) as created_date, count(*) as threads
FROM `questions_question` qq
WHERE qq.`created` BETWEEN '2013-01-01 0' AND DATE(CURDATE())
AND
qq.`solution_id` IS NULL 
AND qq.`is_locked`=1
GROUP BY DATE(qq.`created`)
) as locked
ON all_questions.created_date = locked.created_date