カテゴリもタグもタクソノミーなので、category タクソノミーの list タームに関連付けられた投稿のそれぞれについて、関連付けられている post_tag タクソノミーのターム数をカウントします。日本語で書いてもSQLで書いてもややこしい。
SELECT p.ID, COUNT(tt2.term_taxonomy_id) AS count
FROM wp_posts AS p
JOIN wp_term_relationships AS r ON p.ID=r.object_id
JOIN wp_term_taxonomy AS tt ON r.term_taxonomy_id=tt.term_taxonomy_id AND tt.taxonomy='category'
JOIN wp_terms AS t ON tt.term_id=t.term_id AND t.name='list'
JOIN wp_term_relationships AS r2 ON p.ID=r2.object_id
JOIN wp_term_taxonomy AS tt2 ON r2.term_taxonomy_id=tt2.term_taxonomy_id AND tt2.taxonomy='post_tag'
WHERE p.post_status='publish' AND p.post_type='post'
GROUP BY p.ID
ORDER BY count ASC
5 までで list カテゴリーに関連付けられた投稿を取り出しています。6と7で、その投稿に関連付けられたタグを取り出し、投稿IDごとにカウントします。
SELECT COUNT(t3.term_id) AS count, t3.term_id, t3.name
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt ON t.term_id=tt.term_id AND taxonomy='post_tag'
INNER JOIN wp_term_relationships AS r ON tt. term_taxonomy_id=r.term_taxonomy_id
INNER JOIN wp_posts AS p ON r.object_id=p.ID AND p.post_status='publish' AND p.post_type='post'
INNER JOIN wp_term_relationships AS r2 ON p.ID=r2.object_id
INNER JOIN wp_term_taxonomy AS tt2 ON r2.term_taxonomy_id=tt2.term_taxonomy_id AND tt2.taxonomy='category'
INNER JOIN wp_terms AS t2 ON tt2.term_id=t2.term_id AND t2.name='list'
INNER JOIN wp_term_relationships AS r3 ON p.ID=r3.object_id
INNER JOIN wp_term_taxonomy AS tt3 ON r3.term_taxonomy_id=tt3.term_taxonomy_id AND tt3.taxonomy='post_tag'
INNER JOIN wp_terms AS t3 ON tt3.term_id=t3.term_id AND t3.term_id != （表示タグのID）
WHERE t.term_id=（表示タグのID）
GROUP BY t3.term_id
ORDER BY count DESC
LIMIT 5
SELECT COUNT(DISTINCT t.term_id) AS total, COUNT(t.term_id) AS c_total
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id AND tt.taxonomy = 'asin'
INNER JOIN wp_term_relationships AS r ON tt.term_taxonomy_id = r.term_taxonomy_id
INNER JOIN wp_posts AS p ON r.object_id = p.ID AND p.post_status = 'publish' AND p.post_type = 'post'
INNER JOIN wp_term_relationships AS r2 ON p.ID = r2.object_id
INNER JOIN wp_term_taxonomy AS tt2 ON r2.term_taxonomy_id = tt2.term_taxonomy_id AND tt2.taxonomy = 'category'
INNER JOIN wp_terms AS t2 ON tt2.term_id = t2.term_id AND t2.name = 'list'
SELECT COUNT(DISTINCT t.term_id) AS total, COUNT(t.term_id) AS c_total
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships AS r ON tt.term_taxonomy_id = r.term_taxonomy_id
INNER JOIN wp_posts AS p ON r.object_id = p.ID
INNER JOIN wp_term_relationships AS r2 ON p.ID = r2.object_id
INNER JOIN wp_term_taxonomy AS tt2 ON r2.term_taxonomy_id = tt2.term_taxonomy_id
INNER JOIN wp_terms AS t2 ON tt2.term_id = t2.term_id
WHERE tt.taxonomy = 'asin' AND p.post_status = 'publish' AND p.post_type = 'post' AND tt2.taxonomy = 'category' AND t2.name = 'list'
