Print

[Selroth]

Just playing with the database some. 

Top 20 members by summed characters across all their posts (those who have typed the most into the forums):

Code: [Select]

SELECT t.name, SUM( t.myCount ) AS total_count
FROM (
SELECT poster_name AS name, LENGTH( body ) AS myCount
FROM smf_messages
)t

GROUP BY t.name
ORDER BY total_count DESC
LIMIT 0, 20
(Thank you Kiantis for the code)

atomicdragon   2518333
Selroth   2368896
Rossenod   2254532
Redalgo   2100962
Aurelia   1814531
Airy   1772093
JKarrah   1424375
Rocangus   1410023
Dradolan   1343164
Nezdragon   1297933
Vellos   1265077
Desren_Shemahlen   782468
sillydraco   770964
Zucadragon   711389
Cerapter   676369
Lunarya   657165
Karnanyd   594702
fossi   594185
Canvasian   540186
aaarhus   525242

Anyone have any other queries they'd like to run?  I'm trying to figure out how to figure the average length of posts for members who have made more than 10 posts.  I should learn more on how to use SQL =P

(BTW, we should break 100,000posts this month =P)

[Kiantis]

Well a trick to know how much words in there are... is to count for how much spaces are present in the clob.
Still not sure if theres a count occurences of a character function in mysql tho.

[ImadNemeir]

too bad most of these guys are no longer active>.>

congratulation selroth for the highest remaining user till now

[Nezdragon]

The average and median ages of all members. Preferably the ones with more than ten posts and/or the ones that have posted in the last, say, year. :>

[smotang]

redalgo is still very active right?

[Selroth]

The average and median ages of all members. Preferably the ones with more than ten posts and/or the ones that have posted in the last, say, year. :>

Dont suppose I can get that question in mySQL format, could I?  I'm not very pro with it yet.

table: smf_members

Related variables:

posts (mediumint 8)
last_login (int 10)
birthdate (date) - you'll want to exclude bogus dates.  Default birthday is 01-01-0001

[Canvasian]

Neat! I wouldn't have expected to actually show up on that list xP I guess I've posted here more than I thought. But I guess the posts that I do (did? sorry if I kind of disappeared xD) make are typically pretty long xD

[Perforex]

Neat! I wouldn't have expected to actually show up on that list xP I guess I've posted here more than I thought. But I guess the posts that I do (did? sorry if I kind of disappeared xD) make are typically pretty long xD

Your posts are usually 2 word pages or more x3

[Kiantis]

AVG should be an aggregate function for averages.
Since it's an aggregate then, still used in a select when having a 'group by'. Or else you'll get a single record with all's AVG.

[Kiantis]

Mmh, did some tries and came out with this:

select avg(age) from
(
    select
        m.id_member as id,
        datediff(now(), m.birthdate)/365 as age
    from
        smf_messages msg
        left join smf_members m on m.id_member = m.id_member
    where
        year(m.birthdate) > 1
        and msg.poster_time > [timestamp of a year ago]
    group by m.id_member
    having count(msg.id_msg) > [number of posts minimum]
) users

Should go for agerage.
For median... i think it needs to be calculated by hand... and i'll have to refresh my mind in how to calculate it, too.

Note: the poster_time is an int. SMF says it's a timestamp and has a function to convert it... but i didn't get how to actually determine how it actually does it (mmh, and it seems quite an antipattern to not use a datetime/timestamp field!)

[Selroth]

Mmh, did some tries and came out with this:

select avg(age) from
(
    select
        m.id_member as id,
        datediff(now(), m.birthdate)/365 as age
    from
        smf_messages msg
        left join smf_members m on m.id_member = m.id_member
    where
        year(m.birthdate) > 1
        and msg.poster_time > [timestamp of a year ago]
    group by m.id_member
    having count(msg.id_msg) > [number of posts minimum]
) users

Should go for agerage.
For median... i think it needs to be calculated by hand... and i'll have to refresh my mind in how to calculate it, too.

Note: the poster_time is an int. SMF says it's a timestamp and has a function to convert it... but i didn't get how to actually determine how it actually does it (mmh, and it seems quite an antipattern to not use a datetime/timestamp field!)

Your code gave odd numbers and such.  I didn't like DateDiff seem to be behaving, and figured to use your code to just give the average birth year.

Code: [Select]

SELECT AVG( age ) FROM (
SELECT m.id_member AS id, YEAR(m.birthdate ) AS age
FROM smf_messages msg
LEFT JOIN smf_members m ON m.id_member = m.id_member
WHERE YEAR( m.birthdate ) > 1900
AND msg.poster_time > 1270597987
GROUP BY m.id_member
HAVING COUNT( msg.id_msg ) >10
)users
Which is:
1988.3520

The average age of the forum for members who have posted this year, with a total post count of more than 10, is 23.  I'm surprised O_o

Changing some variables around yield the exact same result.  Something's not working.

Code: [Select]

SELECT AVG( age ) FROM (
SELECT YEAR(birthdate) AS age
FROM smf_members
WHERE YEAR( birthdate ) > 10
)users
Yields:
1988.3520

[Emien]

Try finding the top 10 most active members. 

[ImadNemeir]

Try finding the top 10 most active members. 

yeah who are the 10 most actif member this year 

i don't care about it usually but like the post name suggested, just for fun

[Alastago]

Try finding the top 10 most active members. 

What defines activity?

Most time spent online? I win, my firefox refreshes the website every 5 minutes, and my browser is on all the time
Most posts? ...HOW DARE YOU!

[ImadNemeir]

we have no doubt that you'll win alastago

activity: number of post with relation of time spent online

wish we have a count on the IRC or the shout box that would expose active members even more

[Tags:]

Tags: statistics draconity.org