WITH cte_ranked AS ( SELECT t.*, ROW_NUMBER() OVER ( PARTITION BY t.ml_customer_id ORDER BY t.eligibility_date DESC ) AS rn FROM earlysalary.loan_taken_vkyc_strategy_base t ),

base_latest_date AS ( SELECT * FROM cte_ranked WHERE rn = 1 ),

base_not_in_vkyc AS ( SELECT b.* FROM base_latest_date b LEFT JOIN earlysalary.videokycevent v ON CAST(b.ml_customer_id AS VARCHAR) = v.userid WHERE v.userid IS NULL ),

-- Users who have clicked / active in MoEngage last 7 days base_active_moengage AS ( SELECT DISTINCT bniv.ml_customer_id FROM base_not_in_vkyc bniv INNER JOIN earlysalary.moengage_campaign_data moe ON CAST(bniv.ml_customer_id AS VARCHAR) = moe.event_user_attributes_id WHERE moe.event_event_name IN ( 'Email Clicked', 'MOE_WHATSAPP_CLICKED', 'SMS Clicked', 'Notification Clicked Android', 'Notification Clicked iOS', 'Mobile In-App Clicked' ) AND FROM_UNIXTIME(CAST(moe.event_event_time AS BIGINT)) >= date_add('day', -7, current_date) ),

-- Users who have booked a slot in MoEngage last 7 days base_slot_booked AS ( SELECT DISTINCT bniv.ml_customer_id FROM base_not_in_vkyc bniv INNER JOIN earlysalary.moengage_campaign_data moe ON CAST(bniv.ml_customer_id AS VARCHAR) = moe.event_user_attributes_id WHERE moe.event_event_name = 'vkyc_slot_booked' AND FROM_UNIXTIME(CAST(moe.event_event_time AS BIGINT)) >= date_add('day', -7, current_date) ),

-- Intersection: active AND slot-booked base_active_and_slot AS ( SELECT b1.ml_customer_id FROM base_active_moengage b1 INNER JOIN base_slot_booked b2 ON b1.ml_customer_id = b2.ml_customer_id )

SELECT bas.ml_customer_id, latest.eligibility_date FROM base_active_and_slot bas JOIN base_latest_date latest ON bas.ml_customer_id = latest.ml_customer_id ORDER BY bas.ml_customer_id