MySQL select value in range where that value not in another range

Question

We have a table where we log every customer request, each request get's a created_at and customer_email column.

mysql> select * from requests;

+---------------------+-----------------------+
| created_at          | customer_email        |
+---------------------+-----------------------+
| 2016-08-08 14:05:50 | user1@example.com     |
| 2016-08-08 14:05:51 | user2@example.com     |
| 2017-08-15 14:41:40 | user3@example.com     |
| 2017-08-15 15:17:25 | user2@example.com     |
| 2018-08-15 15:30:41 | user1@example.com     |
+---------------------+-----------------------+

I'm trying to get records for customer email that was created_at in 08/2018 but didn't create a request before that. In other words, find out what customers that created requests in 08/2018 are new customers.
Here's the query i'm trying and it's returning 0

    SELECT DISTINCT
    customer_email
FROM
    requests
WHERE
    created_at > '2018-08-01'
        AND customer_email NOT IN (SELECT DISTINCT
            customer_email
        FROM
            requests
        WHERE
            created_at < '2018-08-01')

Any help would be much appreciated!

The query looks fine. Did you find any email manually which was newly created? — mallik1055, Aug 30 '18 at 14:26
it returns no results even though i know I have some new ones in there — Haim, Aug 30 '18 at 14:42

score 1 · Accepted Answer · answered Aug 30 '18 at 14:36

Try GROUP BY with HAVING clause.

Using MIN function, we can find out the first_created_at for a customer_email, and then utilizing HAVING clause, we restrict our result set to the ones having first_created_at after 08/18.

SELECT customer_email, DATE(MIN(created_at)) AS first_created_at 
FROM requests
GROUP BY customer_email 
HAVING first_created_at >= DATE('2018-08-01')

MySQL select value in range where that value not in another range

1 Answers1