Is it possible to process external data as some kind of virtual table?

I have the following problem I need to make a funnel, where I need to show correlation between the data I have and data from DB. I have a query of the following kind:

select name, count(distinct email) from some_table
   where name = 'name_1' and email in ('email 1', 'email 2', 'email 3') or 
         name = 'name_2' and email in ('email 2', 'email 4', 'email 5')
group by 1

Is it possible to process the data in where statement in such a way that I could address to it as to a table I mean if there is a possibility to count the emails by name in where statement to in something like this?

select name, count(emails in the list), count(distinct email)

to have the result like this

name_1 3 2
name_2 3 1
...  

listed emails can be absent it the some_table and if I’m to join tables, I’m to join 3 different tables for every data piece that are not directly related. The data I have is manually processed and is not added to DB.

Answer

You can use a VALUES list to construct your virtual table, and join that to your real table.

select f.name, count(distinct email) from some_table join 
   (VALUES ('name_1', '{email 1,email 2,email 3}'::text[]), 
           ('name_2', '{email 2,email 4,email 5}')) f(name,emails)
   on some_table.name=f.name and ARRAY[email] && emails
group by 1

I had to switch from an IN-list to an equivalent array operation, because the tables can have arrays but can’t have lists.

count(emails in the list)

I’m not sure what this means. Maybe this:?

select f.name, cardinality(emails), count(distinct email) from some_table join 
       (VALUES ('name_1', '{email 1,email 2,email 3}'::text[]), 
               ('name_2', '{email 2,email 4,email 5}')) f(name,emails)
       on some_table.name=f.name and ARRAY[email] && emails
    group by 1,2