april forum stats

Code:

select count(*)/(round((unix_timestamp(date_add(from_unixtime(dateline), interval 1 month))-dateline)/86400)) as ppd, count(*)/(24*round((unix_timestamp(date_add(from_unixtime(dateline), interval 1 month))-dateline)/86400)) as pph, count(*)/(1440*round((unix_timestamp(date_add(from_unixtime(dateline), interval 1 month))-dateline)/86400)) as ppm, count(*) as total, from_unixtime((dateline),"%Y/%m") as month from post group by month order by month;
+---------+-------+------+-------+---------+
| ppd     | pph   | ppm  | total | month   |
+---------+-------+------+-------+---------+
|    1.03 |  0.04 | 0.00 |    32 | 2000/03 |
|    5.93 |  0.25 | 0.00 |   178 | 2000/04 |
|    6.48 |  0.27 | 0.00 |   201 | 2000/05 |
|   20.43 |  0.85 | 0.01 |   613 | 2000/06 |
|   44.29 |  1.85 | 0.03 |  1373 | 2000/07 |
|  106.81 |  4.45 | 0.07 |  3311 | 2000/08 |
|  136.70 |  5.70 | 0.09 |  4101 | 2000/09 |
|  133.97 |  5.58 | 0.09 |  4153 | 2000/10 |
|  222.50 |  9.27 | 0.15 |  6675 | 2000/11 |
|  198.48 |  8.27 | 0.14 |  6153 | 2000/12 |
|  225.81 |  9.41 | 0.16 |  7000 | 2001/01 |
|  231.54 |  9.65 | 0.16 |  6483 | 2001/02 |
|  251.42 | 10.48 | 0.17 |  7794 | 2001/03 |
|  363.10 | 15.13 | 0.25 | 10893 | 2001/04 |
|  376.77 | 15.70 | 0.26 | 11680 | 2001/05 |
|  468.17 | 19.51 | 0.33 | 14045 | 2001/06 |
|  574.26 | 23.93 | 0.40 | 17802 | 2001/07 |
|  655.61 | 27.32 | 0.46 | 20324 | 2001/08 |
|  624.93 | 26.04 | 0.43 | 18748 | 2001/09 |
|  757.52 | 31.56 | 0.53 | 23483 | 2001/10 |
|  882.40 | 36.77 | 0.61 | 26472 | 2001/11 |
| 1005.29 | 41.89 | 0.70 | 31164 | 2001/12 |
| 1172.45 | 48.85 | 0.81 | 36346 | 2002/01 |
| 1272.57 | 53.02 | 0.88 | 35632 | 2002/02 |
| 1185.52 | 49.40 | 0.82 | 36751 | 2002/03 |
| 1239.30 | 51.64 | 0.86 | 37179 | 2002/04 |
|   10.68 |  0.44 | 0.01 |   331 | 2002/05 |
+---------+-------+------+-------+---------+
27 rows in set (4.07 sec)

Zitat

| Babes Home | Doedel Broedel | 72 | 0 |

Code:

create table temp_forumstat as select username, count(*) as posts, forumid from post left join thread using(threadid) where 
post.dateline > 1017655200 and post.dateline < 1020160800 group by forumid, post.userid;
create table temp_forumstat2 as select forum.title, substring( max( concat(LPAD(posts,6,'0'),username)),7) as forum_0wner, 
0.00+LEFT(max( concat(LPAD(posts,6,'0'),username)),6) as posts from temp_forumstat left join forum using(forumid) group by 
temp_forumstat.forumid order by forum_0wner, forum.parentid, forum.displayorder; 
select temp_forumstat2.*, (moderator.userid is not null) as is_moderator from temp_forumstat2 left join user on (forum_0wner = username) left join forum on (temp_forumstat2.title = forum.title) left join moderator on (moderator.userid = user.userid and (moderator.forumid = forum.forumid or moderator.forumid = forum.parentid)); drop table temp_forumstat; drop table temp_forumstat2;

Zitat von .deRElict.

query für die forum0wnage (s.o.)

Code:

create table temp_forumstat as select username, count(*) as posts, forumid from post left join thread using(threadid) where 
post.dateline > 1017655200 and post.dateline < 1020160800 group by forumid, post.userid;
create table temp_forumstat2 as select forum.title, substring( max( concat(LPAD(posts,6,'0'),username)),7) as forum_0wner, 
0.00+LEFT(max( concat(LPAD(posts,6,'0'),username)),6) as posts from temp_forumstat left join forum using(forumid) group by 
temp_forumstat.forumid order by forum_0wner, forum.parentid, forum.displayorder; 
select temp_forumstat2.*, (moderator.userid is not null) as is_moderator from temp_forumstat2 left join user on (forum_0wner = username) left join forum on (temp_forumstat2.title = forum.title) left join moderator on (moderator.userid = user.userid and (moderator.forumid = forum.forumid or moderator.forumid = forum.parentid)); drop table temp_forumstat; drop table temp_forumstat2;

hav fun