MySQL query to get members with paid levels (6, 20, 21) that don't have a paid order within the past 12 months.

unpaidmembers.sql

# Find members with active paid memberships but their last order was > 12 months ago.
SELECT 
    m.user_id, 
    m.membership_id,
    u.user_email,
    MAX(o.timestamp) as last_order_timestamp
FROM 
    wp_pmpro_memberships_users m
        LEFT JOIN wp_users u ON m.user_id = u.ID
JOIN 
    wp_pmpro_membership_orders o ON m.user_id = o.user_id
WHERE 
    m.membership_id IN (6, 20, 21)
        AND m.status = 'active'
GROUP BY 
    m.user_id, m.membership_id
HAVING
    last_order_timestamp < DATE_SUB(NOW(), INTERVAL 12 MONTH);