-- Step 1: Exclude threads from specified forums

WITH filtered_threads AS (

    SELECT thread_id

    FROM threads

    WHERE forum_id NOT IN (

        SELECT forum_id

        FROM forums

        WHERE name IN ('Nightlife Reviews', 'Let\'s Get Together & Real World Ideas', 'Drug Prevention', 'Music Production & Technology', 'News Postings & Articles')

    )

),

-- Step 2: Count unique user IDs per thread from posts within the threads and store it in a temporary table

thread_user_count AS (

    SELECT p.thread_id, COUNT(DISTINCT p.user_id) AS unique_user_count

    FROM posts p

    JOIN filtered_threads ft ON p.thread_id = ft.thread_id

    GROUP BY p.thread_id

)

-- Step 3: Select the top 100 threads with the most unique user IDs

SELECT thread_id, unique_user_count

FROM thread_user_count

ORDER BY unique_user_count DESC

LIMIT 100;