Meego Wiki
Views

Metrics/Forum queries

From MeeGo wiki
< Metrics(Difference between revisions)
Jump to: navigation, search
Dneary (Talk | contribs)
(Created page with " The forum database structure could not be simpler, it mirrors precisely [http://forum.meego.com/stats/ the CVS files which are generated every month]. === Forum posts per forum...")

Latest revision as of 18:32, 14 October 2011

The forum database structure could not be simpler, it mirrors precisely the CVS files which are generated every month.

Contents

Forum posts per forum by month 


SELECT
     `forum_posts`.`month` as month,
     `forum_posts`.`year` as year,
     date_format(date_add( makedate(year, 1), interval month-1 MONTH), '%b %Y') as monthstr, 
     concat(cast(year as char), LPAD(cast(month as char), 2, '0')) as monthnum,
     `forum_posts`.`forum` as forum,
     `forum_posts`.`posts` as posts
FROM
     `forum_posts`
ORDER BY
     `forum_posts`.`forum` ASC,
     `forum_posts`.`year` ASC,
     `forum_posts`.`month` ASC

Forum top 20 posters last month 


SELECT
     `forum_top_posters`.`month`,
     `forum_top_posters`.`year`,
     `forum_top_posters`.`rank`,
     `forum_top_posters`.`member`,
     `forum_top_posters`.`posts`,
     monthname(${last_month}) AS monthname
FROM
     `forum_top_posters`
WHERE
     month = month(${last_month})
 AND year = year(${last_month})
 limit 20

Forum most viewed threads last month 


SELECT
     month,
     year,
     rank,
     title,
     views
FROM
     `forum_most_viewed_threads`
WHERE
     month=MONTH(${last_month})
     AND
     year=YEAR(${last_month})
ORDER BY
     views ASC

Forum posts per forum cumulative 


SELECT
     `forum_cumulative_posts`.`month`,
     `forum_cumulative_posts`.`year`,
     `forum_cumulative_posts`.`forum`,
     `forum_cumulative_posts`.`posts`
FROM
     `forum_cumulative_posts`
ORDER BY
`forum_cumulative_posts`.`forum`,
     `forum_cumulative_posts`.`year` ASC,
     `forum_cumulative_posts`.`month`

Forum top thanks last month 


SELECT
     `forum_top_thanked`.`month`,
     `forum_top_thanked`.`year`,
     `forum_top_thanked`.`rank`,
     `forum_top_thanked`.`member`,
     `forum_top_thanked`.`thanks`
FROM
     `forum_top_thanked`
 WHERE
     month = month(${last_month})
 AND year = year(${last_month})
order by rank
limit 20


Forum threads per forum cumulative 


SELECT
     `forum_cumulative_threads`.`month`,
     `forum_cumulative_threads`.`year`,
     date_format(date_add( makedate(year, 1), interval month-1 MONTH), '%b %Y') as monthstr, 
     concat(cast(year as char), LPAD(cast(month as char), 2, '0')) as monthnum,
     `forum_cumulative_threads`.`forum`,
     `forum_cumulative_threads`.`threads`
FROM
     `forum_cumulative_threads`
ORDER BY
     `forum_cumulative_threads`.`forum` ASC,
     `forum_cumulative_threads`.`year` ASC,
     `forum_cumulative_threads`.`month` ASC


Forum top 10 posters last month 


SELECT
     `forum_top_posters`.`month`,
     `forum_top_posters`.`year`,
     `forum_top_posters`.`rank`,
     `forum_top_posters`.`member`,
     `forum_top_posters`.`posts`,
     monthname(${last_month}) AS monthname
FROM
     `forum_top_posters`
WHERE
     month = month(${last_month})
 AND year = year(${last_month})
 limit 10


Forum posts per forum last 4 months 


SELECT
     `forum_posts`.`month` as month,
     `forum_posts`.`year` as year,
     date_format(date_add( makedate(year, 1), interval month-1 MONTH), '%b %Y') as monthstr, 
     concat(cast(year as char), LPAD(cast(month as char), 2, '0')) as monthnum,
     `forum_posts`.`forum` as forum,
     `forum_posts`.`posts` as posts
FROM
     `forum_posts`
where
	month>=MONTH(DATE_SUB(NOW(), INTERVAL 4 MONTH)) and
	year>=YEAR(DATE_SUB(NOW(), INTERVAL 4 MONTH))
ORDER BY
     `forum_posts`.`forum` ASC,
     monthnum ASC


Forum repeat posters last month 


SELECT
     count(`forum_top_posters`.`member`) as active_count,
     monthname(${last_month}) AS monthname
FROM
     `forum_top_posters`
WHERE
     month = month(${last_month})
 AND year = year(${last_month})
 AND `forum_top_posters`.`posts`>=2

Forum posters last month 


SELECT
     count(`forum_top_posters`.`member`) as poster_count,
     monthname(${last_month}) AS monthname
FROM
     `forum_top_posters`
WHERE
     month = month(${last_month})
 AND year = year(${last_month})

Forum total posts last month 


select
     sum(posts) as total_posts
FROM
     forum_posts
WHERE
     forum_posts.month = MONTH(${last_month})
AND
     forum_posts.year = YEAR(${last_month})

Forum hottest threads last month 


SELECT
     `forum_hottest_threads`.`month`,
     `forum_hottest_threads`.`year`,
     `forum_hottest_threads`.`rank`,
     `forum_hottest_threads`.`title`,
     `forum_hottest_threads`.`posts`
FROM
     `forum_hottest_threads`
WHERE
     `forum_hottest_threads`.`month`=month(${last_month})
     AND
     `forum_hottest_threads`.`year`=year(${last_month})
ORDER BY
     `forum_hottest_threads`.`rank` ASC
LIMIT 10
Retrieved from "http://wiki.meego.com/Metrics/Forum_queries"
Personal tools