0

Let's suppose I have two tables: customers and orders

Orders have these columns:

order_created
customer_id

Customers have these columns:

id
name
email

I am trying to build a query to show me the average range of the latest customer orders, for example:

  • get two latest orders from all customers (select only customers that have more than 1 order, of course)
  • compare the dates and retrieve the average time from o1 to o2 (all customers)

I am not very experienced with MySQL, but so far I have managed to get the max value.

select max(o.order_created), c.id, c.name, c.email, 
(date(max(o.order_created)) - date(min(o.order_created))) as date_interval
from orders o
inner join customers c
on c.id = o.customer_id
group by c.id
having date_interval > 0

min doesn`t work, of course, because I need to retrieve the second max value, not the minimum. And after all that, it needs to retrieve an average value for all this query, which I have no clue how to build.

How can I approach this?

Thanks everybody.

@Edit I don`t think the duplicate mark solve my question

Henrique M.
  • 512
  • 2
  • 14
  • 1
    The column names in your query don't match the ones you listed above. – jarlh Oct 13 '16 at 13:56
  • Sorry for that. I've updated – Henrique M. Oct 13 '16 at 14:18
  • this boils down to a top-n-per-group query, once you strip off the averaging and whatnot – Marc B Oct 13 '16 at 14:19
  • 1
    Sorry @MarcB But I dont see which answer there solve this only see a lot of link (most external) and even then still will need solve how to calculate the date difference. – Juan Carlos Oropeza Oct 13 '16 at 14:31
  • the date difference is basically trivial. the hard part is getting those top-2 records, which isn't directly possible in mysql, since limits aren't supported in subqueries. – Marc B Oct 13 '16 at 14:33
  • 1
    @MarcB Sorry but I disagree, because not sure if will be trivial for OP level. You can look vp answer ... is very close to solve the problem and is very far from a basic query. – Juan Carlos Oropeza Oct 13 '16 at 14:44
  • @MarcB Unfortunately I disagree. I tried making and remaking several similar queries outside and inside SO and nothing could help. Happily, vkp is very close to what I desire and i think it is starting making sense. I ask you to please remove the duplicate flag. Juan, I agree with you. Thanks – Henrique M. Oct 13 '16 at 14:55

1 Answers1

2

One way to do it is to calculate the row numbers where the latest order per customer gets row number 1 and then calculate the date difference between the latest and the next latest order (for those customers who have atleast 2 orders)

select t1.customer_id,
datediff(max(case when rownum=1 then order_date end) ,
         max(case when rownum=2 then order_date end)
        )/2.0 diff
from (select o.*,
      @rn:=if(@previous=customer_id,@rn,0) + 1 as rownum,
      @previous:=customer_id
      from orders o, (select @rn:= 0, @previous:= null) t
      order by customer_id,order_date desc) t1
join (select customer_id from orders group by customer_id having count(*) > 1) t2 
on t1.customer_id=t2.customer_id
group by t1.customer_id
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58