This new set of reports are "Most Useful Articles" and "Least Useful Articles", providing information on the feedback that the end-users send on the OKA articles they had accessed and viewed.

 

The query running in the back end for these reports is the one below:

 

SELECT SQL_CALC_FOUND_ROWS okcs_answers.summary col1, SUM(okcs_answer_stats.clickthru_cnt) + SUM(okcs_answer_stats.content_view_cnt) col2, SUM(okcs_answer_stats.caselink_cnt) col3, ROUND(CASE WHEN SUM(okcs_answer_stats.clickthru_cnt) > 0 THEN (SUM(okcs_answer_stats.clickthru_cnt) / SUM(okcs_answer_stats.answer_cnt)) ELSE 0 END, 9) col4, ROUND((SUM(kf_answer_stats.rated_1) + SUM(kf_answer_stats.rated_2) + SUM(kf_answer_stats.rated_3) + SUM(kf_answer_stats.rated_4) + SUM(kf_answer_stats.rated_5)) / COUNT(kf_answer_stats.a_id), 9) col5, ROUND((UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - UNIX_TIMESTAMP(okcs_answers.publish_on)) / 86400, 9) col6 FROM okcs_answer_stats okcs_answer_stats JOIN okcs_answers okcs_answers ON okcs_answer_stats.a_id = okcs_answers.a_id LEFT JOIN kf_answer_stats kf_answer_stats ON ((kf_answer_stats.window = 1 AND kf_answer_stats.stat_date = okcs_answer_stats.stat_date) AND okcs_answers.a_id = kf_answer_stats.a_id) WHERE (okcs_answer_stats.app IN (1) AND (okcs_answer_stats.window = 1 AND (okcs_answer_stats.interface_id IN (1) AND okcs_answer_stats.stat_date >= '2017-08-01 00:00:00' AND okcs_answer_stats.stat_date < '2018-03-31 00:00:00'))) GROUP BY col1, col6 ORDER BY col2 DESC LIMIT 10000