Selecting first records of a type in a given period

Question

I have a database table that stores user comments:

comments(id, user_id, created_at)

From that table, I want to get the number of users that have commented for the first time in the past 7 days.

---

Here's what I have so far:

SELECT COUNT(DISTINCT `user_id`) 
FROM `comments` 
WHERE `created_at` BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()

This would give the number of users that have commented, but it would not take into consideration whether these comments are first for their users.

Answer 1

SELECT COUNT(DISTINCT user_id)
FROM comments AS c1
WHERE c1.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
AND NOT EXISTS (SELECT 1 FROM comments AS c2 
                WHERE c2.user_id = c1.user_id AND c2.created_at < c1.created_at)

The NOT EXISTS clause checks whether the same user_id has a record with an earlier created_at time. If so, it means this is not the first time they are commenting, and thus we should discount this record.

I have kept DISTINCT user_id because it is possible two comments are created at the same time. You could also try the following instead, which only gets the very first record for each user, so you can do away with the DISTINCT, but I don't know which would be more optimal:

SELECT COUNT(*)
FROM comments AS c1
WHERE c1.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
AND NOT EXISTS (SELECT 1 FROM comments AS c2 
                WHERE c2.user_id = c1.user_id 
                AND (c2.created_at < c1.created_at 
                    OR (c2.created_at = c1.created_at AND c2.id < c1.id)))

Answer 2

SELECT COUNT(DISTINCT `user_id`) 
FROM comments c1
WHERE created_at BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
AND NOT EXISTS 
   (SELECT NULL 
   FROM comments c2 
   where c1.user_id = c2.user_id
   AND c2.create_at < DATE_SUB(NOW(), INTERVAL 7 DAY));