SQL - CASE WHEN result outputting conflicting results-CodePudding

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'

CodePudding user response：

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.

CodePudding user response：

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.