Support/Interesting queries

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%';