Date Differences from different outputs

Question

I try to find out how long it takes till a customer orders again in my eshop.

Name of the list eshop_flat_sales_order

customer_email created_at status
------------------------------------
a(at)a.com     12.1.10    complete
b(at)a.com     14.2.10    cancelled
c(at)a.com     16.1.10    complete
a(at)a.com     18.1.10    complete
c(at)a.com     18.1.10    complete
b(at)a.com     20.1.10    complete

With the query

SELECT *
FROM eshop_flat_sales_order
ORDER BY customer_email

Ill get all the emails with the date in orders. Like this:

customer_email created_at status
------------------------------------
a(at)a.com     12.1.10    complete
a(at)a.com     18.1.10    complete
b(at)a.com     14.2.10    cancelled
b(at)a.com     20.1.10    complete
c(at)a.com     16.1.10    complete
c(at)a.com     18.1.10    complete

Now it would be great to get a query which tells me how long it took a(at)a.com to order again. In the example it would be 6 days. For c(at)a.com it would be 2 days. Then in the end I need the average of all this dates but I should manage that :)

Thanks so much for the answers

Answer 1

You would probably be best to do it with SQL Variables... so you can always use whatever the last person was and their corresponding date of activity...

SELECT 
      PreSort.customer_email, 
      @lastDate as LastOrderDate,
      PreSort.Created_At,
      if( @lastCust = PreSort.customer_email, 
         datediff( @lastDate, PreSort.Created_At ), null ) as DaysDiff,
      @lastDate := PreSort.Created_At as PreserveDate,
      @lastCust := PreSort.customer_email as PreserveCustomer
   FROM 
      ( select efso.customer_email,
               efso.created_at 
            from eshop_flat_sales_order AS efso
            where efso.status = 'complete'
            order by efso.customer_email,
                     efso.created_at ) PreSort,
      (SELECT @lastCust := '', @lastDate := null ) AS SqlVars

Now, you are looking for last time BETWEEN ACTUAL Orders... so you have the status of "complete" or "cancelled" vs just visiting your site and ATTEMPTING an order..

should create a result cursor something like

customer_email LastOrderDate created_at DaysDiff   PreserveDate PreserveCustomer
-------------- ------------- ---------- ---------- ------------ ----------------
a(at)a.com     NULL          12.1.10    NULL       12.1.10      a(at)a.com
a(at)a.com     12.1.10       18.1.10    6          18.1.10      a(at)a.com
b(at)a.com     NULL          20.1.10    NULL       20.1.10      b(at)a.com
c(at)a.com     NULL          16.1.10    NULL       16.1.10      c(at)a.com
c(at)a.com     16.1.10       18.1.10    2          18.1.10      c(at)a.com

Now, if you want averages, you can roll THESE records up and get average from DaysDiff. However, you would probably want to apply WHERE to remove the "NULL" entries that indicate the FIRST order for a person and just leave those that specifically HAD a second order. You could even apply GROUPING values for first date, last date, total orders too.

Answer 2

EDIT: this query will give you the customer, the last order and the previous order. Check if it reflects what you need. If yes, just do a datediff on the columns:

with last_order as
(
    select customer_email, max(created_at) as max_order, max (ID) as max_id
    from eshop_flat_sales_order 
    where status='complete'
    group by customer_email
)
Select customer_email, MAX_ORDER, 
(select top 1 created_at 
 from eshop_flat_sales_order o 
 where customer_email=lo.customer_email and ID!= lo.max_id and status='complete'
 order by created_at desc) as PREVIOUS_ORDER
from last_order LO  

Do you always have 2 records per customer? If yes, try this:

select customer_email, datediff(day, min(created_at),max(created_at))
from eshop_flat_sales_order
where status='complete'
group by customer_email
having count(*)=2

Answer 3

Here's a query to return the latest gap, in days, for each customer, using JOIN:

SELECT e1.customer_email, DATEDIFF(e1.created_at, e2.created_at) AS gap
FROM eshop_flat_sales_order e1
LEFT JOIN eshop_flat_sales_order e2
  ON e2.customer_email = e1.customer_email
  AND e2.created_at < e1.created_at
LEFT JOIN eshop_flat_sales_order e3
  ON e3.customer_email = e1.customer_email
  AND e3.created_at < e2.created_at
WHERE e3.customer_email IS NULL
ORDER BY e1.customer_email

This query assumes that created_at is a DATE field.

It will return NULL for gap where a customer only has one order. If you don't want to return results for customers with only one order, change the first join from LEFT JOIN to JOIN.

Here's another version that only considers complete orders:

SELECT e1.customer_email, DATEDIFF(e1.created_at, e2.created_at) AS gap
FROM eshop_flat_sales_order e1
LEFT JOIN eshop_flat_sales_order e2
  ON e2.customer_email = e1.customer_email
  AND e2.created_at < e1.created_at
  AND e2.status = 'complete'
LEFT JOIN eshop_flat_sales_order e3
  ON e3.customer_email = e1.customer_email
  AND e3.created_at < e2.created_at
  AND e3.status = 'complete'
WHERE e1.status = 'complete'
  AND e3.customer_email IS NULL
ORDER BY e1.customer_email

This would show all the gaps between all the corresponding orders, for all the customers:

SELECT e1.customer_email, DATEDIFF(e1.created_at, e2.created_at) AS gap
FROM eshop_flat_sales_order e1
JOIN eshop_flat_sales_order e2
  ON e2.customer_email = e1.customer_email
  AND e2.created_at < e1.created_at
  AND e2.status = 'complete'
LEFT JOIN eshop_flat_sales_order e3
  ON e3.customer_email = e1.customer_email
  AND e3.created_at < e1.created_at
  AND e3.created_at > e2.created_at
  AND e3.status = 'complete'
WHERE e1.status = 'complete'
  AND e3.customer_email IS NULL
ORDER BY e1.customer_email ASC, e1.created_at DESC

This would show the average gaps for each customer:

SELECT e1.customer_email, AVG(DATEDIFF(e1.created_at, e2.created_at)) AS gap
FROM eshop_flat_sales_order e1
JOIN eshop_flat_sales_order e2
  ON e2.customer_email = e1.customer_email
  AND e2.created_at < e1.created_at
  AND e2.status = 'complete'
LEFT JOIN eshop_flat_sales_order e3
  ON e3.customer_email = e1.customer_email
  AND e3.created_at < e1.created_at
  AND e3.created_at > e2.created_at
  AND e3.status = 'complete'
WHERE e1.status = 'complete'
  AND e3.customer_email IS NULL
GROUP BY e1.customer_email

Answer 4

To get the result as requested, try the following:

SELECT
    customer_email,
    DATEDIFF(MAX(date), MIN(date))
FROM eshop_flat_sales_order
GROUP BY customer_email