Trying to create a shortcode to display categories with a specific product tag

You need to pass your attributes in the shortcode as key-value pairs, and use them in php as an array as follows:

[tagtagtag tag_id=246]

function get_categories_with_product_in_tag( $atts ) {
    global $wpdb;

    return $wpdb->get_results( $wpdb->prepare(
        "SELECT categories.* FROM ".
        "{$wpdb->prefix}terms categories ".
        "INNER JOIN {$wpdb->prefix}term_taxonomy ctax ON (categories.term_id = ctax.term_id) ".
        "INNER JOIN {$wpdb->prefix}term_relationships crel ON (crel.term_taxonomy_id = ctax.term_taxonomy_id) ".
        "INNER JOIN {$wpdb->prefix}posts posts ON (posts.ID = crel.object_id) ".
        "INNER JOIN {$wpdb->prefix}term_relationships trel ON (posts.ID = trel.object_id) ".
        "INNER JOIN {$wpdb->prefix}term_taxonomy ttax ON (trel.term_taxonomy_id = ttax.term_taxonomy_id) ".
        "INNER JOIN {$wpdb->prefix}terms tags ON (ttax.term_id = tags.term_id) ".
        "WHERE ".
        "ctax.taxonomy='product_cat' ".
        "AND posts.post_type="product" ".
        "AND posts.post_status="publish" ".
        "AND ttax.taxonomy = 'product_tag' ".
        "AND tags.term_id = %d ".
        "GROUP BY categories.term_id",
        $atts['tag_id']
    ) );
}
add_shortcode('tagtagtag', 'get_categories_with_product_in_tag');

https://developer.wordpress.org/plugins/shortcodes/shortcodes-with-parameters/