WITH subquery AS (
    SELECT best_sellers.job_statuses_id, bestseller_product.product_id
    FROM bestseller_product
    LEFT JOIN best_sellers ON best_sellers.id = bestseller_product.best_seller_id
    UNION ALL
    SELECT jobstatus_product.job_status_id AS job_statuses_id, jobstatus_product.product_id
    FROM jobstatus_product
)
SELECT DISTINCT
    products.asin,
    products.domainId,
    products.id,
    job_statuses.id,
    job_statuses.created_at,
    job_statuses.priority
FROM products
JOIN subquery u ON u.product_id = products.id
LEFT JOIN job_statuses ON u.job_statuses_id = job_statuses.id
WHERE products.valid = true
AND NOT EXISTS (
    SELECT 1
    FROM global_keywords
    WHERE global_keywords.product_id = products.id
)
ORDER BY
    CASE
        WHEN job_statuses.priority = 'immediate' THEN 0
        WHEN job_statuses.priority = 'high' THEN 1
        WHEN job_statuses.priority = 'default' THEN 2
        WHEN job_statuses.priority = 'low' THEN 3
        ELSE 4
    END,
    job_statuses.id ASC,
    products.id ASC
LIMIT 10;