Subquery has to many columns

Question

I'm trying to solve this problem:

Determine the size category (Small, Medium, Large) for each company by first identify the number of job postings they have. Use a subquery to calculate the total job postings per company. A company is considered 'SMALL' if it has less than 10 job postings MEDIUM if jobs are 10-50 and LARGE if it has more than 50. Implement a subquery to aggregate job counts per company before classifying them based on size-

My query below but it returns subquery has too many columns

SELECT 
    company_id,
    name AS company_name
FROM company_dim 
WHERE company_id IN (
SELECT 
    COUNT(*),
CASE 
WHEN COUNT(*) < 10 THEN 'Small'
WHEN COUNT(*) BETWEEN 10 AND 50 THEN 'Medium'
ELSE 'Large'
END AS size_category
FROM job_postings_fact
GROUP BY company_id
);

I'm trying to return company name and their size category.

Answer 1

You're recieving a syntax error because you can't use a 'CASE' statement inside an 'IN' clause like this.

Try this instead:

SELECT
    c.company_id,
    c.name AS company_name,
    size_category
FROM company_dim c
JOIN (
    SELECT 
        company_id,
        CASE 
            WHEN COUNT(*) < 10 THEN 'Small'
            WHEN COUNT(*) BETWEEN 10 AND 50 THEN 'Medium'
            ELSE 'Large'
        END AS size_category
    FROM job_postings_fact
    GROUP BY company_id
) AS size_info ON c.company_id = size_info.company_id;

This first calculates the catergory size for each company with regard to the number of job postings in the 'job_postings_fact' table, and then it joins the result with the 'company_dim' table to recieve the company name and ID.