CODEWITHSUNDEEP
mysqlsql
Ali Hassan
Ali Hassan

Reputation: 3

Can you tell me what's wrong in the query I'm using to retrieve info on when a customer last placed an order?

I'm trying to write a query on MySQL workbench that shows me when a customer last ordered from my client's website. I'm using two tables for this purpose. One is called "orders", which is updated with order time whenever a new order is placed and assigns a unique id to each new order. The other table is called "orders_customers_details", which is also updated whenever a new order is placed and contains the unique id (based on a combination of customer's email address and phone number) of the customer placing the order, as well as a key which corresponds to the orders table's id.

My problem is that the query I'm using is not returning every customer's most recent order. In the case of some customers, I'm being given the date of their fourth or fifth most recent order.

I'm left joining orders on orders_customer_details:

left join orders o
on ocd.id = o.customer_details_id

I've also tried using a left outer join, and the following join:

from orders o, orders_customers_details ocd
where o.customer_details_id = ocd.id

In order to retrieve the most recent order only, I'm grouping by customer_id HAVING max(order.id). FYI, order.id increases with the placement of each new order, so the order with the highest id is the most recent order.

I've also tried 

 SELECT customer_id, MAX(order.id)

and then grouped by customer_id, but to no avail.

Here's the entire code:

select customer_id, o.id as id_of_last_order, date(order_date) as 
last_ordered, timestampdiff(day, order_date, now()) as 
days_since_last_ordered
from orders o, orders_customers_details ocd
where o.customer_details_id = ocd.id
group by customer_id having max(o.id)
order by customer_id;

Upvotes: 0

Views: 41

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

A typical method in MySQL is to use a correlated subquery to get the most recent order:

select ocd.customer_id, o.id as id_of_last_order, 
       date(o.order_date) as last_ordered,
       timestampdiff(day, o.order_date, now()) as days_since_last_ordered
from orders o join
     orders_customers_details ocd
     on o.customer_details_id = ocd.id
where o.order_date = (select max(o2.order_date)
                      from orders o2 join
                           orders_customers_details ocd2
                           on o2.customer_details_id = ocd2.id
                      where oc2.customer_id = ocd.customer_id
                     )
order by ocd.customer_id;

Of course, if o.id is an auto-incrementing column, then the largest value is from the most recent date. If this is the case, then you can just use aggregation:

select ocd.customer_id,
       max(o.id) as id_of_last_order, 
       date(max(o.order_date)) as last_ordered,
       timestampdiff(day, max(o.order_date), now()) as days_since_last_ordered
from orders o join
     orders_customers_details ocd
     on o.customer_details_id = ocd.id
group by ocd.customer_id
order by ocd.customer_id;

In MySQL 8+, you would simply use window functions:

select *
from (select ocd.customer_id, o.id as id_of_last_order, 
             date(o.order_date) as last_ordered,
             timestampdiff(day, o.order_date, now()) as days_since_last_ordered,
             row_number() over (partition by ocd.customer_id order by o.order_date desc) as seqnum
      from orders o join
           orders_customers_details ocd
           on o.customer_details_id = ocd.id
     ) ocd
where seqnum = 1
order by ocd.customer_id;

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT 
customer_details_id customer_id, 
MAX(o.id) AS id_of_last_order, 
MAX(order_date) AS last_ordered, 
TIMESTAMPDIFF(DAY, MAX(order_date), NOW()) AS days_since_last_ordered
FROM orders O
INNER JOIN orders_customers_details OCD
    ON O.customer_details_id = OCD.id
GROUP BY customer_details_id 
ORDER BY customer_details_id;

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17655

Using a correlated sub query in the where clause to find the most recent order by customer and tidying up the code then something like this

select customer_id, o.id as id_of_last_order, date(order_date) as last_ordered, 
        timestampdiff(day, order_date, now()) as days_since_last_ordered
from orders_customers_details ocd
join orders o on  o.customer_details_id = ocd.id
where ocd_id = (select max(ocd_id) from orders_customers_details ocd1 where ocd1.customer_id = ocd_customer_id);

Though I cannot be certain without table definitions sample data etc..

Upvotes: 0

Related Questions