Tenant no. of CC used trend
Thu Dec 29 2022 13:14:01 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%jdbc(hive) set tez.queue.name=default; set hive.execution.engine=tez; -- AML RENT: MULTIPLE CARDS USED FOR RENT PAYMENT : MONTHLY with base as (SELECT DISTINCT A.landlord , IF(B.phone_number IS NOT NULL, B.phone_number, NULL) AS landlord_phoneno , IF(C.reciver_vpa IS NOT NULL, C.reciver_vpa, NULL) AS landlord_vpa , IF(B.user_id IS NOT NULL, B.user_id, C.user_id) as landlord_user_id , A.sender , A.txn_id, A.amt --, A.CC , A.month, A.day, A.timee , D.cardId -- , A.eventdata_senderglobalcardid, A.eventdata_receiverbankifsc, A.eventdata_receiverbankaccountsha, A.eventdata_receivermaskaccountid -- , CONCAT(A.eventdata_sendermaskaccountid, " - ", eventdata_senderbankid) AS uniqueCardId FROM (select distinct eventdata_merchantcontext_contactid as landlord , eventdata_transactionid AS txn_id ,eventdata_sender as sender ,eventdata_senderphone ,eventdata_totaltransactionamount/100 as amt -- ,eventdata_senderglobalcardid as CC ,month, day, `time` as timee -- , eventdata_senderglobalcardid, eventdata_receiverbankifsc, eventdata_receiverbankaccountsha , eventdata_sendermaskaccountid, eventdata_senderbankid from foxtrot_stream.payment_backend_transact where 0 = 0 and year = 2022 and month = 11 ------------------- CHANGE THIS IF REQUIRED and eventtype = 'PAYMENT' and eventdata_status = 'COMPLETED' and eventdata_receiveruser = 'PHONEPERENTPAYMENT' and eventdata_sendertype = 'INTERNAL_USER' AND eventdata_workflowtype = 'CONSUMER_TO_MERCHANT' AND eventdata_receiversubtype = 'ONLINE_MERCHANT' AND eventdata_receivertypeinrequest = 'MERCHANT' AND eventdata_receivertype = 'MERCHANT' AND ((LENGTH(eventdata_merchantcontext_contactid) = 10) OR (eventdata_merchantcontext_contactid LIKE '%@ybl') OR (eventdata_merchantcontext_contactid LIKE '%@ibl') OR (eventdata_merchantcontext_contactid LIKE '%@axl')))A LEFT JOIN (SELECT DISTINCT user_ext_id AS user_id, phone_number FROM users.users WHERE blacklisted = 0)B ON A.landlord = B.phone_number LEFT JOIN (SELECT DISTINCT receiveruser as user_id, reciver_vpa FROM fraud.transaction_details_v3 WHERE year(updated_date) = 2022 AND Month(updated_date) = 11 ------------------- CHANGE THIS IF REQUIRED AND errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED' AND ((reciver_vpa LIKE '%@ybl') OR (reciver_vpa LIKE '%@ibl') OR (reciver_vpa LIKE '%@axl')))C ON A.landlord = C.reciver_vpa INNER JOIN (SELECT DISTINCT transaction_id AS txn_id, CONCAT(masked_card_number, " - " , bank_id) AS cardId FROM payment.transaction_payer_sources WHERE year = 2022 AND month = 11 AND masked_card_number IS NOT NULL)D ON A.txn_id = D.txn_id) SELECT CCused, COUNT(DISTINCT sender) as users FROM (SELECT sender, COUNT(DISTINCT cardId) as CCused --, SUM(amt) as totRentPaid FROM base WHERE landlord_user_id IS NOT NULL GROUP BY sender)X GROUP BY CCused
Comments