select t.id end_usr_id,
       t.end_user_name end_usr_name,
       t.end_user_real_name end_usr_nknm,
       t.end_user_password end_usr_pswd,
       t.end_user_birthday end_usr_bthd,
       t.mobile mobl_num,
       t.phone phone_num,
       t.valid_mobile_phone_num bindg_mobl_num,
       t.end_user_email end_usr_email,
       substr(t.end_user_email,
              instr(t.end_user_email, '@'),
              instr(t.end_user_email, '.') - instr(t.end_user_email, '@')) email_class,
       t.end_user_create_time creat_time,
       t.end_user_last_login_date end_usr_last_login_date,
       t.end_user_last_bought_date end_usr_last_buy_date,
       t.end_user_login_times end_usr_login_times,
       t.end_user_bought_times end_usr_buy_times,
       t.end_user_bought_amount end_usr_buy_amt,
       t.end_user_type end_usr_type,
       t.ip ip,
       t.end_user_points end_usr_intgl,
       t.id_card id_card,
       t.end_user_sex sex,
       t.mc_site_id mc_site_id,
       case
         when t.is_email_activate = 1 then
          1
         else
          0
       end as email_verfd_flg,
       case
         when t.valid_mobile_phone_num is not null then
          1
         else
          0
       end as mobl_bding_flg,
       case
         when t.end_user_name like '%@sina%' then
          1
         when t.end_user_name like '%@pingan%' then
          2
         when t.end_user_name like '%@alipay%' and
              not (t2.user_grade in (1, 2) and t2.user_grade_type = 1) then
          3
         when t.end_user_name like '%@alipay%' and t2.user_grade in (1, 2) and
              t2.user_grade_type = 1 then
          4
         when t.end_user_name like '%@163%' then
          5
         when t.end_user_name like '%@kaixin001%' then
          6
         when t.end_user_name like '%@139%' then
          7
         when t.end_user_name like '%@msn.com%' then
          8
         when t.end_user_name like '%@anyue%' then
          9
         when t.end_user_name like '%@qq%' then
          10
         else
          0
       end as commn_login_type,
       case
         when t.end_user_name like '%@b2b%' or t.end_user_name = 'yuxiaolan' then
          1
         else
          0
       end as b2b_flg,
       case
         when t3.send_to is not null then
          1
         else
          0
       end as email_ret_flg,
       case
         when t4.email is not null then
          1
         else
          0
       end as email_blacklist_flg,
       case
         when t5.car = '1' then
          1
         else
          0
       end as car_flg,
       t5.shopping_habit shppg_prefr,
       t6.mail_level email_lvl,
       '' whsl_flg,
       '' xh_flg,
       '' exptn_flg,
       '' b2b_vip_flg,
       t.member_grade end_usr_grd_id,
       case
         when t.co_code is null then
          null
         when t.co_code is not null and t7.usr_union_logon_id is null then
          -999999
         else
          t7.usr_union_logon_id
       end usr_union_logon_id,
       t.co_code usr_union_logon_code,
       t8.exp end_usr_grow_val
  from tandem.end_user t
  left outer join tandem.alipay_user t2
    on t.id = t2.end_user_id
  left outer join (select distinct lower(send_to) as send_to
               from tandem.yhd_send_message_blacklist) t3
    on lower(t.end_user_email) = t3.send_to
  left outer join (select distinct lower(email) as email
               from tandem.edm_error_email) t4
    on lower(t.end_user_email) = t4.email
  left outer join tmp_end_usr_info t5
    on t.id = t5.end_usr_id
  left outer join tandem.crm_user_quality t6
    on t.end_user_email = t6.email
   and t6.user_id is not null
  left outer join dw.usr_union_logon t7
    on t.co_code = t7.usr_union_logon_code
   and t7.usr_union_logon_code is not null
  left outer join tandem.member t8
    on t.id = t8.end_user_id
   and to_date(t8.update_time) = '2015-05-04'
 where t.update_time >= '2015-05-04'
    or length(t8.end_user_id)>0;