Error subquery en vista materializada

Hola a todos! 

Estoy intentando crear una vista materializada pero me están apareciendo errores por intentar crearla con subqueries. He visto en la doc de oracle que las subqueries no son posibles si están dentro de la sentencia SELECT pero que si están en el FROM o el WHERE si que es posible usarlas. Aqui os dejo la vistapara que le deis un vistazo y ver si podeis ayudarme. Os lo agradecería mucho.

CREATE MATERIALIZED VIEW LOG ON "subscriber" WITH SEQUENCE, ROWID
("id", "status", "id_service") 
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON "subscriber_events" WITHSEQUENCE, ROWID
("created_at", "id_event", "billed", "percent_billed", "id_service")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON "subscriber_status" WITHSEQUENCE, ROWID
("id_status")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON "service" WITH SEQUENCE, ROWID
("id", "price", "revenue")
INCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW "bill_arpu_month_by_service"
TABLESPACE plat_dat
BUILD IMMEDIATE
REFRESH FORCE 
START WITH sysdate NEXT +1 MONTH 
ENABLE QUERY REWRITE
AS

SELECT * FROM 
(SELECT created, service, billed, global_user_actives, optin, optout, new_users, dif, 
global_user_actives - SUM(new_users) OVER (PARTITION BY service ORDER BY created DESC) as actives_S_M, 
global_user_actives - SUM(new_users) OVER (PARTITION BY service ORDER BY created DESC) + new_users as actives_E_M, 
round((CASE WHEN (global_user_actives - SUM(new_users) OVER (PARTITION BY service ORDER BY created DESC) + new_users) =0 THEN 0 
ELSE (billed/(global_user_actives - SUM(new_users) OVER (PARTITION BYservice ORDER BY created DESC) + new_users)) * service_mult END),2) arpu

FROM 
( select to_char("created_at", 'yyyymm') "CREATED", AVG("service"."id")service,
SUM( CASE WHEN "id_event" IN ('1', '5', '3') and "billed" = '1' THEN 1 WHEN "id_event" IN ('6', '4') and "billed" = '1' THEN "percent_billed"/100 ELSE 0 END) AS BILLED, 
(select count("id") from "subscriber" join "subscriber_status" on "subscriber"."status" = "subscriber_status"."id_status" where "subscriber"."status" = 1 and "subscriber"."id_service" = "service"."id") as global_user_actives, 
SUM( CASE WHEN "id_event" IN ('1') THEN 1 ELSE 0 END) AS optin, SUM( CASE WHEN "id_event" IN ('2') THEN 1 ELSE 0 END) AS optout, (SUM( CASE WHEN "id_event" IN ('1','3') THEN 1 ELSE 0 END) - SUM( CASE WHEN "id_event" IN ('2') THEN 1 ELSE 0 END)) as new_users, 
((select count("id") from "subscriber" join "subscriber_status" on "subscriber"."status" = "subscriber_status"."id_status" where "subscriber"."status" = 1 and "subscriber"."id_service" = "service"."id") - (SUM( CASE WHEN "id_event" IN ('1','3') THEN 1 ELSE 0 END) - SUM( CASE WHEN "id_event" IN ('2') THEN 1 ELSE 0 END))) as dif, 

(COALESCE( AVG("service"."price")*AVG("service"."revenue")/100 , 0)) as service_mult

from "subscriber_events" 
JOIN "service" ON "subscriber_events"."id_service" = "service"."id" 
where "id_event" IN ('1', '2', '3', '4', '5', '6') 
group by "service"."id", to_char("created_at", 'yyyymm') order by "service"."id", "CREATED" DESC ) 
ORDER BY "SERVICE", "CREATED" DESC);

22818. 00000 -  "subquery expressions not allowed here"

*Cause:    An attempt was made to use a subquery expression where these

are not supported.