Forum metrics — monthly metrics generating script

!acidforums
 [hr]


 [b][u]1. Users by usergroup:[/u][/b]
% SELECT
%   g.title,
%   COUNT(*)
% FROM
%   users u INNER JOIN usergroups g ON u.usergroup = g.gid
% GROUP BY
%   g.gid
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   1



 [b][u]2. New users this month by usergroup:[/u][/b]
% SELECT
%   g.title,
%   COUNT(*)
% FROM
%   users u INNER JOIN usergroups g ON u.usergroup = g.gid
% WHERE
%   YEAR(FROM_UNIXTIME(u.regdate)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
%   MONTH(FROM_UNIXTIME(u.regdate)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
% GROUP BY
%   g.gid
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   1



 [b][u]3. Usage of forum themes:[/u][/b]
% SELECT
%   t.name,
%   COUNT(*)
% FROM
%   themes t INNER JOIN (
%     SELECT
%       CASE style WHEN 0 THEN 2 ELSE style END AS style
%     FROM
%       users
%   ) u ON t.tid = u.style
% GROUP BY
%   t.tid
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   1



 [b][u]4. Monthly activity breakdown by forum sections:[/u][/b]
% SELECT
%   f.name,
%   COUNT(*)
% FROM
%   posts p INNER JOIN (
%     SELECT
%       fl.fid as fid,
%       fl.name as name,
%       pf.disporder as order1,
%       fl.disporder as order2
%     FROM
%       forums fl INNER JOIN forums pf on fl.pid = pf.fid
%     WHERE
%       fl.type = 'f' AND
%       fl.name <> 'Blogs'
%   ) f ON p.fid = f.fid
% WHERE
%   p.visible = 1 AND
%   YEAR(FROM_UNIXTIME(p.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
%   MONTH(FROM_UNIXTIME(p.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
% GROUP BY
%   f.fid
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   f.order1, f.order2



 [b][u]5. Threads made this month by forum section:[/u][/b]
% SELECT
%   f.name,
%   COUNT(*)
% FROM
%   threads t INNER JOIN (
%     SELECT
%       fl.fid as fid,
%       fl.name as name,
%       pf.disporder as order1,
%       fl.disporder as order2
%     FROM
%       forums fl INNER JOIN forums pf on fl.pid = pf.fid
%     WHERE
%       fl.type = 'f' AND
%       fl.name <> 'Blogs'
%   ) f ON t.fid = f.fid
% WHERE
%   t.visible = 1 AND
%   YEAR(FROM_UNIXTIME(t.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
%   MONTH(FROM_UNIXTIME(t.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
% GROUP BY
%   f.fid
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   f.order1, f.order2



 [b][u]6. Monthly level-ups:[/u][/b]
% SELECT
%   g.title,
%   COUNT(*)
% FROM
%   usergroups g INNER JOIN promotionlogs p ON g.gid = p.newusergroup
% WHERE
%   YEAR(FROM_UNIXTIME(p.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
%   MONTH(FROM_UNIXTIME(p.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
% GROUP BY
%   g.gid
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   1



 [b][u]7. Last login times:[/u][/b]
% SELECT
%   i.grp,
%   COUNT(*)
% FROM
%   (
%     SELECT
%       UNIX_TIMESTAMP(NOW()) - u.lastactive AS time
%     FROM
%       users u
%   ) la INNER JOIN (
%     SELECT
%       0 AS low, 86400 AS high, 'Today' AS grp
%     UNION ALL SELECT
%       86400, 172800, '1 day ago'
%     UNION ALL SELECT
%       172800, 259200, '2 days ago'
%     UNION ALL SELECT
%       259200, 345600, '3 days ago'
%     UNION ALL SELECT
%       345600, 604800, 'This week'
%     UNION ALL SELECT
%       604800, 1209600, '1 week ago'
%     UNION ALL SELECT
%       1209600, 1814400, '2 weeks ago'
%     UNION ALL SELECT
%       1814400, UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH), 'This month'
%     UNION ALL SELECT
%       UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH), 2147483647, 'Before this month'
%   ) i ON la.time >= i.low AND la.time < i.high
% GROUP BY
%   i.grp, i.low
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   i.low



 [b][u]8. Threads by post count:[/u][/b]
% SELECT
%   IF (
%     r.low = 0,
%     CONCAT(CAST(r.high AS CHAR), ' and below'),
%     IF (
%       r.high = 2147483647,
%       CONCAT(CAST(r.low AS CHAR), ' and above'),
%       CONCAT(CAST(r.low AS CHAR), ' to ', CAST(r.high AS CHAR))
%     )
%   ),
%   COUNT(*)
% FROM
%   (
%     SELECT
%       t.replies + 1 AS replies
%     FROM
%       threads t
%     WHERE
%       t.visible = 1 AND
%       t.fid <> 8
%   ) n INNER JOIN (
%     SELECT
%       0 AS low, 10 AS high
%     UNION ALL SELECT
%       11, 20
%     UNION ALL SELECT
%       21, 40
%     UNION ALL SELECT
%       41, 60
%     UNION ALL SELECT
%       61, 100
%     UNION ALL SELECT
%       101, 200
%     UNION ALL SELECT
%       201, 500
%     UNION ALL SELECT
%       501, 2147483647
%   ) r ON n.replies >= r.low AND n.replies <= r.high
% GROUP BY
%   r.low, r.high
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   r.low



 [b][u]9. Blogs this month by comment count:[/u][/b]
% SELECT
%   IF (
%     r.low = 0,
%     CONCAT(CAST(r.high AS CHAR), ' and below'),
%     IF (
%       r.high = 2147483647,
%       CONCAT(CAST(r.low AS CHAR), ' and above'),
%       CONCAT(CAST(r.low AS CHAR), ' to ', CAST(r.high AS CHAR))
%     )
%   ),
%   COUNT(*)
% FROM
%   (
%     SELECT
%       t.replies AS replies
%     FROM
%       threads t
%     WHERE
%       t.visible = 1 AND
%       t.fid = 8 AND
%       YEAR(FROM_UNIXTIME(t.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
%       MONTH(FROM_UNIXTIME(t.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
%   ) n INNER JOIN (
%     SELECT
%       0 AS low, 4 AS high
%     UNION ALL SELECT
%       5, 9
%     UNION ALL SELECT
%       10, 19
%     UNION ALL SELECT
%       20, 29
%     UNION ALL SELECT
%       30, 39
%     UNION ALL SELECT
%       40, 59
%     UNION ALL SELECT
%       60, 99
%     UNION ALL SELECT
%       100, 199
%     UNION ALL SELECT
%       200, 499
%     UNION ALL SELECT
%       500, 2147483647
%   ) r ON n.replies >= r.low AND n.replies <= r.high
% GROUP BY
%   r.low, r.high
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   r.low



 [b][u]10. Total amount of buddy requests, across all users:[/u][/b]
% SELECT
%   'Accepted',
%   (
%     SELECT
%       SUM(1 + LENGTH(buddylist) - LENGTH(REPLACE(buddylist, ',', '')))
%     FROM
%       users
%     WHERE
%       buddylist != ''
%   )
% UNION ALL SELECT
%   'Pending',
%   (
%     SELECT
%       COUNT(*)
%     FROM
%       buddyrequests
%   )



 [b][u]11. Amount of buddies per user:[/u][/b]
% SELECT
%   IF(
%     tiers.low = tiers.high,
%     ELT(1 + tiers.high, 'None', 'One', 'Two', 'Three', 'Four', 'Five'),
%     CONCAT(CAST(tiers.low AS CHAR), IF(tiers.high = 2147483647, ' and above', CONCAT(' to ', CAST(tiers.high AS CHAR))))
%   ),
%   COUNT(*)
% FROM
%   (
%     SELECT
%       IF(
%         buddylist = '',
%         0,
%         1 + LENGTH(buddylist) - LENGTH(REPLACE(buddylist, ',', ''))
%       ) AS amount
%     FROM
%       users
%   ) bc INNER JOIN (
%     SELECT
%       0 AS low, 0 AS high
%     UNION ALL SELECT
%       1, 1
%     UNION ALL SELECT
%       2, 2
%     UNION ALL SELECT
%       3, 3
%     UNION ALL SELECT
%       4, 4
%     UNION ALL SELECT
%       5, 5
%     UNION ALL SELECT
%       6, 9
%     UNION ALL SELECT
%       10, 19
%     UNION ALL SELECT
%       20, 29
%     UNION ALL SELECT
%       30, 49
%     UNION ALL SELECT
%       50, 99
%     UNION ALL SELECT
%       100, 2147483647
%   ) tiers ON bc.amount >= tiers.low AND bc.amount <= tiers.high
% GROUP BY
%   tiers.low, tiers.high
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   tiers.high



 [b][u]12. Amount of ignored users per user:[/u][/b]
% SELECT
%   IF(
%     tiers.low = tiers.high,
%     ELT(1 + tiers.high, 'None', 'One', 'Two', 'Three', 'Four', 'Five'),
%     CONCAT(CAST(tiers.low AS CHAR), IF(tiers.high = 2147483647, ' and above', CONCAT(' to ', CAST(tiers.high AS CHAR))))
%   ),
%   COUNT(*)
% FROM
%   (
%     SELECT
%       IF(
%         ignorelist = '',
%         0,
%         1 + LENGTH(ignorelist) - LENGTH(REPLACE(ignorelist, ',', ''))
%       ) AS amount
%     FROM
%       users
%   ) bc INNER JOIN (
%     SELECT
%       0 AS low, 0 AS high
%     UNION ALL SELECT
%       1, 1
%     UNION ALL SELECT
%       2, 2
%     UNION ALL SELECT
%       3, 3
%     UNION ALL SELECT
%       4, 4
%     UNION ALL SELECT
%       5, 5
%     UNION ALL SELECT
%       6, 9
%     UNION ALL SELECT
%       10, 19
%     UNION ALL SELECT
%       20, 29
%     UNION ALL SELECT
%       30, 49
%     UNION ALL SELECT
%       50, 99
%     UNION ALL SELECT
%       100, 2147483647
%   ) tiers ON bc.amount >= tiers.low AND bc.amount <= tiers.high
% GROUP BY
%   tiers.low, tiers.high
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   tiers.high



 [b][u]13. Username changes per user:[/u][/b]
% SELECT
%   amount,
%   COUNT(*)
% FROM
%   (
%     SELECT
%       COALESCE(
%         ELT(
%           1 + COUNT(h.dateline),
%           'None', 'One', 'Two', 'Three', 'Four', 'Five'
%         ),
%         'More than five'
%       ) AS amount,
%       COUNT(h.dateline) AS number
%     FROM
%       users u LEFT JOIN usernamehistory h ON u.uid = h.uid
%     GROUP BY
%       u.uid
%   ) a
% GROUP BY
%   a.amount
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   a.number



 [b][u]14. Username changes, overall:[/u][/b]
% SELECT
%   ELT(
%     kind,
%     'From previous months',
%     'Via User Control Panel, this month',
%     'Administratively issued, this month'
%   ),
%   COUNT(*)
% FROM
%   (
%     SELECT
%       IF(
%         YEAR(FROM_UNIXTIME(dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
%         MONTH(FROM_UNIXTIME(dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY),
%         adminchange + 2,
%         1
%       ) AS kind
%     FROM
%       usernamehistory
%   ) h
% GROUP BY
%   kind
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   kind



 [b][u]15. Attachments uploaded this month, by type:[/u][/b]
% SELECT
%   IFNULL(name, 'Other'),
%   COUNT(*)
% FROM
%   (
%     SELECT
%       t.name AS name,
%       a.dateuploaded AS dateline
%     FROM
%       attachments a LEFT JOIN attachtypes t
%         ON LCASE(SUBSTRING_INDEX(a.filename, '.', -1)) = LCASE(t.extension)
%   ) fa
% WHERE
%   YEAR(FROM_UNIXTIME(dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
%   MONTH(FROM_UNIXTIME(dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
% GROUP BY
%   name
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   name IS NULL, name



 [b][u]16. Total attachment size (in kilobytes) uploaded this month, by type:[/u][/b]
% SELECT
%   IFNULL(name, 'Other'),
%   IFNULL(ROUND(SUM(filesize) / 1024), 0)
% FROM
%   (
%     SELECT
%       t.name AS name,
%       a.dateuploaded AS dateline,
%       a.filesize AS filesize
%     FROM
%       attachments a LEFT JOIN attachtypes t
%         ON LCASE(SUBSTRING_INDEX(a.filename, '.', -1)) = LCASE(t.extension)
%   ) fa
% WHERE
%   YEAR(FROM_UNIXTIME(dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
%   MONTH(FROM_UNIXTIME(dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
% GROUP BY
%   name
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   name IS NULL, name



 [b][u]17. Date and time formats, by amount of users:[/u][/b]
% SELECT
%   ELT(
%     c.kind + 1,
%     'Default date and time formats',
%     'Custom time format',
%     'Custom date format',
%     'Custom date and time formats'
%   ),
%   c.amount
% FROM
%   (
%     SELECT
%       2 * (u.dateformat != '' AND u.dateformat != '0') +
%       (u.timeformat != '' AND u.timeformat != '0') AS kind,
%       COUNT(*) AS amount
%     FROM
%       users u
%     GROUP BY
%       1
%     ORDER BY
%       1
%   ) AS c
% ORDER BY
%   c.kind



 [b][u]18. Referrals per user:[/u][/b]
% SELECT
%   IF(
%     tiers.low = tiers.high,
%     ELT(1 + tiers.high, 'None', 'One', 'Two', 'Three', 'Four', 'Five'),
%     CONCAT(CAST(tiers.low AS CHAR), IF(tiers.high = 2147483647, ' and above', CONCAT(' to ', CAST(tiers.high AS CHAR))))
%   ),
%   COUNT(*)
% FROM
%   users u INNER JOIN (
%     SELECT
%       0 AS low, 0 AS high
%     UNION ALL SELECT
%       1, 1
%     UNION ALL SELECT
%       2, 2
%     UNION ALL SELECT
%       3, 3
%     UNION ALL SELECT
%       4, 4
%     UNION ALL SELECT
%       5, 5
%     UNION ALL SELECT
%       6, 9
%     UNION ALL SELECT
%       10, 14
%     UNION ALL SELECT
%       15, 19
%     UNION ALL SELECT
%       20, 29
%     UNION ALL SELECT
%       30, 49
%     UNION ALL SELECT
%       50, 2147483647
%   ) tiers ON u.referrals >= tiers.low AND u.referrals <= tiers.high
% GROUP BY
%   tiers.low, tiers.high
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   tiers.high



 [b][u]19. Posts this month by time (UTC):[/u][/b]
% SELECT
%   CONCAT(DATE_FORMAT(FROM_UNIXTIME(tiers.low), '%k:%i'), ' to ', DATE_FORMAT(FROM_UNIXTIME(tiers.high), '%k:%i')),
%   COUNT(*)
% FROM
%   (
%     SELECT
%       (pc.dateline % 86400) AS time
%     FROM
%       posts pc INNER JOIN threads t ON pc.tid = t.tid
%     WHERE
%       YEAR(FROM_UNIXTIME(pc.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
%       MONTH(FROM_UNIXTIME(pc.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY) AND
%       pc.visible = 1 AND
%       t.visible = 1 AND
%       t.fid <> 8
%   ) p INNER JOIN (
%     SELECT
%       0 AS low, 5399 AS high
%     UNION ALL SELECT
%       5400, 10799
%     UNION ALL SELECT
%       10800, 16199
%     UNION ALL SELECT
%       16200, 21599
%     UNION ALL SELECT
%       21600, 26999
%     UNION ALL SELECT
%       27000, 32399
%     UNION ALL SELECT
%       32400, 37799
%     UNION ALL SELECT
%       37800, 43199
%     UNION ALL SELECT
%       43200, 48599
%     UNION ALL SELECT
%       48600, 53999
%     UNION ALL SELECT
%       54000, 59399
%     UNION ALL SELECT
%       59400, 64799
%     UNION ALL SELECT
%       64800, 70199
%     UNION ALL SELECT
%       70200, 75599
%     UNION ALL SELECT
%       75600, 80999
%     UNION ALL SELECT
%       81000, 86399
%   ) tiers ON p.time >= tiers.low AND p.time <= tiers.high
% GROUP BY
%   tiers.low, tiers.high
% HAVING
%   COUNT(*) > 0
% ORDER BY
%   tiers.low



 [b][u]20. Posts this month by day of week (UTC):[/u][/b] (this statistic adjusts for days that occur five times this month)
% SELECT
%   CONCAT(dayname, IF(adjust, ' (adjusted -20%)', '')),
%   IF(adjust, (amount * 4 + 2) DIV 5, amount)
% FROM
%   (
%     SELECT
%       ELT(counts.dow, 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday') AS dayname,
%       counts.amount AS amount,
%       ld.dow IS NOT NULL AS adjust
%     FROM
%       (
%         SELECT
%           (pp.dateline DIV 86400 + 4) MOD 7 + 1 AS dow,
%           COUNT(*) AS amount
%         FROM
%           posts pp INNER JOIN threads t ON pp.tid = t.tid
%         WHERE
%           YEAR(FROM_UNIXTIME(pp.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
%           MONTH(FROM_UNIXTIME(pp.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY) AND
%           pp.visible = 1 AND
%           t.visible = 1 AND
%           t.fid <> 8
%         GROUP BY
%           dow
%       ) counts LEFT OUTER JOIN (
%         SELECT
%           DAYOFWEEK(MAKEDATE(m.y, dn.d) + INTERVAL (m.m - 1) MONTH) AS dow
%         FROM
%           (
%             SELECT
%               29 AS d
%             UNION ALL SELECT
%               30
%             UNION ALL SELECT
%               31
%           ) dn, (
%             SELECT
%               YEAR(UTC_DATE() - INTERVAL 10 DAY) AS y,
%               MONTH(UTC_DATE() - INTERVAL 10 DAY) As m
%           ) m
%         WHERE
%           dn.d <= DAY(LAST_DAY(MAKEDATE(m.y, 1) + INTERVAL (m.m - 1) MONTH))
%       ) ld ON counts.dow = ld.dow
%     WHERE
%       counts.amount > 0
%     ORDER BY
%       counts.dow = 1, counts.dow
%   ) totals



 [b][u]21. Users by online time:[/u][/b]
% SELECT
%   IF(unit = 'never', 'No time', CONCAT(
%     IF(low = 0, 'Less than', low),
%     ' ',
%     IF(high = 2147483647,
%       CONCAT(unit, IF(low = 1, '', 's'), ' and above'),
%       CONCAT(IF(low = 0, '', 'to '), high, ' ', unit, IF(high = 1, '', 's'))
%     )
%   )),
%   COUNT(*)
% FROM
%   (
%     SELECT
%       v.low AS low,
%       v.high AS high,
%       v.unit AS unit,
%       v.sort AS sort
%     FROM
%       (
%         SELECT
%           CASE
%             WHEN timeonline < 86400 THEN
%               timeonline / 3600
%             WHEN timeonline < 604800 THEN
%               timeonline / 86400
%             ELSE
%               timeonline / 604800
%           END AS amount,
%           CASE
%             WHEN timeonline <= 0 THEN
%               'never'
%             WHEN timeonline < 86400 THEN
%               'hour'
%             WHEN timeonline < 604800 THEN
%               'day'
%             ELSE
%               'week'
%           END AS unit
%         FROM
%           users
%       ) t INNER JOIN (
%         SELECT
%           0 AS low, 2147483647 AS high, 'never' AS unit, 0 AS sort
%         UNION ALL SELECT
%           0, 1, 'hour', 1
%         UNION ALL SELECT
%           1, 3, 'hour', 1
%         UNION ALL SELECT
%           3, 6, 'hour', 1
%         UNION ALL SELECT
%           6, 12, 'hour', 1
%         UNION ALL SELECT
%           12, 24, 'hour', 1
%         UNION ALL SELECT
%           1, 2, 'day', 2
%         UNION ALL SELECT
%           2, 3, 'day', 2
%         UNION ALL SELECT
%           3, 5, 'day', 2
%         UNION ALL SELECT
%           5, 7, 'day', 2
%         UNION ALL SELECT
%           1, 2, 'week', 3
%         UNION ALL SELECT
%           2, 3, 'week', 3
%         UNION ALL SELECT
%           3, 5, 'week', 3
%         UNION ALL SELECT
%           5, 10, 'week', 3
%         UNION ALL SELECT
%           10, 2147483647, 'week', 3
%       ) v ON t.unit = v.unit AND t.amount >= v.low AND t.amount < v.high
%   ) list
% GROUP BY
%   sort, low, high, unit
% HAVING
%   COUNT(*) > 0