SQL cannot add columns to query I have the following query (PostgreSQL 9.5.19):

with userlist as (
  select a.ips, 
         array_agg(t.super_user_id) as super_users
  from tmp_table_1 t
    left join unnest(t.ips) as a(ips) on true
  group by a.ips
), shared_users as (
  select u.ips,
         array(select distinct ul.uid
               from userlist u2, unnest(u2.super_users) as ul(uid)
               where u.super_users &amp;&amp; u2.super_users
               order by ul.uid) as super_users
  from userlist u
)
select s.super_users, array_agg(distinct s.ips) as ips, 
    (select sum(msg_count) from tmp_table_1 where super_user_id in s.super_users) as sum_msg_count
from shared_users s
group by s.super_users;


I get the following error:


ERROR:  syntax error at or near "s"
LINE 38: ...sg_count) from tmp_table_1 where super_user_id in s.super_us...
                                                              ^



It seems like I cannot access s.super_users from within the subquery. 

Anyone know how I can do this?

Here is a full example:  https://www.db-fiddle.com/f/geB9V6TdHEkF2mSqpd2b4g/1

Maybe I am not approaching this correctly? 

Question

How can I add the additional columns (sum_msg_count, sum_user_count, max_last_date and pubs) to the result set?

Desired result

|super_users                      |  ips                                                        |sum_msg_count| sum_user_count  |   max_last_date                | pubs                        |
|---------------------------------|-------------------------------------------------------------|-------------|-----------------|--------------------------------|-----------------------------|
|{522460771,522460772,522460773}  |  {94.134.88.115,94.134.88.124,94.134.88.125,94.134.88.136}  | 30          |         9       |  "2018-06-30 21:29:48.664000"  |  {ad2,googlecooler,ads,ad1} |
|{522460774,522460775}            |  {127.0.0.1,5.5.5.5}                                        | 20          |         6       |  "2018-06-30 21:29:48.664000"  |  {googlecooler,ad3,ad1}     |
|{522460776}                      |  {8.8.8.8}                                                  | 10          |         3       |  "2018-06-30 21:29:48.664000"  |  {googlecooler,ads}         |
You should use ANY, not IN to search in an array

...where super_user_id = ANY (s.super_users)