Need help on this nested SQL

I have this:

select
    Z.sponsor_category,
    Z.reporting_rollup_sponsor_code,
    Z.collector_key,
    Z.mkt_campaign_key,
    Z.date_id,
    rank() over(partition by Z.sponsor_category, Z.reporting_rollup_sponsor_code, Z.collector_key
        order by Z.date_id DESC NULLS last) rn
from
    (
    select distinct 
        d.sponsor_category,
        d.reporting_rollup_sponsor_code,
        b.collector_key,
        a.mkt_campaign_key,
        b.date_id,
        count(distinct d.reporting_rollup_sponsor_code) as sponsor_count
    from db.mkt_offer_instance_dim a,
        db.mkt_contact_fact b,
        db.collector_channel_activity c,
        db.rng_email_active_spon d
    where 
        a.sponsor_key = d.sponsor_key and
        b.collector_key = c.collector_key and
        a.mkt_offer_instance_key = b.mkt_offer_instance_key and
        b.date_id > to_number(to_char(date(current_date -  cast('12 month' as interval)),'YYYYMMDD'),99999999) and 
        a.mkt_channel_key = 2 and 
        b.contact_status_key = 1 and
        c.emailable_flag = 'Y' and
        d.reporting_rollup_sponsor_code in ('RONA')
    group by 1,2,3,4,5
    having sponsor_count=1;
    ) Z

Not sure why this does not work.

I basically want to use the rank() to return the top 4 records:

I had this originally, but I do not want to create a table of the last 12 months. I just want to apply a nested SQL and create the final table of the last 4 records.

create table db.rng_solo_email_del_coll_L4 as
select 
    sponsor_category,
    reporting_rollup_sponsor_code,
    collector_key,
    mkt_campaign_key,
    date_id,
    rn
from
(
select 
    sponsor_category,
    reporting_rollup_sponsor_code,
    collector_key,
    mkt_campaign_key,
    date_id,
    rank() over(partition by sponsor_category, reporting_rollup_sponsor_code, collector_key
        order by date_id DESC NULLS last) rn
from db.rng_solo_email_del_coll_12mth
) A
where rn <=4
distribute on random;