1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Need help with a query

Discussion in 'Troubleshooting and Problems' started by cmeinck, Sep 13, 2012.

  1. cmeinck

    cmeinck Well-Known Member

    I need to run a query that will generate a list of members who had [x] or greater posts within a specific timeframe? I remember seeing something months back, but cannot seem to locate it.

    Any help would be greatly appreciated.

  2. trilogy33

    trilogy33 Well-Known Member

  3. cmeinck

    cmeinck Well-Known Member

  4. borbole

    borbole Well-Known Member

    Try this query:

    SELECT u.username,
    COUNT(*) AS monthlypostsers
    FROM xf_post p
    LEFT JOIN xf_user u ON (p.user_id=u.user_id)
    WHERE p.post_date BETWEEN UNIX_TIMESTAMP('2012-08-14 00:00:00') AND UNIX_TIMESTAMP('2012-09-14 00:00:00')
    GROUP BY p.user_id
    ORDER BY monthlypostsers DESC;

    This will return a list of all the users who have posted for a month starting from 14 of last month until today. To change the date at the query above you will have to edit the time in this part:

    BETWEEN UNIX_TIMESTAMP('2012-08-14 00:00:00') AND UNIX_TIMESTAMP('2012-09-14 00:00:00')
    Hope it helps.
    Jake Bunce likes this.
  5. cmeinck

    cmeinck Well-Known Member

    This worked out perfectly. Thanks once again for the assistance. You're the query master!
    borbole likes this.
  6. borbole

    borbole Well-Known Member

    I am far from a query master but you are welcome :)

Share This Page