!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
Comments