user1940212 user1940212 - 1 year ago 92
MySQL Question

Query to get list of customers whose orders are less than the previous order

I am trying to write a sql to get the list of customers whose total ordered units is consistently lesser than the previous order. As in Total Qty in nth order is less than Total qty in n-1 th order, etc

SQL to create and populate table

create table orders (order_id int, customer_id varchar(5), order_date date, product_id varchar(5), quantity int);

Insert into orders values(01,'C1','2000-01-01','P1',10);
Insert into orders values(02,'C2','2002-01-01','P2',15);
Insert into orders values(03,'C3','2002-04-01','P3',17);
Insert into orders values(04,'C4','2003-04-01','P1',20);
Insert into orders values(05,'C4','2006-01-01','P2',1);
Insert into orders values(06,'C1','2006-05-01','P5',7);

I am assuming that I need to write a procedure and LOOP based on Order_id serial number. Once in loop, I need to pick the product_id and quantity Q corresponding to order_id. Then check whether for the same order_id there is any other quantity Q1 that is less than Q. If so, then I print the customer_id.
If not, then the order_id moves to next order_id.

I am not sure how to implement the part where I check the quantity column to check whether there is any other quantity Q1 < Q for order_id?

Please clarify

vkp vkp
Answer Source

You should calculate row_numbers per each customer_id based on order_date. Then you have to join the nth row to the n-1th row for each customer and check to see if they have atleast one order where the quantity is less than the previous order.

SQL Fiddle

select t1.customer_id
from (select o.*,
      @rn:=if(@previous=customer_id,@rn,0) + 1 as rownum,
      from orders o
      order by customer_id,order_date) t1
join (select o.*,
      @rn:=if(@previous=customer_id,@rn,0) + 1 as rownum,
      from orders o
      order by customer_id,order_date) t2 
on t1.customer_id=t2.customer_id and t1.rownum=t2.rownum-1
group by t1.customer_id
having count(case when t2.quantity < t1.quantity then 1 end) >= 1