How to nest select statements in SQL?

Question

I'm trying to select only the User_IDs that are in the first select statement and the last select statement, but I am doing something wrong.

SELECT COUNT(DISTINCT User_ID) AS total
FROM UserClicks
WHERE (Date > :startDate AND Date < :endDate) 
    AND User_ID IN (
        SELECT User_ID 
        FROM UserClicks
        WHERE (Date > :monthBeforeStartDate AND Date < :startDate)
    )

Update: This is for a MySQL database.

Answer 1

if your query is hanging, it's because of indexing, to optimize the outer query and inner query, you'd want indexes like so:

create index du on UserClicks (Date, User_ID)
create index ud on UserClicks (User_ID, Date)

it should then return instantly. more details on how mysql optimizes sub queries specified with the IN operator:

http://dev.mysql.com/doc/refman/5.1/en/optimizing-subqueries.html

MySQL replaces subqueries of the following form with an index-lookup function, which EXPLAIN describes as a special join type (unique_subquery or index_subquery):

... IN (SELECT indexed_column FROM single_table ...)

Answer 2

SELECT COUNT(DISTINCT User_ID) AS total
    FROM UserClicks
    WHERE (Date BETWEEN startDate AND endDate) AND User_ID IN ( 
        SELECT User_ID 
        FROM UserClicks
        WHERE (Date BETWEEN monthBeforeStartDate AND startDate)
    ) GROUP BY User_ID 

Answer 3

Sub-query and join will suit you here

SELECT DISTINCT u1.UserID 
FROM
  UserClicks u1
INNER JOIN
(
   SELECT 
      UserID 
   FROM 
     UserClicks 
   WHERE 
     (Date > :monthBeforeStartDate AND Date < :startDate)
) u2
ON u1.UserID = u2.UserID
WHERE
  (u1.Date > :startDate AND u1.Date < :endDate)

Answer 4

I'm curious... why don't you just have a more detailed where statement?

SELECT COUNT(DISTINCT User_ID) AS total
FROM UserClicks
WHERE (Date > :startDate AND Date < :endDate)
AND (Date > :monthBeforeStartDate AND Date < :startDate)

Update:
It seems like you want a count of the number of users who clicked between startDate and endDate who also clicked in the month before the startDate.

Depending on if your dates are in a MySQL-friendly format (like YYYY-MM-DD HH:MM:SS), then you can just use MySQL to get the month prior to the start date. Example below:

SELECT COUNT(DISTINCT User_ID) AS total
FROM UserClicks
WHERE (Date > :startDate AND Date < :endDate)
AND (Date > DATE_SUB(:startDate, INTERVAL 1 MONTH) AND Date < :startDate)

Answer 5

I've had a hard time with sub-queries in mysql in which both queries use the same table. My solution has always been to use a join

SELECT COUNT(DISTINCT User_ID) AS total
FROM UserClick u1 
    JOIN UserClick u2 
        on u2.User_ID = u1.User_ID
WHERE u1.Date BETWEEN :startDate AND :endDate
    AND u2.Date BETWEEN :monthBeforeStartDate and :startDate