Notes

select A.date, A.problems, B.SSAT, C.avg_rating, C.4_5_star_pct, C.1_2_star_pct, D.avg_chat_time, E.audits, E.inaccuracy_pct, E.perfect_accuracy_pct, F.experts_online, F.experienced_experts_pct, F.novice_experts_pct, F.pct_explanations_from_experienced, F.pct_explanations_from_novice from

(-- problem A
select date(created - interval 5 hour) as date, count(id) as problems from problem
where created - interval 5 hour > '2018-02-01'
group by date ) A

left join

(-- SSAT B
select created as date, (count(*)-sum(if(A.state like '%dead%',1,0))-sum(if(A.state like '%claimed_unanswered%',1,0))-sum(if(A.score between 1 and 2 and A.state not like '%claimed_unanswered%',1,0)))/COUNT(*) as SSAT

from (select A.*, case when B.score is null then 0 else B.score end as score from

(select A.qid, A.created,
case when A.vba = 1 then 'VBA' else 'NON_VBA' end as topic,
case when B.failed_reason like 'majority flaggs' then 'flagged'
when B.failed_reason is null and B.route_count > 1 and A.explainer_id is null then 'dead_skipped'
when B.failed_reason is null and B.route_count = 1 then 'dead_no_capacity'
when A.explainer_id is not null and A.final_answer_provided = 0 then 'claimed_unanswered'
else 'claimed_answer' end as state from

(select id as qid, date(created - interval 5 hour) as created, case when topic_id = -4 then 1 else 0 end as vba, explainer_id, case when final_solution_created is null then 0 else 1 end as final_answer_provided from problem where created - interval 5 hour > '2018-02-01') as A join (select problem_id as qid, state, failed_reason, route_count from problem_state ) as B ON A.qid = B.qid) as A LEFT OUTER JOIN (select problem_id as qid, score from explainer_rating) as B ON A.qid = B.qid) as A where A.state not like 'flagged'

group by created) B
on A.date = B.date

left join

(-- rating C
select date(A.created - interval 5 hour) as date, sum(score)/count(score) as avg_rating, sum(if(score >= 4,1,0))/count(score) as 4_5_star_pct, sum(if(score < 3,1,0))/count(score) as 1_2_star_pct, count(score) as rated_sessions
from problem A left join explainer_rating B on A.id = B.problem_id
where A.created - interval 5 hour > '2018-02-01'
group by date ) C
on A.date = C.date

left join



(-- chat time D
select date, round(sum(chat_time)/count(qid),1) as avg_chat_time from

(
select A.*, (C.chatend-B.granted)/60 as chat_time from

(select id as qid, explainer_id, date(created - interval 5 hour) as date from problem where explainer_id is not null and created - interval 5 hour > '2018-02-01') as A -- Table 3.1

join

(select problem_id as qid, unix_timestamp(updated) as granted from problem_log where action like "granted" ) as B -- Table 3.2

ON A.qid = B.qid

join

(select problem_id as qid, unix_timestamp(updated) as chatend from problem_log where action like "chatend" ) as C -- Table 3.3

ON A.qid = C.qid) as A
group by date) D
on A.date = D.date

left join

(-- audits E
select date, count(audit_points) as audits, sum(if(audit_points = 0,1,0))/count(audit_points) as inaccuracy_pct, sum(if(audit_points = 3,1,0))/count(audit_points) as perfect_accuracy_pct, sum(audit_points)/count(audit_points) as avg_accuracy from

(
select A.id, A.explainer_id, A.date, B.audit_points, B.160_yes, B.161_yes, B.163_yes from

(select id, explainer_id, date(created - interval 5 hour) as date from problem
where created - interval 5 hour > '2018-02-01') as A -- TABLE 4.1


left join

(select problem_id, audit_points, 160_yes, 161_yes, 163_yes from
(select problem_id, id as audit_id from audit
where state = 2) A -- Table 4.2.1

join

(select audit_id, sum(yes) as audit_points, sum(if(original_id = 160 and Yes = 1,1,0)) as 160_yes, sum(if(original_id = 161 and Yes = 1,1,0)) as 161_yes, sum(if(original_id = 163 and Yes = 1,1,0)) as 163_yes

from
(select audit_id, question_id, case when question_response_value > 0 then 1 else 0 end as Yes from audit_result) A -- table 4.2.2.1

join

(select id, original_id from audit_question where original_id in (160,161,163)) B -- table 4.2.2.2
on A.question_id = B.id
group by audit_id) B -- Table 4.2.2

on A.audit_id = B.audit_id) B -- TABLE 4.2
on A.id = B.problem_id
) as A
group by date) E
on A.date = E.date

left join

(-- % of novice/experienced experts and their explanations F
select date, count(explainer_id) as experts_online, sum(if(explanations > 20,1,0)) as experienced_experts, sum(if(explanations > 20,0,1)) novice_experts, sum(if(explanations > 20,1,0))/count(explainer_id) as experienced_experts_pct, sum(if(explanations > 20,0,1))/count(explainer_id) as novice_experts_pct, sum(explanations_this_day) as explanations, sum(if(explanations > 20,explanations_this_day,0)) as explanations_from_experienced, sum(if(explanations > 20,0,explanations_this_day)) as explanations_from_novice,
sum(if(explanations > 20,explanations_this_day,0))/sum(explanations_this_day) as pct_explanations_from_experienced, sum(if(explanations > 20,0,explanations_this_day))/sum(explanations_this_day) as pct_explanations_from_novice from

(select date, explainer_id, count(explainer_id) as explanations, explanations_this_day from

(select A.date, A.explainer_id, explanations_this_day, B.created from

(select date(A.created - interval 5 hour) as date, A.explainer_id, count(explainer_id) as explanations_this_day
from problem A
where created - interval 5 hour > '2018-02-01' and explainer_id is not null
group by date, explainer_id) A

left join problem B on A.explainer_id = B.explainer_id
having date(B.created) < A.date
) A
group by date,explainer_id) A

group by date) F
on A.date = F.date