0

The Return table looks like:

Order_id Address Return_request_date
12345 Address1 2022-01-01
23456 Address2 2022-03-04

The Order table looks like:

Order_id Address Order_date
12345 Address1 2022-01-01
12346 Address1 2022-01-03

How to count how many addresses made the 2nd purchase after submitting return request?

What I have tried but doesn't really works:

select count(distinct r.address)
from return r left join order o on r.address = o.address
where o.order_date > r.return_request_date
Xinxinzi
  • 1
  • 3
  • Please explain "doesn't really work"; does it not work at all or does it work but is slow, etc.? – Martin Jul 19 '22 at 22:13
  • Hi, the table structure is a bit complicated. Even though I narrowed down the constraints a lot, the code still ran for more than 15 minutes without any results, so I'm trying to understand if there's something wrong with my own code logic. – Xinxinzi Jul 19 '22 at 22:32
  • to be honest, there's something wrong with your question! What exactly are you trying to do? Can you show a complete example of what the data looks like and what result you are trying to obtain? – DCR Jul 19 '22 at 22:35

1 Answers1

0

Something like

SELECT ot.order_id, MAX(rt.return_request_date) as mrrd 
    FROM orders_table ot
    INNER JOIN returns_table rt ON ot.order_id = rt.order_id 
    HAVING ot.order_date >= mrrd

This above SQL will almost certainly need some refining but the process here is to:

  • Select ONLY the order Id's which have at least one returned order id in place (via the INNER JOIN)
  • Select only the maximum order_return_request date from the join
  • and HAVING the order date is later than this maximum return_request_date.

Without using HAVING you'd need to use Subquery SELECTs which might be illustrated here or here.

Martin
  • 22,212
  • 11
  • 70
  • 132