Support/Interesting queries
From MozillaWiki
< Support
Contents
Threads per day
The number of threads (questions) posted each day.
SELECT DATE(FROM_UNIXTIME(commentDate)) d, COUNT(*) c FROM tiki_comments WHERE parentId = 0 AND objectType = 'forum' GROUP BY d ORDER BY d;
Replies per day
The number of posts (questions and replies) posted each day.
SELECT DATE(FROM_UNIXTIME(commentDate)) d, COUNT(*) c FROM tiki_comments WHERE objectType = 'forum' GROUP BY d ORDER BY d;
Instances of a pattern
Finds the most common strings in forum posts that match the pattern given.
echo "SELECT data FROM tiki_comments WHERE objectType = 'forum' AND data LIKE '%.dll%'" > test.sql mysql -u root sumo < ./test.sql > output.txt grep -o "\S*\.dll" output.txt > output2.txt sort output2.txt | uniq -c | sort -nr > report.txt
Instances of a word being used per day
SELECT DATE(FROM_UNIXTIME(commentDate)) d, COUNT(*) c FROM tiki_comments WHERE parentId = 0 AND objectType = 'forum' AND data LIKE '%Vundo%' GROUP BY d ORDER BY d;
Poll results
SELECT * FROM tiki_poll_options tpo INNER JOIN tiki_poll_votes tpv on tpo.optionId = tpv.optionId INNER JOIN tiki_polls tp on tpo.pollId = tp.pollId WHERE tp.title LIKE '%smart%';
Poll result summary
Weekly:
SELECT YEARWEEK(voteDate, 3), position, SUM(voteCount) votes FROM tiki_poll_votes tpv INNER JOIN tiki_poll_options tpo ON tpo.optionId = tpv.optionId GROUP BY YEARWEEK(voteDate, 3), position ORDER BY voteDate, position;
- 0 - Solved problem
- 1 - Did not solve problem
- 10 - Easy to understand
- 11 - Not easy to understand
Polls with the most "Helpful" votes per week
See YEARWEEK.
SELECT tp.title, SUM(voteCount) votes FROM tiki_poll_votes tpv INNER JOIN tiki_poll_options tpo ON tpo.optionId = tpv.optionId INNER JOIN tiki_polls tp ON tp.pollId = tpo.pollId WHERE YEARWEEK(voteDate, 3) = '200812' AND position = 0 GROUP BY tp.title ORDER BY voteDate, votes;
Thread statuses per week
Open/locked/solved threads per week. Week year then number of week (hopefully matches Ubuntu's calendar)
SELECT YEARWEEK(FROM_UNIXTIME(commentDate),3) d, type, COUNT(*) c FROM tiki_comments WHERE objectType = 'forum' AND parentId = 0 GROUP BY d, type ORDER BY d;