Announcement

Collapse
No announcement yet.

SQL query help needed

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL query help needed

    I extracted number of users who have registerd on certain dates:


    Code:
    SELECT count(*) FROM `user` 
    WHERE `joindate` >= UNIX_TIMESTAMP('2010-11-15 00:00:00')
    AND `joindate` <UNIX_TIMESTAMP('2010-11-22 00:00:00')
    AND `usergroupid` = 2
    Now I want to check how many of these new users wrote a post within next 24 or 48 hours after registration, any idea how to do this? Probably some left join with post table ?

    And one more question, what is 'dateline' in thread table?
    Is this time when the first post was submited, or maybe last replay time ?

  • #2
    thread.dateline is the first post time or thread creation time. thread.lastpost is the time of the last post.

    I'll let someone else tackle the query question since I'm not very good at those.

    Please don't PM or VM me for support - I only help out in the threads.
    vBulletin Manual & vBulletin 4.0 Code Documentation (API)
    Want help modifying your vbulletin forum? Head on over to vbulletin.org
    If I post CSS and you don't know where it goes, throw it into the additional.css template.

    W3Schools &lt;- awesome site for html/css help

    Comment


    • #3
      Try this...
      Code:
      SELECT user.userid, user.username, FROM_UNIXTIME(post.dateline) post_date, post.postid  
      FROM user, post 
      WHERE joindate >= UNIX_TIMESTAMP('2010-11-15 00:00:00')
      AND joindate <UNIX_TIMESTAMP('2010-11-22 00:00:00')
      AND (post.dateline - user.joindate) < 172800
      AND usergroupid = 2
      GROUP BY
      user.userid
      Lats...

      Comment

      widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
      Working...
      X