How to query with multiple conditions that won't match on just a single row

user_4953738 0
Viewed 61

I provided a simplified sample of the table I'm currently working with here: http://sqlfiddle.com/#!17/c57e33/1

CREATE TABLE orders
    (id SERIAL PRIMARY KEY,
     customer_id INT, 
     created_at DATE)
;

INSERT INTO orders
    (customer_id, created_at)
VALUES
    (1, '2019-10-09'),
    (1, '2019-10-01'),
    (1, '2019-08-09'),
    (2, '2019-10-09'),
    (2, '2019-10-09'),
    (3, '2019-09-09'),
    (3, '2019-08-09'),
    (4, '2019-08-09'),
    (4, '2019-08-09'),
    (5, '2019-10-09'),
    (5, '2019-10-09'),
    (5, '2019-08-09')
;

My problem is that I want to return only the customers with one or more orders on two specific dates and no orders in between. In the fiddle only the customer_id of 5 should match.

After trying many different ways of queries I finally found a way that works using a bunch of EXISTS clauses:

SELECT DISTINCT(customer_id) 
FROM orders o1
WHERE EXISTS (SELECT 1 FROM orders o2 WHERE o1.customer_id = o2.customer_id AND o2.created_at = '2019-10-09')
AND EXISTS (SELECT 1 FROM orders o2 WHERE o1.customer_id = o2.customer_id AND o2.created_at = '2019-08-09')
AND NOT EXISTS (SELECT 1 FROM orders o2 WHERE o1.customer_id = o2.customer_id AND o2.created_at BETWEEN '2019-08-10' AND '2019-10-08')

But I am wondering if there is an easier and more efficient way to do this.

postgresql sql
3 Answers

You can use conditional aggregation:

SELECT customer_id
FROM orders
WHERE created_at BETWEEN '2019-08-09' AND '2019-10-09'
GROUP BY customer_id
HAVING MIN(created_at) = '2019-08-09'
AND    MAX(created_at) = '2019-10-09'
AND    COUNT(DISTINCT created_at) = 2

The HAVING clause will filter the groups with matching condition.

user_870150

You can GROUP BY customer_id and set the conditions in the HAVING clause:

SELECT customer_id 
FROM orders
GROUP BY customer_id
HAVING 
    SUM((created_at = '2019-10-09')::int) > 0
  AND 
    SUM((created_at = '2019-08-09')::int) > 0
  AND 
    SUM((created_at BETWEEN '2019-08-10' AND '2019-10-08')::int) = 0

See the demo.
Results:

| customer_id |
| ----------- |
| 5           |
user_104988280

Postgres has nice boolean aggregate functions:

select customer_id
from orders
where created_at between '2019-08-09' and '2019-10-09'
group by customer_id
having bool_or(created_at = '2019-10-09')
and bool_or(created_at = '2019-08-09')
and not bool_or(created_at between '2019-08-10' and '2019-10-08')

The query should be a bit or significantly faster than yours depending on your actual data.

user_19957380
Related