i have a table with customers. every customers has several orders with the paid price and day he/she ordered an item. i want only customers their first order was after a certain date
orders table looks like this:
x
id | customer | item | price | date
--------------------------------------------
1 | a | a2 | 50 | 2018-07-03
2 | b | a5 | 30 | 2019-12-06
3 | c | a3 | 20 | 2020-01-14
4 | a | a2 | 23 | 2017-07-12
5 | f | a1 | 34 | 2018-10-03
6 | c | a1 | 90 | 2018-03-03
7 | b | a2 | 56 | 2020-02-03
8 | a | a2 | 52 | 2019-05-03
SELECT customer FROM orders WHERE min(date) > TO_DATE('2018-09-01', 'YYYY-MM-DD')
i want the customer only when the first order was after 2018-09-01, the min(date) > TO_DATE(‘2018-09-01’, ‘YYYY-MM-DD’)
result should be customers: b f
Advertisement
Answer
Use aggregation:
SELECT customer
FROM orders o
GROUP BY customer
HAVING min(date) > DATE '2018-09-01';
You don’t need to convert the date to a string for comparisons. Depending on your database, DATE
may not be required.