CODEWITHSUNDEEP
sql-serverdatabase
Andy
Andy

Reputation: 2762

Gathering site user stats

How do people usually gather site statistics about the number of users registered or number of messages left (assuming the site is a forum) - the totals, today, yesterday, last week? is this about running Count() on the user and message table every hour/day/week or there is a smarter incremental approach?

Upvotes: 2

Views: 73

Answers (1)

Steve
Steve

Reputation: 1636

Sure, you log all the activities with a date timestamp, then query against those records where the date is between the two dates you want to see. If this is an existing project, although, you might not already have all this data, in which case you are pretty much stuck.

If you are making this, though, it is common practice to do it like this:

Lets say you have a table for your messages in a forum. You could store each post in the database like this:

CREATE TABLE forum_post
   (
    thread_posted_to_id int,
    user_id int,
    message text,
    date_posted datetime DEFAULT (getdate())
   )

CREATE TABLE users
   (
    user_id int,
    name text,
    date_signed_up datetime DEFAULT (getdate())
   )

Then you could just query against that like this to get the count of all posts from a certain user:

select count(*) from forum_post where user_id = ?

Or if you wanted only dates in a range, you could say 

select count(*) from forum_post where user_id = ? and date_posted between ? and ?

Filling in the question marks with the data you want to select.

Upvotes: 3

Related Questions