Selecting first records of a type in a given period
- by Emanuil Rusev
I have a database table that stores user comments:
comments(id, user_id, created_at)
I want to get from it the number of users that have commented for the first time in the past week.
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 these users.