SELECT
    ak.id, 
    ak.keyword_phrase,
    ak.search_volume, 
    apk.domain_id, 
    COUNT(*) as occurrences
FROM
    viko-lab.aquila.amz_keywords ak
INNER JOIN
    viko-lab.aquila.amz_product_keywords apk
    ON ak.id = apk.keyword_id
WHERE
    ak.gads IS NULL
GROUP BY
    ak.id, ak.keyword_phrase, ak.search_volume, apk.domain_id
ORDER BY
    domain_id, occurrences DESC, ak.search_volume DESC
LIMIT 500;