WooCommerce, select users with completed orders by user role

mysql_query.sql

-- ALL SUBSCRIBERS WITH AN ORDER COMPLETED
SELECT 
    u.ID, um.meta_value AS user_role, COALESCE(COUNT(p.ID), 0) AS completed_orders
FROM wp_users AS u JOIN wp_usermeta AS um ON u.ID = um.user_id 
	LEFT JOIN wp_postmeta AS pm ON pm.meta_value = um.user_id  
    LEFT JOIN wp_posts AS p ON p.ID = pm.post_id 
WHERE um.meta_key = 'wp_capabilities'
    AND um.meta_value LIKE '%subscriber%' 
    AND um.meta_value NOT LIKE '%administrator%'
    AND pm.meta_key = '_customer_user'
    AND p.post_status LIKE 'wc-completed' 
    AND p.post_type LIKE 'shop_order' 
GROUP BY u.ID;

-- ALL CUSTOMERS WITHOUT AN ORDER COMPLETED
SELECT 
    u.ID, um.meta_value AS user_role, pm.meta_value
FROM wp_users AS u 
    INNER JOIN wp_usermeta AS um ON u.ID = um.user_id 
	LEFT JOIN wp_postmeta AS pm ON (u.ID = pm.meta_value
        AND pm.meta_key = '_customer_user')
    -- INNER JOIN wp_posts AS p ON p.ID = pm.post_id 
WHERE um.meta_key = 'wp_capabilities'
    AND um.meta_value LIKE '%customer%' 
    AND um.meta_value NOT LIKE '%administrator%'
    AND pm.meta_value IS NULL
    -- AND p.post_status LIKE 'wc-completed' 
    -- AND p.post_type LIKE 'shop_order' 
GROUP BY u.ID
LIMIT 10

-- ALL SUBSCRIBERS
SELECT DISTINCT u.ID, um.meta_value as user_role
	FROM wp_users AS u 
	JOIN wp_usermeta AS um ON u.ID = um.user_id 
WHERE um.meta_key = 'wp_capabilities' 
	AND um.meta_value LIKE '%subscriber%' 
	AND um.meta_value NOT LIKE '%administrator%';