Advertisement
aaaaaa123456789

Forum metrics — monthly metrics generating script

Aug 31st, 2015
57
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. !acidforums
  2. [hr]
  3.  
  4.  
  5. [b][u]1. Users by usergroup:[/u][/b]
  6. % SELECT
  7. % g.title,
  8. % COUNT(*)
  9. % FROM
  10. % users u INNER JOIN usergroups g ON u.usergroup = g.gid
  11. % GROUP BY
  12. % g.gid
  13. % HAVING
  14. % COUNT(*) > 0
  15. % ORDER BY
  16. % 1
  17.  
  18.  
  19.  
  20. [b][u]2. New users this month by usergroup:[/u][/b]
  21. % SELECT
  22. % g.title,
  23. % COUNT(*)
  24. % FROM
  25. % users u INNER JOIN usergroups g ON u.usergroup = g.gid
  26. % WHERE
  27. % YEAR(FROM_UNIXTIME(u.regdate)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
  28. % MONTH(FROM_UNIXTIME(u.regdate)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
  29. % GROUP BY
  30. % g.gid
  31. % HAVING
  32. % COUNT(*) > 0
  33. % ORDER BY
  34. % 1
  35.  
  36.  
  37.  
  38. [b][u]3. Usage of forum themes:[/u][/b]
  39. % SELECT
  40. % t.name,
  41. % COUNT(*)
  42. % FROM
  43. % themes t INNER JOIN (
  44. % SELECT
  45. % CASE style WHEN 0 THEN 2 ELSE style END AS style
  46. % FROM
  47. % users
  48. % ) u ON t.tid = u.style
  49. % GROUP BY
  50. % t.tid
  51. % HAVING
  52. % COUNT(*) > 0
  53. % ORDER BY
  54. % 1
  55.  
  56.  
  57.  
  58. [b][u]4. Monthly activity breakdown by forum sections:[/u][/b]
  59. % SELECT
  60. % f.name,
  61. % COUNT(*)
  62. % FROM
  63. % posts p INNER JOIN (
  64. % SELECT
  65. % fl.fid as fid,
  66. % fl.name as name,
  67. % pf.disporder as order1,
  68. % fl.disporder as order2
  69. % FROM
  70. % forums fl INNER JOIN forums pf on fl.pid = pf.fid
  71. % WHERE
  72. % fl.type = 'f' AND
  73. % fl.name <> 'Blogs'
  74. % ) f ON p.fid = f.fid
  75. % WHERE
  76. % p.visible = 1 AND
  77. % YEAR(FROM_UNIXTIME(p.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
  78. % MONTH(FROM_UNIXTIME(p.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
  79. % GROUP BY
  80. % f.fid
  81. % HAVING
  82. % COUNT(*) > 0
  83. % ORDER BY
  84. % f.order1, f.order2
  85.  
  86.  
  87.  
  88. [b][u]5. Threads made this month by forum section:[/u][/b]
  89. % SELECT
  90. % f.name,
  91. % COUNT(*)
  92. % FROM
  93. % threads t INNER JOIN (
  94. % SELECT
  95. % fl.fid as fid,
  96. % fl.name as name,
  97. % pf.disporder as order1,
  98. % fl.disporder as order2
  99. % FROM
  100. % forums fl INNER JOIN forums pf on fl.pid = pf.fid
  101. % WHERE
  102. % fl.type = 'f' AND
  103. % fl.name <> 'Blogs'
  104. % ) f ON t.fid = f.fid
  105. % WHERE
  106. % t.visible = 1 AND
  107. % YEAR(FROM_UNIXTIME(t.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
  108. % MONTH(FROM_UNIXTIME(t.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
  109. % GROUP BY
  110. % f.fid
  111. % HAVING
  112. % COUNT(*) > 0
  113. % ORDER BY
  114. % f.order1, f.order2
  115.  
  116.  
  117.  
  118. [b][u]6. Monthly level-ups:[/u][/b]
  119. % SELECT
  120. % g.title,
  121. % COUNT(*)
  122. % FROM
  123. % usergroups g INNER JOIN promotionlogs p ON g.gid = p.newusergroup
  124. % WHERE
  125. % YEAR(FROM_UNIXTIME(p.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
  126. % MONTH(FROM_UNIXTIME(p.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
  127. % GROUP BY
  128. % g.gid
  129. % HAVING
  130. % COUNT(*) > 0
  131. % ORDER BY
  132. % 1
  133.  
  134.  
  135.  
  136. [b][u]7. Last login times:[/u][/b]
  137. % SELECT
  138. % i.grp,
  139. % COUNT(*)
  140. % FROM
  141. % (
  142. % SELECT
  143. % UNIX_TIMESTAMP(NOW()) - u.lastactive AS time
  144. % FROM
  145. % users u
  146. % ) la INNER JOIN (
  147. % SELECT
  148. % 0 AS low, 86400 AS high, 'Today' AS grp
  149. % UNION ALL SELECT
  150. % 86400, 172800, '1 day ago'
  151. % UNION ALL SELECT
  152. % 172800, 259200, '2 days ago'
  153. % UNION ALL SELECT
  154. % 259200, 345600, '3 days ago'
  155. % UNION ALL SELECT
  156. % 345600, 604800, 'This week'
  157. % UNION ALL SELECT
  158. % 604800, 1209600, '1 week ago'
  159. % UNION ALL SELECT
  160. % 1209600, 1814400, '2 weeks ago'
  161. % UNION ALL SELECT
  162. % 1814400, UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH), 'This month'
  163. % UNION ALL SELECT
  164. % UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH), 2147483647, 'Before this month'
  165. % ) i ON la.time >= i.low AND la.time < i.high
  166. % GROUP BY
  167. % i.grp, i.low
  168. % HAVING
  169. % COUNT(*) > 0
  170. % ORDER BY
  171. % i.low
  172.  
  173.  
  174.  
  175. [b][u]8. Threads by post count:[/u][/b]
  176. % SELECT
  177. % IF (
  178. % r.low = 0,
  179. % CONCAT(CAST(r.high AS CHAR), ' and below'),
  180. % IF (
  181. % r.high = 2147483647,
  182. % CONCAT(CAST(r.low AS CHAR), ' and above'),
  183. % CONCAT(CAST(r.low AS CHAR), ' to ', CAST(r.high AS CHAR))
  184. % )
  185. % ),
  186. % COUNT(*)
  187. % FROM
  188. % (
  189. % SELECT
  190. % t.replies + 1 AS replies
  191. % FROM
  192. % threads t
  193. % WHERE
  194. % t.visible = 1 AND
  195. % t.fid <> 8
  196. % ) n INNER JOIN (
  197. % SELECT
  198. % 0 AS low, 10 AS high
  199. % UNION ALL SELECT
  200. % 11, 20
  201. % UNION ALL SELECT
  202. % 21, 40
  203. % UNION ALL SELECT
  204. % 41, 60
  205. % UNION ALL SELECT
  206. % 61, 100
  207. % UNION ALL SELECT
  208. % 101, 200
  209. % UNION ALL SELECT
  210. % 201, 500
  211. % UNION ALL SELECT
  212. % 501, 2147483647
  213. % ) r ON n.replies >= r.low AND n.replies <= r.high
  214. % GROUP BY
  215. % r.low, r.high
  216. % HAVING
  217. % COUNT(*) > 0
  218. % ORDER BY
  219. % r.low
  220.  
  221.  
  222.  
  223. [b][u]9. Blogs this month by comment count:[/u][/b]
  224. % SELECT
  225. % IF (
  226. % r.low = 0,
  227. % CONCAT(CAST(r.high AS CHAR), ' and below'),
  228. % IF (
  229. % r.high = 2147483647,
  230. % CONCAT(CAST(r.low AS CHAR), ' and above'),
  231. % CONCAT(CAST(r.low AS CHAR), ' to ', CAST(r.high AS CHAR))
  232. % )
  233. % ),
  234. % COUNT(*)
  235. % FROM
  236. % (
  237. % SELECT
  238. % t.replies AS replies
  239. % FROM
  240. % threads t
  241. % WHERE
  242. % t.visible = 1 AND
  243. % t.fid = 8 AND
  244. % YEAR(FROM_UNIXTIME(t.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
  245. % MONTH(FROM_UNIXTIME(t.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
  246. % ) n INNER JOIN (
  247. % SELECT
  248. % 0 AS low, 4 AS high
  249. % UNION ALL SELECT
  250. % 5, 9
  251. % UNION ALL SELECT
  252. % 10, 19
  253. % UNION ALL SELECT
  254. % 20, 29
  255. % UNION ALL SELECT
  256. % 30, 39
  257. % UNION ALL SELECT
  258. % 40, 59
  259. % UNION ALL SELECT
  260. % 60, 99
  261. % UNION ALL SELECT
  262. % 100, 199
  263. % UNION ALL SELECT
  264. % 200, 499
  265. % UNION ALL SELECT
  266. % 500, 2147483647
  267. % ) r ON n.replies >= r.low AND n.replies <= r.high
  268. % GROUP BY
  269. % r.low, r.high
  270. % HAVING
  271. % COUNT(*) > 0
  272. % ORDER BY
  273. % r.low
  274.  
  275.  
  276.  
  277. [b][u]10. Total amount of buddy requests, across all users:[/u][/b]
  278. % SELECT
  279. % 'Accepted',
  280. % (
  281. % SELECT
  282. % SUM(1 + LENGTH(buddylist) - LENGTH(REPLACE(buddylist, ',', '')))
  283. % FROM
  284. % users
  285. % WHERE
  286. % buddylist != ''
  287. % )
  288. % UNION ALL SELECT
  289. % 'Pending',
  290. % (
  291. % SELECT
  292. % COUNT(*)
  293. % FROM
  294. % buddyrequests
  295. % )
  296.  
  297.  
  298.  
  299. [b][u]11. Amount of buddies per user:[/u][/b]
  300. % SELECT
  301. % IF(
  302. % tiers.low = tiers.high,
  303. % ELT(1 + tiers.high, 'None', 'One', 'Two', 'Three', 'Four', 'Five'),
  304. % CONCAT(CAST(tiers.low AS CHAR), IF(tiers.high = 2147483647, ' and above', CONCAT(' to ', CAST(tiers.high AS CHAR))))
  305. % ),
  306. % COUNT(*)
  307. % FROM
  308. % (
  309. % SELECT
  310. % IF(
  311. % buddylist = '',
  312. % 0,
  313. % 1 + LENGTH(buddylist) - LENGTH(REPLACE(buddylist, ',', ''))
  314. % ) AS amount
  315. % FROM
  316. % users
  317. % ) bc INNER JOIN (
  318. % SELECT
  319. % 0 AS low, 0 AS high
  320. % UNION ALL SELECT
  321. % 1, 1
  322. % UNION ALL SELECT
  323. % 2, 2
  324. % UNION ALL SELECT
  325. % 3, 3
  326. % UNION ALL SELECT
  327. % 4, 4
  328. % UNION ALL SELECT
  329. % 5, 5
  330. % UNION ALL SELECT
  331. % 6, 9
  332. % UNION ALL SELECT
  333. % 10, 19
  334. % UNION ALL SELECT
  335. % 20, 29
  336. % UNION ALL SELECT
  337. % 30, 49
  338. % UNION ALL SELECT
  339. % 50, 99
  340. % UNION ALL SELECT
  341. % 100, 2147483647
  342. % ) tiers ON bc.amount >= tiers.low AND bc.amount <= tiers.high
  343. % GROUP BY
  344. % tiers.low, tiers.high
  345. % HAVING
  346. % COUNT(*) > 0
  347. % ORDER BY
  348. % tiers.high
  349.  
  350.  
  351.  
  352. [b][u]12. Amount of ignored users per user:[/u][/b]
  353. % SELECT
  354. % IF(
  355. % tiers.low = tiers.high,
  356. % ELT(1 + tiers.high, 'None', 'One', 'Two', 'Three', 'Four', 'Five'),
  357. % CONCAT(CAST(tiers.low AS CHAR), IF(tiers.high = 2147483647, ' and above', CONCAT(' to ', CAST(tiers.high AS CHAR))))
  358. % ),
  359. % COUNT(*)
  360. % FROM
  361. % (
  362. % SELECT
  363. % IF(
  364. % ignorelist = '',
  365. % 0,
  366. % 1 + LENGTH(ignorelist) - LENGTH(REPLACE(ignorelist, ',', ''))
  367. % ) AS amount
  368. % FROM
  369. % users
  370. % ) bc INNER JOIN (
  371. % SELECT
  372. % 0 AS low, 0 AS high
  373. % UNION ALL SELECT
  374. % 1, 1
  375. % UNION ALL SELECT
  376. % 2, 2
  377. % UNION ALL SELECT
  378. % 3, 3
  379. % UNION ALL SELECT
  380. % 4, 4
  381. % UNION ALL SELECT
  382. % 5, 5
  383. % UNION ALL SELECT
  384. % 6, 9
  385. % UNION ALL SELECT
  386. % 10, 19
  387. % UNION ALL SELECT
  388. % 20, 29
  389. % UNION ALL SELECT
  390. % 30, 49
  391. % UNION ALL SELECT
  392. % 50, 99
  393. % UNION ALL SELECT
  394. % 100, 2147483647
  395. % ) tiers ON bc.amount >= tiers.low AND bc.amount <= tiers.high
  396. % GROUP BY
  397. % tiers.low, tiers.high
  398. % HAVING
  399. % COUNT(*) > 0
  400. % ORDER BY
  401. % tiers.high
  402.  
  403.  
  404.  
  405. [b][u]13. Username changes per user:[/u][/b]
  406. % SELECT
  407. % amount,
  408. % COUNT(*)
  409. % FROM
  410. % (
  411. % SELECT
  412. % COALESCE(
  413. % ELT(
  414. % 1 + COUNT(h.dateline),
  415. % 'None', 'One', 'Two', 'Three', 'Four', 'Five'
  416. % ),
  417. % 'More than five'
  418. % ) AS amount,
  419. % COUNT(h.dateline) AS number
  420. % FROM
  421. % users u LEFT JOIN usernamehistory h ON u.uid = h.uid
  422. % GROUP BY
  423. % u.uid
  424. % ) a
  425. % GROUP BY
  426. % a.amount
  427. % HAVING
  428. % COUNT(*) > 0
  429. % ORDER BY
  430. % a.number
  431.  
  432.  
  433.  
  434. [b][u]14. Username changes, overall:[/u][/b]
  435. % SELECT
  436. % ELT(
  437. % kind,
  438. % 'From previous months',
  439. % 'Via User Control Panel, this month',
  440. % 'Administratively issued, this month'
  441. % ),
  442. % COUNT(*)
  443. % FROM
  444. % (
  445. % SELECT
  446. % IF(
  447. % YEAR(FROM_UNIXTIME(dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
  448. % MONTH(FROM_UNIXTIME(dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY),
  449. % adminchange + 2,
  450. % 1
  451. % ) AS kind
  452. % FROM
  453. % usernamehistory
  454. % ) h
  455. % GROUP BY
  456. % kind
  457. % HAVING
  458. % COUNT(*) > 0
  459. % ORDER BY
  460. % kind
  461.  
  462.  
  463.  
  464. [b][u]15. Attachments uploaded this month, by type:[/u][/b]
  465. % SELECT
  466. % IFNULL(name, 'Other'),
  467. % COUNT(*)
  468. % FROM
  469. % (
  470. % SELECT
  471. % t.name AS name,
  472. % a.dateuploaded AS dateline
  473. % FROM
  474. % attachments a LEFT JOIN attachtypes t
  475. % ON LCASE(SUBSTRING_INDEX(a.filename, '.', -1)) = LCASE(t.extension)
  476. % ) fa
  477. % WHERE
  478. % YEAR(FROM_UNIXTIME(dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
  479. % MONTH(FROM_UNIXTIME(dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
  480. % GROUP BY
  481. % name
  482. % HAVING
  483. % COUNT(*) > 0
  484. % ORDER BY
  485. % name IS NULL, name
  486.  
  487.  
  488.  
  489. [b][u]16. Total attachment size (in kilobytes) uploaded this month, by type:[/u][/b]
  490. % SELECT
  491. % IFNULL(name, 'Other'),
  492. % IFNULL(ROUND(SUM(filesize) / 1024), 0)
  493. % FROM
  494. % (
  495. % SELECT
  496. % t.name AS name,
  497. % a.dateuploaded AS dateline,
  498. % a.filesize AS filesize
  499. % FROM
  500. % attachments a LEFT JOIN attachtypes t
  501. % ON LCASE(SUBSTRING_INDEX(a.filename, '.', -1)) = LCASE(t.extension)
  502. % ) fa
  503. % WHERE
  504. % YEAR(FROM_UNIXTIME(dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
  505. % MONTH(FROM_UNIXTIME(dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY)
  506. % GROUP BY
  507. % name
  508. % HAVING
  509. % COUNT(*) > 0
  510. % ORDER BY
  511. % name IS NULL, name
  512.  
  513.  
  514.  
  515. [b][u]17. Date and time formats, by amount of users:[/u][/b]
  516. % SELECT
  517. % ELT(
  518. % c.kind + 1,
  519. % 'Default date and time formats',
  520. % 'Custom time format',
  521. % 'Custom date format',
  522. % 'Custom date and time formats'
  523. % ),
  524. % c.amount
  525. % FROM
  526. % (
  527. % SELECT
  528. % 2 * (u.dateformat != '' AND u.dateformat != '0') +
  529. % (u.timeformat != '' AND u.timeformat != '0') AS kind,
  530. % COUNT(*) AS amount
  531. % FROM
  532. % users u
  533. % GROUP BY
  534. % 1
  535. % ORDER BY
  536. % 1
  537. % ) AS c
  538. % ORDER BY
  539. % c.kind
  540.  
  541.  
  542.  
  543. [b][u]18. Referrals per user:[/u][/b]
  544. % SELECT
  545. % IF(
  546. % tiers.low = tiers.high,
  547. % ELT(1 + tiers.high, 'None', 'One', 'Two', 'Three', 'Four', 'Five'),
  548. % CONCAT(CAST(tiers.low AS CHAR), IF(tiers.high = 2147483647, ' and above', CONCAT(' to ', CAST(tiers.high AS CHAR))))
  549. % ),
  550. % COUNT(*)
  551. % FROM
  552. % users u INNER JOIN (
  553. % SELECT
  554. % 0 AS low, 0 AS high
  555. % UNION ALL SELECT
  556. % 1, 1
  557. % UNION ALL SELECT
  558. % 2, 2
  559. % UNION ALL SELECT
  560. % 3, 3
  561. % UNION ALL SELECT
  562. % 4, 4
  563. % UNION ALL SELECT
  564. % 5, 5
  565. % UNION ALL SELECT
  566. % 6, 9
  567. % UNION ALL SELECT
  568. % 10, 14
  569. % UNION ALL SELECT
  570. % 15, 19
  571. % UNION ALL SELECT
  572. % 20, 29
  573. % UNION ALL SELECT
  574. % 30, 49
  575. % UNION ALL SELECT
  576. % 50, 2147483647
  577. % ) tiers ON u.referrals >= tiers.low AND u.referrals <= tiers.high
  578. % GROUP BY
  579. % tiers.low, tiers.high
  580. % HAVING
  581. % COUNT(*) > 0
  582. % ORDER BY
  583. % tiers.high
  584.  
  585.  
  586.  
  587. [b][u]19. Posts this month by time (UTC):[/u][/b]
  588. % SELECT
  589. % CONCAT(DATE_FORMAT(FROM_UNIXTIME(tiers.low), '%k:%i'), ' to ', DATE_FORMAT(FROM_UNIXTIME(tiers.high), '%k:%i')),
  590. % COUNT(*)
  591. % FROM
  592. % (
  593. % SELECT
  594. % (pc.dateline % 86400) AS time
  595. % FROM
  596. % posts pc INNER JOIN threads t ON pc.tid = t.tid
  597. % WHERE
  598. % YEAR(FROM_UNIXTIME(pc.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
  599. % MONTH(FROM_UNIXTIME(pc.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY) AND
  600. % pc.visible = 1 AND
  601. % t.visible = 1 AND
  602. % t.fid <> 8
  603. % ) p INNER JOIN (
  604. % SELECT
  605. % 0 AS low, 5399 AS high
  606. % UNION ALL SELECT
  607. % 5400, 10799
  608. % UNION ALL SELECT
  609. % 10800, 16199
  610. % UNION ALL SELECT
  611. % 16200, 21599
  612. % UNION ALL SELECT
  613. % 21600, 26999
  614. % UNION ALL SELECT
  615. % 27000, 32399
  616. % UNION ALL SELECT
  617. % 32400, 37799
  618. % UNION ALL SELECT
  619. % 37800, 43199
  620. % UNION ALL SELECT
  621. % 43200, 48599
  622. % UNION ALL SELECT
  623. % 48600, 53999
  624. % UNION ALL SELECT
  625. % 54000, 59399
  626. % UNION ALL SELECT
  627. % 59400, 64799
  628. % UNION ALL SELECT
  629. % 64800, 70199
  630. % UNION ALL SELECT
  631. % 70200, 75599
  632. % UNION ALL SELECT
  633. % 75600, 80999
  634. % UNION ALL SELECT
  635. % 81000, 86399
  636. % ) tiers ON p.time >= tiers.low AND p.time <= tiers.high
  637. % GROUP BY
  638. % tiers.low, tiers.high
  639. % HAVING
  640. % COUNT(*) > 0
  641. % ORDER BY
  642. % tiers.low
  643.  
  644.  
  645.  
  646. [b][u]20. Posts this month by day of week (UTC):[/u][/b] (this statistic adjusts for days that occur five times this month)
  647. % SELECT
  648. % CONCAT(dayname, IF(adjust, ' (adjusted -20%)', '')),
  649. % IF(adjust, (amount * 4 + 2) DIV 5, amount)
  650. % FROM
  651. % (
  652. % SELECT
  653. % ELT(counts.dow, 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday') AS dayname,
  654. % counts.amount AS amount,
  655. % ld.dow IS NOT NULL AS adjust
  656. % FROM
  657. % (
  658. % SELECT
  659. % (pp.dateline DIV 86400 + 4) MOD 7 + 1 AS dow,
  660. % COUNT(*) AS amount
  661. % FROM
  662. % posts pp INNER JOIN threads t ON pp.tid = t.tid
  663. % WHERE
  664. % YEAR(FROM_UNIXTIME(pp.dateline)) = YEAR(UTC_DATE() - INTERVAL 10 DAY) AND
  665. % MONTH(FROM_UNIXTIME(pp.dateline)) = MONTH(UTC_DATE() - INTERVAL 10 DAY) AND
  666. % pp.visible = 1 AND
  667. % t.visible = 1 AND
  668. % t.fid <> 8
  669. % GROUP BY
  670. % dow
  671. % ) counts LEFT OUTER JOIN (
  672. % SELECT
  673. % DAYOFWEEK(MAKEDATE(m.y, dn.d) + INTERVAL (m.m - 1) MONTH) AS dow
  674. % FROM
  675. % (
  676. % SELECT
  677. % 29 AS d
  678. % UNION ALL SELECT
  679. % 30
  680. % UNION ALL SELECT
  681. % 31
  682. % ) dn, (
  683. % SELECT
  684. % YEAR(UTC_DATE() - INTERVAL 10 DAY) AS y,
  685. % MONTH(UTC_DATE() - INTERVAL 10 DAY) As m
  686. % ) m
  687. % WHERE
  688. % dn.d <= DAY(LAST_DAY(MAKEDATE(m.y, 1) + INTERVAL (m.m - 1) MONTH))
  689. % ) ld ON counts.dow = ld.dow
  690. % WHERE
  691. % counts.amount > 0
  692. % ORDER BY
  693. % counts.dow = 1, counts.dow
  694. % ) totals
  695.  
  696.  
  697.  
  698. [b][u]21. Users by online time:[/u][/b]
  699. % SELECT
  700. % IF(unit = 'never', 'No time', CONCAT(
  701. % IF(low = 0, 'Less than', low),
  702. % ' ',
  703. % IF(high = 2147483647,
  704. % CONCAT(unit, IF(low = 1, '', 's'), ' and above'),
  705. % CONCAT(IF(low = 0, '', 'to '), high, ' ', unit, IF(high = 1, '', 's'))
  706. % )
  707. % )),
  708. % COUNT(*)
  709. % FROM
  710. % (
  711. % SELECT
  712. % v.low AS low,
  713. % v.high AS high,
  714. % v.unit AS unit,
  715. % v.sort AS sort
  716. % FROM
  717. % (
  718. % SELECT
  719. % CASE
  720. % WHEN timeonline < 86400 THEN
  721. % timeonline / 3600
  722. % WHEN timeonline < 604800 THEN
  723. % timeonline / 86400
  724. % ELSE
  725. % timeonline / 604800
  726. % END AS amount,
  727. % CASE
  728. % WHEN timeonline <= 0 THEN
  729. % 'never'
  730. % WHEN timeonline < 86400 THEN
  731. % 'hour'
  732. % WHEN timeonline < 604800 THEN
  733. % 'day'
  734. % ELSE
  735. % 'week'
  736. % END AS unit
  737. % FROM
  738. % users
  739. % ) t INNER JOIN (
  740. % SELECT
  741. % 0 AS low, 2147483647 AS high, 'never' AS unit, 0 AS sort
  742. % UNION ALL SELECT
  743. % 0, 1, 'hour', 1
  744. % UNION ALL SELECT
  745. % 1, 3, 'hour', 1
  746. % UNION ALL SELECT
  747. % 3, 6, 'hour', 1
  748. % UNION ALL SELECT
  749. % 6, 12, 'hour', 1
  750. % UNION ALL SELECT
  751. % 12, 24, 'hour', 1
  752. % UNION ALL SELECT
  753. % 1, 2, 'day', 2
  754. % UNION ALL SELECT
  755. % 2, 3, 'day', 2
  756. % UNION ALL SELECT
  757. % 3, 5, 'day', 2
  758. % UNION ALL SELECT
  759. % 5, 7, 'day', 2
  760. % UNION ALL SELECT
  761. % 1, 2, 'week', 3
  762. % UNION ALL SELECT
  763. % 2, 3, 'week', 3
  764. % UNION ALL SELECT
  765. % 3, 5, 'week', 3
  766. % UNION ALL SELECT
  767. % 5, 10, 'week', 3
  768. % UNION ALL SELECT
  769. % 10, 2147483647, 'week', 3
  770. % ) v ON t.unit = v.unit AND t.amount >= v.low AND t.amount < v.high
  771. % ) list
  772. % GROUP BY
  773. % sort, low, high, unit
  774. % HAVING
  775. % COUNT(*) > 0
Advertisement
RAW Paste Data Copied
Advertisement