MySQL Find closest date and corresponding records between 2 tables

user_3163980 0
Viewed 205

I'm trying to get the closest subscription date to each order date (only for subscription dates that are before order dates) and also the corresponding cancellation date. Is there anyway to do this? I use mainly MySQL but any SQL variant that works in Snowflake should be ok.

My Code:

Select DISTINCT o.ORDER_ID, o.CUSTOMER_EMAIL, o.order_DATE min(subs.START_DATE)

FROM ORDERS o
INNER JOIN subscriptions subs

GROUP BY ORDER_ID, CUSTOMER_EMAIL
,ORDER_DATE
ON o.CUSTOMER_EMAIL = subscriptions.CUSTOMER_EMAIL

ORDERS Table:

ORDER_ID ORDER_DATE CUSTOMER_EMAIL
1234 20/02/2021 jay@gmail.com

Subscriptions table:

|SUBSCRIPTION_ID | START_DATE | CUSTOMER_EMAIL | CANCEL_DATE |
|----------------|------------|----------------|-------------|
|1236            | 20/03/2021 |jay@gmail.com   |   20/04/2021|
|1232            | 19/02/2021 |jay@gmail.com   |   19/03/2021|
|1219            | 20/01/2021 |jay@gmail.com   |   29/01/2021|

Expected Result:

ORDER_ID ORDER_DATE CUSTOMER_EMAIL EARLIEST_SUBSCRIPTION_START CLOSEST_SUBSCRIPTION_START CLOSEST_SUBSCRIPTION_END
1234 20/02/2021 jay@gmail.com 20/01/2021 19/02/2021 19/03/2021
snowflake-cloud-data-platform mysql
2 Answers

A snowflake focused solution:

So with some CTEs for to fake the data:

WITH orders(order_id, order_date, email) AS (
    SELECT column1, to_date(column2,'DD/MM/YYYY'), column3 FROM VALUES
    (1234, '20/02/2021', 'jay@gmail.com')
), subscriptions(subscription_id, start_date, email, cancel_date) AS (
    SELECT column1, to_date(column2,'DD/MM/YYYY'), column3, to_date(column4,'DD/MM/YYYY') FROM VALUES
    (1236, '20/03/2021', 'jay@gmail.com', '20/04/2021'),
    (1232, '19/02/2021', 'jay@gmail.com', '19/03/2021'),
    (1219, '20/01/2021', 'jay@gmail.com', '29/01/2021')
)

and this SQL:

SELECT
    o.order_id,
    o.order_date,
    o.email,
    FIRST_VALUE(s.start_date) OVER (PARTITION BY o.order_id, o.order_date, o.email ORDER BY s.start_date) as EARLIEST_SUBSCRIPTION_START,
    start_date AS CLOSEST_SUBSCRIPTION_START,
    cancel_date AS CLOSEST_SUBSCRIPTION_END
FROM orders AS o
JOIN subscriptions AS s
    ON o.email = s.email AND o.order_date > s.start_date
QUALIFY row_number() over(PARTITION BY o.order_id, o.order_date, o.email ORDER BY s.start_date desc ) = 1

we get:

ORDER_ID ORDER_DATE EMAIL EARLIEST_SUBSCRIPTION_START CLOSEST_SUBSCRIPTION_START CLOSEST_SUBSCRIPTION_END
1234 2021-02-20 jay@gmail.com 2021-01-20 2021-02-19 2021-03-19

This is using the WINDOW function FIRST_VALUE to get the first row for each row, then we use QUALIFY to drop the rows (per order,oder_date,email) that are not the first when sorted by subscription date in reverse.

user_439920

Sorry, editing my answer. It isn't really clear what you are asking in total. If you just do a simple INNER JOIN LIKE:

SELECT ORDER_ID, ORDER_DATE, orders.CUSTOMER_EMAIL, subscriptions.START_DATE, subscriptions.SUBSCRIPTION_ID, subscriptions.CANCEL_DATE, DATEDIFF(ORDER_DATE, subscriptions.START_DATE) as DIFF

FROM orders INNER JOIN subscriptions ON orders.CUSTOMER_EMAIL = subscriptions.CUSTOMER_EMAIL

WHERE ORDER_DATE < subscriptions.START_DATE

You get results that look like:

ORDER_ID    ORDER_DATE  CUSTOMER_EMAIL  START_DATE  SUBSCRIPTION_ID CANCEL_DATE DIFF
1234        2021-01-20  jay@gmail.com   2021-02-20  1232            2021-03-19  -31
1234        2021-01-20  jay@gmail.com   2021-03-20  1236            2021-02-20  -59

Seems like you would have to place an order before the subscription starts, the the other way around ? Not sure what you mean by earliest vs. closest either.

Adding an update, which seems closer and requires the customer e-mail as the parameter:

SELECT ORDER_ID, ORDER_DATE, orders.CUSTOMER_EMAIL, earliest.EARLIEST_SUBSCRIPTION_START, closest.CLOSEST_SUBSCRIPTION_START, closestcancel.CLOSEST_SUBSCRIPTION_END  from orders
LEFT JOIN
(SELECT MIN(START_DATE) as EARLIEST_SUBSCRIPTION_START from subscriptions WHERE CUSTOMER_EMAIL = 'jay@gmail.com') earliest ON orders.CUSTOMER_EMAIL = 'jay@gmail.com'
LEFT JOIN
(SELECT DATEDIFF(subscriptions.START_DATE,orders.ORDER_DATE) as days, START_DATE as CLOSEST_SUBSCRIPTION_START from orders INNER JOIN subscriptions ON orders.CUSTOMER_EMAIL = subscriptions.CUSTOMER_EMAIL WHERE orders.CUSTOMER_EMAIL = 'jay@gmail.com' ORDER BY ABS(days) LIMIT 1) closest ON orders.CUSTOMER_EMAIL = 'jay@gmail.com'
LEFT JOIN
(SELECT DATEDIFF(subscriptions.CANCEL_DATE,orders.ORDER_DATE) as days, CANCEL_DATE as CLOSEST_SUBSCRIPTION_END from orders INNER JOIN subscriptions ON orders.CUSTOMER_EMAIL = subscriptions.CUSTOMER_EMAIL WHERE orders.CUSTOMER_EMAIL = 'jay@gmail.com' ORDER BY ABS(days) LIMIT 1) closestcancel ON orders.CUSTOMER_EMAIL = 'jay@gmail.com'

You would have to run it on your own table since I'm not sure the data is correct. It is looking forwards and backwards in time for the "Closest" (sorting by Absolute value), so you might have to adjust some things there.

user_23918590
Related