Untitled

mail@pastecode.io avatar
unknown
plain_text
a month ago
646 B
2
Indexable
Never
SELECT
    pm1.meta_value AS customer_email,
    p1.post_date AS order_date,
    MIN(p2.post_date) AS next_order_date,
    DATEDIFF(MIN(p2.post_date), p1.post_date) AS days_between_orders
FROM
    wp_posts p1
INNER JOIN wp_postmeta pm1 ON p1.ID = pm1.post_id AND pm1.meta_key = '_billing_email'
INNER JOIN wp_posts p2 ON p2.post_date > p1.post_date
INNER JOIN wp_postmeta pm2 ON p2.ID = pm2.post_id AND pm2.meta_key = '_billing_email' AND pm1.meta_value = pm2.meta_value
WHERE
    p1.post_type = 'shop_order'
    AND p2.post_type = 'shop_order'
GROUP BY
    p1.ID, pm1.meta_value
HAVING
    days_between_orders > 0
ORDER BY
    p1.post_date ASC;
Leave a Comment