select
  s.emailaddress
, s.subscriberkey
, isnull(t.status, 'unknown') status
, case
    when t.dateCreated < m.DateCreated then t.dateCreated
    else m.dateCreated
  end dateCreated
, t.DateHeld
, t.DateUnsubscribed
from (

    select
    s.emailaddress
    , s.subscriberkey
    from Tracking_Subscribers s

    union

    select
    s.emailaddress
    , s.customerid subscriberkey
    from Master_Subscribers s

) s
left join Tracking_Subscribers t on t.subscriberkey = s.subscriberkey
left join Master_Subscribers m on m.customerid = s.subscriberkey
/* name: Unsubscribe_Sync_Subscriber_Source */
/* target: Unsubscribe_Sync_Subscriber_Source */
/* description: all subscribers plus master DE */
/* action: overwrite */

select
  x.emailaddress
, x.subscriberkey
, x.status
, x.DateCreated
, x.DateHeld
, x.DateUnsubscribed
, x.count
, x.ranking
from (

  select
    dupeSKSubs.emailaddress
  , s2.subscriberkey
  , s2.status
  , s2.DateCreated
  , s2.DateHeld
  , s2.DateUnsubscribed
  , dupeSKSubs.count
  , row_number() over (
        partition by dupeSKSubs.emailaddress
        order by
            s2.dateCreated
          , case
              when s2.subscriberkey not like '%_@_%_.__%' then s2.subscriberkey
              else 'z'
            end) ranking
  from (

      select
      s0.emailaddress
      , count(*) count
      from Unsubscribe_Sync_Subscriber_Source s0
      group by s0.emailaddress
      having count(*) > 1

  ) dupeSKSubs
  left join Unsubscribe_Sync_Subscriber_Source s2 on s2.emailaddress = dupeSKSubs.emailaddress

) x
/* name: Unsubscribe_Sync_Candidates1 */
/* target: Unsubscribe_Sync_Candidates */
/* description: rank rows from oldest to newest */
/* action: overwrite */