WITH RECURSIVE job_categories AS (
    SELECT DISTINCT CAST(
            JSON_EXTRACT(properties, '$.node_id') AS UNSIGNED
        ) AS category_id,
        created_at AS job_created_at
    FROM job_statuses
    WHERE id >= 9712
        AND type = 'best_seller'
        AND status != 'failed'
        AND JSON_EXTRACT(properties, '$.node_id') IS NOT NULL
),
product_list AS (
    SELECT p.id AS product_id
    FROM products p
        JOIN job_categories jc ON p.category = jc.category_id
    WHERE p.created_at >= jc.job_created_at
)
SELECT DISTINCT
    pl.product_id,
    ak.keyword_phrase,
    ak.iq_score,
    ak.search_volume,
    ak.search_volume_trend,
    ak.sponsored_asin,
    ak.competing_products,
    ak.CPR,
    ak.title_density,
    ak.amazon_rec_rank,
    ak.sponsored_rank
FROM
    product_list pl
JOIN
    `viko-lab.aquila.amz_product_keywords` apk ON pl.product_id = apk.product_id
JOIN
    `viko-lab.aquila.amz_keywords` ak ON apk.keyword_id = ak.id
ORDER BY
    pl.product_id, ak.keyword_phrase;