0

I am very new to SQL and have a relatively complex query to write, any help would be appreciated.

I have data with the following columns:

ORDER_ID
CUST_ID
ORDER_DATE
RETURNED(1/0) 

I need a table with the following three columns:

  • CUST_ID

  • A column with the count of the number of orders a specific customer had before their FIRST returned order.

  • A column with the count of number of orders a specific customer had after their FIRST returned order.

Each order has a unique id as does each customer, but there are many customers who had multiple orders at different dates. Not all customers had returned orders and I just need the counts for the customers who had a returned order (RETURNED=1).

0

Would something like this do?

with only_returned as
  (select cust_id, 
          min(order_date) min_order_date
   from your_table
   where returned = 1
   group by cust_id
  )
select r.cust_id, 
       count(b.order_id) returned_before,
       count(a.order_id) returned_after
from only_returned r 
  join your_table b on b.cust_id = r.cust_id
                   and b.order_date < r.min_order_date
  join your_table a on a.cust_id = r.cust_id          
                   and a.order_date > r.min_order_date;
  • ONLY_RETURNED CTE contains customers whose orders were returned; it also selects the first return order date
  • then, join only_returned with your_table, twice:
    • once for orders placed before minimum return order date (the b alias)
    • once for orders placed after minimum return order date (the a alias)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.