Selecting Users signed up in past 30 days and in past 7 days in one efficient query

Question

I have this query that selects the number of user signups in the past 30 days:

SELECT
  COUNT(*) AS UserCount30
FROM
  User
WHERE
  User.UserDateCreated > (CURDATE() - INTERVAL 30 DAY)

Then I have this query that selects the number of users that signed up in the past 7 days

SELECT
  COUNT(*) AS UserCount7
FROM
  User
WHERE
  User.UserDateCreated > (CURDATE() - INTERVAL 7 DAY)

Ideally, these are all going to be part of one larger query. How could I get both of these values in one efficient query that preferably does not use subqueries.

Answer 1

Why not use subqueries? Because of 'all subqueries are slow' myth?

Here

SELECT
 u1.cnt AS UserCount30,
 u2.cnt AS UserCount7
FROM (SELECT
  COUNT(*) AS cnt
FROM
  User
WHERE
  User.UserDateCreated > (CURDATE() - INTERVAL 30 DAY)
) AS c1
CROSS JOIN (SELECT
  COUNT(*) AS cnt
FROM
  User
WHERE
  User.UserDateCreated > (CURDATE() - INTERVAL 7 DAY)
) AS c2

Answer 2

Do the pull for 30 days and count(*) on that. Then do a sum and as the expr in sum have an if statement return a 1 if its within 7 days or 0 if it isn't.

SELECT COUNT(*) AS UserCount30, 
       SUM(if((CURDATE() - INTERVAL 7 DAY) < User.UserDateCreated) 1 else 0)
  FROM USER
 WHERE User.UserDateCreated > (CURDATE() - INTERVAL 30 DAY)

Answer 3

SELECT 
   SUM(if((CURDATE() - INTERVAL 30 DAY)< User.UserDateCreated) 1 else 0) AS Last30, 
   SUM(if((CURDATE() - INTERVAL 7 DAY)< User.UserDateCreated) 1 else 0) AS Last7
FROM 
   USER

I liked coffeepac's answer, but changed just a little (easier to read / understand what it's doing this way, IMHO).

I'm not a mySQL guy, but one thing I'd be careful of is if UserDateCreated is a "date/time" field like Sql-Server uses. In that case, it's better to strip the time off before you do a logical (<,>,=,...) comparrision.