SQL - CASE WHEN result outputting conflicting results

Question

I'm working to solve https://platform.stratascratch.com/coding/10065-find-whether-the-number-of-seniors-works-at-facebook-is-higher-than-its-number-of-usa-based-employees?python=

This is the query I've attempted to write:

SELECT CASE WHEN COUNT(CASE WHEN location = 'US' THEN 1 ELSE 0 END) >
    COUNT(CASE WHEN is_senior = true THEN 1 ELSE 0 END) THEN 'More USA-based'
    ELSE 'More seniors' END AS what_do_we_have_more_of
FROM facebook_employees

Result: 'More seniors'

However, when I rewrite it with the conditions flipped around:

SELECT CASE WHEN COUNT(CASE WHEN is_senior = true THEN 1 ELSE 0 END) >
    COUNT(CASE WHEN location = 'US' THEN 1 ELSE 0 END) THEN 'More seniors'
    ELSE 'More USA-based' END AS what_do_we_have_more_of
FROM facebook_employees

Result: 'More USA-based'

Can someone please explain why there is a discrepancy here? What is wrong with the query I've written?

I know this problem can be solved with sub-queries but I wanted to try out a CASE WHEN approach specifically. Is this more efficient?

Edit: the solution I wrote with sub-queries (works with conditions reversed)

WITH us_employees AS (
    SELECT id, location
    FROM facebook_employees
    WHERE location = 'US'
),

senior_employees AS (
    SELECT id, is_senior
    FROM facebook_employees
    WHERE is_senior = true
)

SELECT CASE WHEN COUNT(location) < COUNT(is_senior) THEN 'More seniors' ELSE 'More US-based' END AS what_do_we_have_more_of
FROM us_employees u
FULL JOIN senior_employees s
ON u.id = s.id

Result: 'More seniors'

Answer 1

The use of count() in your query is incorrect. But how do you get different results? Because both counts are the same. So A > B is always false, and you always end up in the ELSE branch.

A proper query could look like this:

SELECT CASE WHEN count(*) FILTER (WHERE location = 'US')
               > count(*) FILTER (WHERE is_senior) THEN 'More USA-based'
            WHEN count(*) FILTER (WHERE location = 'US')
               < count(*) FILTER (WHERE is_senior) THEN 'More seniors'
            ELSE 'US-based and seniors tie' END AS what_do_we_have_more_of
FROM   facebook_employees;

See:

• Aggregate columns with additional (distinct) filters

Note, this can never fail with NULL values, because count() (unlike most aggregate functions) never returns NULL.

Answer 2

You should use SUM instead of COUNT.

COUNT will count +1 even when your CASE return 1 or 0.

SUM only count +1 when your CASE return 1.

So assume that your table has 1000 rows, then both your two queries will be CASE 1000 > 1000 THEN ... ELSE... END.