Grouping records on consecutive dates

Question

If I have following table in Postgres:

order_dtls

Order_id    Order_date  Customer_name
-------------------------------------
1           11/09/17    Xyz
2           15/09/17    Lmn
3           12/09/17    Xyz
4           18/09/17    Abc
5           15/09/17    Xyz
6           25/09/17    Lmn
7           19/09/17    Abc

I want to retrieve such customer who has placed orders on 2 consecutive days. In above case Xyz and Abc customers should be returned by query as result.

So what is your actual problem? What have you tried? Where are you stuck? — Thorsten Kettner, Sep 22 '17 at 12:20
I am using Postgresql, my problem is how to apply group by clause on 2 dates which are consicutive — user3787163, Sep 22 '17 at 12:59
Your last comment made the requirement less clear - it might help if you could expand your sample data to illustrate what you mean by group by and what happens if a customer has events on 3,4(etc) consecutive days. — P.Salmon, Sep 22 '17 at 16:07

Erwin Brandstetter · Answer 1 · 2017-09-22T13:36:59.033

There are many ways to do this. Use an EXISTS semi-join followed by DISTINCT or GROUP BY, should be among the fastest.

Postgres syntax:

SELECT DISTINCT customer_name
FROM   order_dtls o
WHERE  EXISTS (
   SELEST 1 FROM order_dtls
   WHERE  customer_name = o.customer_name
   AND    order_date = o.order_date + 1  -- simple syntax for data type "date" in Postgres!
   );

If the table is big, be sure to have an index on (customer_name, order_date) to make it fast - index items in this order.

To clarify, since Oto happened to post almost the same solution a bit faster:

DISTINCT is an SQL construct, a syntax element, not a function. Do not use parentheses like ~~DISTINCT (customer_name)~~. Would be short for DISTINCT ROW(customer_name) - a row constructor unrelated to DISTINCT - and just noise for the simple case with a single expression, because Postgres removes the pointless row wrapper for a single element automatically. But if you wrap more than one expression like that, you get an actual row type - an anonymous record actually, since no row type is given. Most certainly not what you want.

What is a row constructor used for?

Also, don't confuse DISTINCT with DISTINCT ON (expr, ...). See:

Select first row in each GROUP BY group?

iLikeMySql · Answer 2 · 2017-09-22T14:02:24.937

0

This should work:

SELECT A.customer_name
    FROM order_dtls A
    INNER JOIN (SELECT customer_name, order_date FROM order_dtls) as B
    ON(A.customer_name = B.customer_name and Datediff(B.Order_date, A.Order_date) =1)
    group by A.customer_name

edited Sep 22 '17 at 14:02

answered Sep 22 '17 at 12:25

iLikeMySql

736
3
7

Datediff(B.order,A.order) should probably be Datediff(B.order_date,A.order_date) – P.Salmon Sep 22 '17 at 12:33

Efthymios Kalyviotis · Answer 3 · 2017-09-22T12:50:24.323

0

The way I would think of it doing it would be to join the table the date part with itselft on the next date and joining it with the Customer_name too. This way you can ensure that the same customer_name done an order on 2 consecutive days.

For MySQL:

SELECT distinct *
FROM order_dtls t1
    INNER JOIN order_dtls t2 on
            t1.Order_date = DATE_ADD(t2.Order_date, INTERVAL 1 DAY) and
            t1.Customer_name = t2.Customer_name

The result you should also select it with the Distinct keyword to ensure the same customer is not displayed more than 1 time.

edited Sep 22 '17 at 12:50

answered Sep 22 '17 at 12:31

Efthymios Kalyviotis

859
10
24

1

Neither mysql nor postgresql have a dateadd function. Mysql syntax would be DATE_ADD(t2.Order_date, interval 1 day) in which case solution produces expected result. – P.Salmon Sep 22 '17 at 12:40
You are totally right. I will modify the answer to include MySQL – Efthymios Kalyviotis Sep 22 '17 at 12:49

score 0 · Answer 4 · answered Sep 22 '17 at 12:32

Try something like...

SELECT `order_dtls`.*
FROM `order_dtls`
INNER JOIN `order_dtls` AS mirror
ON `order_dtls`.`Order_id` <> `mirror`.`Order_id`
AND `order_dtls`.`Customer_name` = `mirror`.`Customer_name`
AND DATEDIFF(`order_dtls`.`Order_date`, `mirror`.`Order_date`) = 1

score 0 · Answer 5 · answered Sep 22 '17 at 12:51

For postgresql:

select distinct(Customer_name) from your_table
where exists 
    (select 1 from your_table t1 
    where 
    Customer_name = your_table.Customer_name and Order_date = your_table.Order_date+1 )

Same for MySQL, just instead of your_table.Order_date+1 use: DATE_ADD(your_table.Order_date , INTERVAL 1 DAY)

Grouping records on consecutive dates

5 Answers5