Filtration on columns created using CASE Postgresql

Question

I have a table below:

id int
user_id int
created_at datetime
status varchar
type varchar

and I am trying to answer the question "Write a query that returns the user ID of all users that have created at least one ‘Refinance’ submission and at least one ‘InSchool’ submission."

I figured out how this will be done:

select
    a.user_id
from
    (select
        user_id,
        sum(
            case
                when type='Refinance' then 1 else 0 end) as "Refinancecount",
        sum(
            case
                when type='InSchool' then 1 else 0 end) as "Inschoolcount"
    from
        loans
    group by 1) as a
where a.Refinancecount>=1 and a.Inschoolcount>=1

When I run only the inner query, everything is fine. I am getting 3 columns but when I run the whole query it says column Refinancecount doesn't found. I looked on internet and found when I am aliasing my case columns then I should't wrap them in double quotes and the query worked fine using this suggestion. But I read more about creating columns using CASE in postgre, and found people do wrap column names in double quotes (I have attached an example screenshot). Then why my query doesn't work this way.

Answer 1

You should quote those fields in the where clause. Try:

where "Refinancecount" >= 1 and "Inschoolcount" >= 1

By the way, you can write this query without inner query, by using having clause:

select
        user_id
from
        loans
group by 1
having sum(case when type='Refinance' then 1 else 0 end) >=1 and sum(case when type='InSchool' then 1 else 0 end) >= 1