Merchant Details Table

drop table team_kingkong.shivam_merchants;
create table team_kingkong.shivam_merchants as (
SELECT distinct
  m_1.mid,
  concat('_',m_1.mid) _mid2,
   m_1.id,
   m_1.merchant_name,
    m_1.ppi,
   m_1.entity_type,
   m_1.is_blocked,
   m_1.created_date onboarding_date,
   DATE_DIFF('day', date(m_1.created_date) ,current_date) vintage_days,
contact_mobile, 
primary_email, 
secondary_email,
   m_2.entity_id,
   m_2.category,
   m_7.channel,
   m_2.sub_category,
   m_2.business_name,
   signatory_name,
  m_2.bus_address,
   m_2.zipcode,
   m_2.city,
   m_2.state_id,
   m_2.comm_address,
m_2.comm_pin,
m_2.comm_city,
m_2.comm_state,
offline_alert_count,
online_alert_count,
edc_model_alert_count,
daily_limit,
monthly_limit,
sett_acc_change,
bankname,
aadhar_status,
   pan_status,
   gst_status,
gstin,
pan_no_personal,
pan_no_business,
ifsc_code,
bank_acc_no,
 bank_name,
 bank_account_name,
 ag.agent_id,
 ag.agent_name, 
 ag.agent_type, 
ag.agent_cust_id, 
ag.agent_team, 
ag.phone_number agent_phone_number,
 CASE
  WHEN m_3.o_mid IS NOT NULL THEN 'Online'
  WHEN m_4.e_mid IS NOT NULL THEN 'EDC'
  ELSE 'QR'
 END AS EDC_QR,
 case when complete_latitude is not null then complete_latitude else lat_lon2.lat end as latitude,
case when complete_longitude is not null then complete_longitude else lat_lon2.lon end as longitude,
case when um.mid is not null then 1 end as flag_merchant_is_user,
um.merchant_user_id,
ag.channel onb_source,
mcc


FROM
 (
  SELECT DISTINCT
   mid,
   id,
   merchant_name,
   ppi_limited_merchant AS ppi,
   entity_type,
   is_blocked,
   created_date
  FROM
   pg.entity_info_snapshot_v3
  WHERE
   dl_last_updated >= DATE '2010-01-01'
 ) AS m_1

 LEFT JOIN (
  SELECT distinct
   entity_id,
   category,
   sub_category,
   business_name,
   concat(address_1,' ',address_2,' ',address_3) as bus_address,
   zipcode,
   city,
   state_id,
   concat(comm_address_1,' ',comm_address_2,' ',comm_address_3) as comm_address,
comm_pin,
comm_city,
comm_state
  FROM
   pg.entity_demographics_snapshot_v3
  WHERE
   dl_last_updated >= DATE '2010-01-01'
 ) AS m_2 ON m_1.id = m_2.entity_id

 LEFT JOIN (
  SELECT DISTINCT
   merchant_id AS o_mid
  FROM
   datalake.online_payment_merchants
 ) AS m_3 ON m_1.mid = m_3.o_mid

 LEFT JOIN (
  SELECT DISTINCT
   mid AS e_mid
  FROM
   paytmpgdb.entity_edc_info_snapshot_v3
  WHERE
--    terminal_status = 'ACTIVE'
--    AND 
   dl_last_updated >= DATE '2010-01-01'
 ) m_4 ON m_1.mid = m_4.e_mid

 LEFT Join (
  select distinct
   mid,
   count(mid) as offline_alert_count
  from
   ds4_shared.offline_triggers_snapshot_v3
  WHERE
   dl_last_updated >= DATE '2024-01-01'
  group by mid
 ) as m_6 ON m_1.mid = m_6.mid


left join
(select 
merchant_id mid,
count(merchant_id) as edc_model_alert_count
from team_lionking.edc_score_snapshot_v3
where dl_last_updated >= DATE '2024-01-01'
and  mid_risk_score>=0.95
group by 1) e_al on e_al.mid = m_1.mid


 LEFT Join (
  select distinct
   pg_mid,
   channel
  from
   cdo.total_offline_merchant_base_snapshot_v3
 ) AS m_7 ON m_1.mid = m_7.pg_mid

 LEFT Join (
  select distinct
   mid,
   count(mid) as online_alert_count
  from
   risk_analytics_db.ropanel_review_merchant_snapshot_v3
  where
   DATE(substr(cast(inserted_at AS VARCHAR), 1, 10)) >= date '2023-01-01'
  group by mid
 ) AS m_8 ON m_1.mid = m_8.mid

 LEFT JOIN (
   select * from 
  (select distinct
   ENTITY_ID,
   row_number() over (
    partition by
     ENTITY_ID
    order by
     created_date desc
   ) as r_num,
   maxamtperday as daily_limit,
   maxamtpermon as monthly_limit
  from
   pg.entity_txn_limits_snapshot_v3
  where
   limits_identifier = 34577774955
   AND dl_last_updated >= date'2023-01-01'
   )
   where r_num = 1
 ) AS m_9 ON m_1.id = m_9.ENTITY_ID


 LEFT Join (
  select
   pg_mid,
   count(distinct pg_mid) as sett_acc_change
  from
   dwh.onboarding_engine_olap
  where
   ingest_date >= date '2023-01-01'
   and date(lead_creation_timestamp) >= date '2023-01-01'
   and solution_type = 'pg_profile_update'
   and solution_type_level_2 = 'BANK_DETAIL_UPDATE'
   and stage = 'LEAD_CLOSED'
   and substage = 'LEAD_SUCCESSFULLY_CLOSED'
  group by
   pg_mid
 ) AS m_10 ON m_1.mid = m_10.pg_mid

 LEFT Join (
   select * from 
  (select distinct
   merchantid,
   bankname,
   row_number() over (
    partition by
     merchantid
    order by
     dl_last_updated desc
   ) as r_num
  from
   cdp_risk_transform.external_merchant_info_snapshot_v3
  WHERE
   dl_last_updated >= DATE '2010-01-01')
   where r_num = 1
 ) as m_11 ON m_1.mid = m_11.merchantid


 LEFT Join 
 (
   select * from
  (select distinct
   merchant_mid,
   aadhar_status,
   pan_status,
   gst_status,
   row_number() over (
    partition by
     merchant_mid
    order by
     last_updated desc
   ) as r_num
  from
   cdp_risk_transform.mid_metrics_limits_framework_snapshot_v3)
   where r_num = 1
 ) as m_12 ON m_1.mid = m_12.merchant_mid

left join

(select
entity_id,
gstin,
pan_no_personal,
pan_no_business,
ifsc_code,
bank_acc_no,
signatory_name,
regexp_replace(bank_name,',','') as bank_name,
regexp_replace(regexp_replace(bank_account_name,',',''),'\n','') as bank_account_name
from pg.merchant_kyc_details_snapshot_v3
where  dl_last_updated > date'2010-01-01') pngst on
m_2.entity_id = pngst.entity_id 


left join
(select pg_mid mid,
lat_long,
complete_latitude,
complete_longitude
from datalake.fact_vmn_srs) lat_lon1 on m_1.mid = lat_lon1.mid

left join

(select  
mid,
lat,
lon
from
datalake.dim_merchant_cdo) lat_lon2 on m_1.mid = lat_lon2.mid

left join

(select * from 
(select mid.solution_value as pg_mid,
ubm.solution_type,ubm.id,
ubm.channel,
ubm.channel_actual,
ubm.created_at,
ubm.creator_cust_id,  aud.agent_id, aud.agent_name, aud.agent_type, 
aud.agent_cust_id, aud.agent_team, us.phone_number,
row_number() over (partition by mid.solution_value order by ubm.created_at desc) as rn
from
(select solution_id, solution_value
from onboarding_engine.solution_additional_info_snapshot_v3
where dl_last_updated<=current_date
and solution_key='PG_MID') mid
left join onboarding_engine.related_business_solution_mapping_snapshot_v3 rbsm ON mid.solution_id=rbsm.solution_id
left join onboarding_engine.v_ubm_snapshot_v3 ubm ON rbsm.id=ubm.rdsmid
left join onboarding_engine.audit_trail_snapshot_v3 aud ON ubm.audit_trail_id=aud.id
left join onboarding_engine.v_users_snapshot_v3 us ON aud.agent_cust_id=CAST(us.cust_id as VARCHAR)
where (ubm.solution_type in ('diy_mco','p2p_100k','unified_payment_merchant','offline_50k','qr_merchant','map_edc') OR
(ubm.solution_type='merchant_common_onboard' and ubm.solution_type_level_2 is null))
and rbsm.dl_last_updated<=current_date and ubm.dl_last_updated<=current_date and aud.dl_last_updated<=current_date
and us.dl_last_updated<=current_date
)
where rn=1) ag  ON m_1.mid = ag.pg_mid

left join

(SELECT distinct entity_info_id entity_id, contact_mobile, primary_email, secondary_email
 FROM pg.entity_contact_info_snapshot_v3 WHERE 
dl_last_updated >= DATE '2010-01-01') eml on eml.entity_id = m_2.entity_id


left join
 (SELECT  distinct upi_mcc_code mcc,
          category ,
          sub_category  FROM paytmpgdb.mcc_code_mapping_snapshot_v3) mcc 
          on m_2.category = mcc.category and m_2.sub_category = mcc.sub_category

left join

(SELECT DISTINCT CAST(uid AS VARCHAR) AS merchant_user_id, mid
  FROM pg.uid_eid_mapper_snapshot_v3
  WHERE is_merchant = 1) um on um.mid = m_1.mid
)