// Phase 1: Flash alert/email info
== Daily updates ==
/*(PART 1/3) ALERTS DATA (V_CIMS_BASIC.CSID_ALRT_DTL) Populated by Flash-ProdDataToEDS-batch */
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_FLASH_ALERT_MTH_OTHER AS
SELECT
Current_Date AS UPDATE_DT,
A.EVNT_SUBJ_DSC,
A.ALERT_MTH,
Count ( * ) AS NBR
FROM
( SELECT
T.EVNT_SUBJ_DSC,
/*, CAST(T.EVNT_TMS AS DATE ) AS ALERT_DATE*/
/*====USE CRTE_TMS PER KALEY’S EMAIL ON 5/5/2020===*/
Cast ( T.CRTE_TMS AS DATE ) AS ALERT_DATE,
Last_Day ( Cast ( T.CRTE_TMS AS DATE ) ) AS ALERT_MTH
FROM V_SOT_DIRBNK_CARD_CRD.CSID_ALRT_DTL T
–WHERE ALERT_MTH >= Last_Day(Add_Months(Current_Date, -12))
) A
GROUP BY 1,2,3
ORDER BY 1,2,3
;
/*(PART 2/3) ALERTS WELCOME & RE-ACTIVATION (V_IDS.CUST_ACCT_PFR )*/
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_FLASH_ALERT_MTH_WLCM AS
SELECT
Current_Date AS UPDATE_DT,
‘WELCOME & REACT’ AS EVNT_SUBJ_DSC, /*CREATE EVENT SUBJECT */
Last_Day(A.EMAIL_ENRLL_DT) AS ALERT_MTH,
Count(*) AS NBR
FROM
(
SELECT EMAIL_ENRLL_DT
FROM V_SOT_DIRBNK_CARD_CRD.CUST_ACCT_PFR
WHERE EMAIL_ID>0 /**EMAIL ONLY**/
AND PREFR_CTGY_RUL_ID=’127′ /**IDENTITY ALERT*/
AND EMAIL_PREFR_IND=’Y’
AND EMAIL_ENRLL_DT >= Last_Day(Add_Months(Current_Date, -12))
/*IF THE ACCT HAS > 1 RECORDS ACROSS MONTHS, KEEP ONE FOR EACH MONTH*/
QUALIFY Row_Number() Over (PARTITION BY TRAN_FIN_AGRMNT_KEY, PREFR_CTGY_RUL_ID, Last_Day(EMAIL_ENRLL_DT) ORDER BY PROC_DT DESC,TRAN_TRSFR_ACCT_SEQ_NBR DESC ) = 1
) AS A
GROUP BY 1,2,3
ORDER BY 1,2,3
;
/*(PART 3/3) ALERTS DE-ACTIVATION (V_IDS.CUST_ACCT_PFR )*/
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_FLASH_ALERT_MTH_DEACT AS
SELECT
Current_Date AS UPDATE_DT,
‘DEACTIVATE’ AS EVNT_SUBJ_DSC,
Last_Day(A.EMAIL_UNENRLL_DT) AS ALERT_MTH,
Count(*) AS NBR
FROM
(SELECT
TRAN_FIN_AGRMNT_KEY,
TRAN_TRSFR_ACCT_SEQ_NBR,
/*PREFR_CTGY_RUL_ID,*/
PROC_DT,
/*EMAIL_ID,*/
EMAIL_PREFR_IND,
EMAIL_ENRLL_DT,
EMAIL_UNENRLL_DT
FROM V_SOT_DIRBNK_CARD_CRD.CUST_ACCT_PFR
WHERE EMAIL_ID>0 /**EMAIL ONLY**/
AND PREFR_CTGY_RUL_ID=’127′ /**IDENTITY ALERT*/
AND EMAIL_PREFR_IND=’N’
AND EMAIL_UNENRLL_DT >= Last_Day(Add_Months(Current_Date, -3))
QUALIFY Row_Number() Over (PARTITION BY TRAN_FIN_AGRMNT_KEY , PREFR_CTGY_RUL_ID, TRAN_TRSFR_ACCT_SEQ_NBR ORDER BY PROC_DT DESC) = 1
)AS A
GROUP BY 1,2,3
ORDER BY 1,2,3
;
//COMBINE ALERTS, WELCOME & RE-ACTIVATION, AND DEACTIVATION
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_FLASH_ALERT_MTH_FINAL AS
SELECT
UPDATE_DT,
–EVNT_SUBJ_DSC,
CASE
WHEN EVNT_SUBJ_DSC ILIKE ANY (‘INQUIRY’, ‘EXPERIAN – INQUIRY – NEWINQUIRY’) THEN ‘BTSSNINQUIRY’
WHEN EVNT_SUBJ_DSC ILIKE ANY (‘EXPERIAN – NEW ACCOUNT – NEW TRADE’, ‘EXPERIAN – NEW ACCOUNT – NEWTRADE’) THEN ‘BTSSNNEWACCT’
WHEN EVNT_SUBJ_DSC ILIKE ANY (‘CSID CYBER AGENT REPORT’, ‘CSID CYBER AGENT ALERT’) THEN ‘BTSSNALERT’
WHEN EVNT_SUBJ_DSC ILIKE ANY (‘WELCOME & REACT’) THEN ‘BTSSNWELCOME & BTSSNREACT’
WHEN EVNT_SUBJ_DSC ILIKE ANY (‘DEACTIVATE’) THEN ‘BTSSNDEACT’
ELSE ”
END AS EMAIL_FORM_CDE,
LHS.ALERT_MTH AS PERF_MTHLY_DT,
Sum(NBR) AS NBR
FROM (
SELECT * FROM JHCHOI_FLASH_ALERT_MTH_OTHER
UNION
SELECT * FROM JHCHOI_FLASH_ALERT_MTH_WLCM
UNION
SELECT * FROM JHCHOI_FLASH_ALERT_MTH_DEACT
WHERE ALERT_MTH >= Last_Day(Add_Months(Current_Date, -12))
) LHS
WHERE EMAIL_FORM_CDE IS NOT NULL
GROUP BY 1,2,3;
// Phase 2: DMSS Email info
// PART 1/3: EMAIL IN DMSS (BTSSN%)
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_EMAIL_MTHLY_BTSSN AS
SELECT
Current_Date AS UPDATE_DT,
EMAIL_FORM_CDE,
PERF_MTHLY_DT,
Count ( * ) AS NBR
//FROM V_SOT_DIRBNK_CARD_CRD.VC_VPO_SLCTN_CNTCT
FROM V_SOT_DIRBNK_CARD_CRD.VPO_SLCTN_CNTCT
WHERE EMAIL_FORM_CDE LIKE ‘BTSSN%’
GROUP BY 1,2,3
;
// PART 2/3: EMAIL IN DMSS (ELU)
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_EMAIL_MTHLY_ELU AS
SELECT
Current_Date AS UPDATE_DT,
EMAIL_FORM_CDE,
PERF_MTHLY_DT,
Count ( * ) AS NBR
//FROM V_SOT_DIRBNK_CARD_CRD.VC_VPO_SLCTN_CNTCT
FROM V_SOT_DIRBNK_CARD_CRD.VPO_SLCTN_CNTCT
WHERE EMAIL_FORM_CDE IN (‘CON-102476′,’CON-102495′,’CAL-102469’) // Specific to ELU. In order, codes mean Automatic Opt Outs – PCM Change, Automatic Opt Outs – Ineligible Status, Credit Failures
GROUP BY 1,2,3
;
// PART 3/3: EMAIL PLU
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_EMAIL_MTHLY_PLU AS
SELECT
Current_Date AS UPDATE_DT,
‘CON-102475’ AS EMAIL_FORM_CDE, // Specific to PLU, Automatic Opt Outs – Email Removal
PERF_MTHLY_DT,
Count ( * ) AS NBR
FROM
( SELECT
Last_Day ( LTR_REQ_PROC_DT ) AS PERF_MTHLY_DT
FROM V_SOT_DIRBNK_CARD_CRD.LTR_REQ_EVNT T
WHERE LTR_DTL_KEY = 13822 ) A
GROUP BY 1,2,3
;
//COMBINE EMAILS
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_EMAIL_MTHLY AS
SELECT * FROM JHCHOI_EMAIL_MTHLY_BTSSN
UNION
SELECT * FROM JHCHOI_EMAIL_MTHLY_ELU
UNION
SELECT * FROM JHCHOI_EMAIL_MTHLY_PLU
WHERE PERF_MTHLY_DT >= Last_Day(Add_Months(Current_Date, -12))
AND EMAIL_FORM_CDE NOT IN (‘BTSSNINQSUM’,’BTSSNSUMMARY’)
;
// Show relevant tables for research
// Variance calculation comes from EMAIL_MTHLY / FLASH_ALERT_MTH_FINAL – 1
select * from JHCHOI_EMAIL_MTHLY; // Numerator in variance
Select * from JHCHOI_FLASH_ALERT_MTH_FINAL; // Denominator in variance
Whats new today [[User:Carthik.zee|Carthik.zee]] ([[User talk:Carthik.zee#top|talk]]) 16:48, 21 July 2025 (UTC)
==[[Wikipedia:Criteria for speedy deletion|Speedy deletion]] nomination of [[:User:Carthik.zee]]==
==[[Wikipedia:Criteria for speedy deletion|Speedy deletion]] nomination of [[:User:Carthik.zee]]==
[[File:Information icon4.svg|48px|left|alt=|link=]]
[[File:Information icon4.svg|48px|left|alt=|link=]]
// Phase 1: Flash alert/email info
/*(PART 1/3) ALERTS DATA (V_CIMS_BASIC.CSID_ALRT_DTL) Populated by Flash-ProdDataToEDS-batch */
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_FLASH_ALERT_MTH_OTHER AS
SELECT
Current_Date AS UPDATE_DT,
A.EVNT_SUBJ_DSC,
A.ALERT_MTH,
Count ( * ) AS NBR
FROM
( SELECT
T.EVNT_SUBJ_DSC,
/*, CAST(T.EVNT_TMS AS DATE ) AS ALERT_DATE*/
/*====USE CRTE_TMS PER KALEY'S EMAIL ON 5/5/2020===*/
Cast ( T.CRTE_TMS AS DATE ) AS ALERT_DATE,
Last_Day ( Cast ( T.CRTE_TMS AS DATE ) ) AS ALERT_MTH
FROM V_SOT_DIRBNK_CARD_CRD.CSID_ALRT_DTL T
–WHERE ALERT_MTH >= Last_Day(Add_Months(Current_Date, -12))
) A
GROUP BY 1,2,3
ORDER BY 1,2,3
/*(PART 2/3) ALERTS WELCOME & RE-ACTIVATION (V_IDS.CUST_ACCT_PFR )*/
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_FLASH_ALERT_MTH_WLCM AS
SELECT
Current_Date AS UPDATE_DT,
'WELCOME & REACT' AS EVNT_SUBJ_DSC, /*CREATE EVENT SUBJECT */
Last_Day(A.EMAIL_ENRLL_DT) AS ALERT_MTH,
Count(*) AS NBR
FROM
(
SELECT EMAIL_ENRLL_DT
FROM V_SOT_DIRBNK_CARD_CRD.CUST_ACCT_PFR
WHERE EMAIL_ID>0 /**EMAIL ONLY**/
AND PREFR_CTGY_RUL_ID='127' /**IDENTITY ALERT*/
AND EMAIL_PREFR_IND='Y'
AND EMAIL_ENRLL_DT >= Last_Day(Add_Months(Current_Date, -12))
/*IF THE ACCT HAS > 1 RECORDS ACROSS MONTHS, KEEP ONE FOR EACH MONTH*/
QUALIFY Row_Number() Over (PARTITION BY TRAN_FIN_AGRMNT_KEY, PREFR_CTGY_RUL_ID, Last_Day(EMAIL_ENRLL_DT) ORDER BY PROC_DT DESC,TRAN_TRSFR_ACCT_SEQ_NBR DESC ) = 1
) AS A
GROUP BY 1,2,3
ORDER BY 1,2,3
/*(PART 3/3) ALERTS DE-ACTIVATION (V_IDS.CUST_ACCT_PFR )*/
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_FLASH_ALERT_MTH_DEACT AS
SELECT
Current_Date AS UPDATE_DT,
'DEACTIVATE' AS EVNT_SUBJ_DSC,
Last_Day(A.EMAIL_UNENRLL_DT) AS ALERT_MTH,
Count(*) AS NBR
FROM
(SELECT
TRAN_FIN_AGRMNT_KEY,
TRAN_TRSFR_ACCT_SEQ_NBR,
/*PREFR_CTGY_RUL_ID,*/
PROC_DT,
/*EMAIL_ID,*/
EMAIL_PREFR_IND,
EMAIL_ENRLL_DT,
EMAIL_UNENRLL_DT
FROM V_SOT_DIRBNK_CARD_CRD.CUST_ACCT_PFR
WHERE EMAIL_ID>0 /**EMAIL ONLY**/
AND PREFR_CTGY_RUL_ID='127' /**IDENTITY ALERT*/
AND EMAIL_PREFR_IND='N'
AND EMAIL_UNENRLL_DT >= Last_Day(Add_Months(Current_Date, -3))
QUALIFY Row_Number() Over (PARTITION BY TRAN_FIN_AGRMNT_KEY , PREFR_CTGY_RUL_ID, TRAN_TRSFR_ACCT_SEQ_NBR ORDER BY PROC_DT DESC) = 1
)AS A
GROUP BY 1,2,3
ORDER BY 1,2,3
//COMBINE ALERTS, WELCOME & RE-ACTIVATION, AND DEACTIVATION
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_FLASH_ALERT_MTH_FINAL AS
SELECT
UPDATE_DT,
–EVNT_SUBJ_DSC,
CASE
WHEN EVNT_SUBJ_DSC ILIKE ANY ('INQUIRY', 'EXPERIAN - INQUIRY - NEWINQUIRY') THEN 'BTSSNINQUIRY'
WHEN EVNT_SUBJ_DSC ILIKE ANY ('EXPERIAN - NEW ACCOUNT - NEW TRADE', 'EXPERIAN - NEW ACCOUNT - NEWTRADE') THEN 'BTSSNNEWACCT'
WHEN EVNT_SUBJ_DSC ILIKE ANY ('CSID CYBER AGENT REPORT', 'CSID CYBER AGENT ALERT') THEN 'BTSSNALERT'
WHEN EVNT_SUBJ_DSC ILIKE ANY ('WELCOME & REACT') THEN 'BTSSNWELCOME & BTSSNREACT'
WHEN EVNT_SUBJ_DSC ILIKE ANY ('DEACTIVATE') THEN 'BTSSNDEACT'
ELSE
END AS EMAIL_FORM_CDE,
LHS.ALERT_MTH AS PERF_MTHLY_DT, Sum(NBR) AS NBR
FROM (
SELECT * FROM JHCHOI_FLASH_ALERT_MTH_OTHER
UNION
SELECT * FROM JHCHOI_FLASH_ALERT_MTH_WLCM
UNION
SELECT * FROM JHCHOI_FLASH_ALERT_MTH_DEACT
WHERE ALERT_MTH >= Last_Day(Add_Months(Current_Date, -12))
) LHS
WHERE EMAIL_FORM_CDE IS NOT NULL
GROUP BY 1,2,3;
// Phase 2: DMSS Email info
// PART 1/3: EMAIL IN DMSS (BTSSN%)
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_EMAIL_MTHLY_BTSSN AS
SELECT
Current_Date AS UPDATE_DT,
EMAIL_FORM_CDE,
PERF_MTHLY_DT,
Count ( * ) AS NBR
//FROM V_SOT_DIRBNK_CARD_CRD.VC_VPO_SLCTN_CNTCT
FROM V_SOT_DIRBNK_CARD_CRD.VPO_SLCTN_CNTCT
WHERE EMAIL_FORM_CDE LIKE ‘BTSSN%’
GROUP BY 1,2,3
// PART 2/3: EMAIL IN DMSS (ELU)
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_EMAIL_MTHLY_ELU AS
SELECT
Current_Date AS UPDATE_DT,
EMAIL_FORM_CDE,
PERF_MTHLY_DT,
Count ( * ) AS NBR
//FROM V_SOT_DIRBNK_CARD_CRD.VC_VPO_SLCTN_CNTCT
FROM V_SOT_DIRBNK_CARD_CRD.VPO_SLCTN_CNTCT
WHERE EMAIL_FORM_CDE IN (‘CON-102476′,’CON-102495′,’CAL-102469’) // Specific to ELU. In order, codes mean Automatic Opt Outs – PCM Change, Automatic Opt Outs – Ineligible Status, Credit Failures
GROUP BY 1,2,3
// PART 3/3: EMAIL PLU
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_EMAIL_MTHLY_PLU AS
SELECT
Current_Date AS UPDATE_DT,
'CON-102475' AS EMAIL_FORM_CDE, // Specific to PLU, Automatic Opt Outs - Email Removal
PERF_MTHLY_DT,
Count ( * ) AS NBR
FROM
( SELECT
Last_Day ( LTR_REQ_PROC_DT ) AS PERF_MTHLY_DT
FROM V_SOT_DIRBNK_CARD_CRD.LTR_REQ_EVNT T
WHERE LTR_DTL_KEY = 13822 ) A
GROUP BY 1,2,3
//COMBINE EMAILS
CREATE OR REPLACE TEMPORARY TABLE JHCHOI_EMAIL_MTHLY AS
SELECT * FROM JHCHOI_EMAIL_MTHLY_BTSSN
UNION
SELECT * FROM JHCHOI_EMAIL_MTHLY_ELU
UNION
SELECT * FROM JHCHOI_EMAIL_MTHLY_PLU
WHERE PERF_MTHLY_DT >= Last_Day(Add_Months(Current_Date, -12))
AND EMAIL_FORM_CDE NOT IN ('BTSSNINQSUM','BTSSNSUMMARY')
// Show relevant tables for research
// Variance calculation comes from EMAIL_MTHLY / FLASH_ALERT_MTH_FINAL – 1
select * from JHCHOI_EMAIL_MTHLY; // Numerator in variance
Select * from JHCHOI_FLASH_ALERT_MTH_FINAL; // Denominator in variance
Hello, and welcome to Wikipedia. A tag has been placed on User:Carthik.zee requesting that it be speedily deleted from Wikipedia. This has been done under section U5 of the criteria for speedy deletion, because the page appears to consist of writings, information, discussions, or activities not closely related to Wikipedia’s goals. Please note that Wikipedia is not a free web hosting service. Under the criteria for speedy deletion, such pages may be deleted at any time.
If you think this page should not be deleted for this reason, you may contest the nomination by visiting the page and clicking the button labelled “Contest this speedy deletion”. This will give you the opportunity to explain why you believe the page should not be deleted. However, be aware that once a page is tagged for speedy deletion, it may be deleted without delay. Please do not remove the speedy deletion tag from the page yourself, but do not hesitate to add information in line with Wikipedia’s policies and guidelines. If the page is deleted, and you wish to retrieve the deleted material for future reference or improvement, then please contact the deleting administrator, or if you have already done so, you can place a request here. LakesideMinersCome Talk To Me! 15:13, 7 October 2025 (UTC)
Hello, and welcome to Wikipedia. A tag has been placed on User:Carthik.zee requesting that it be speedily deleted from Wikipedia. This has been done under section U5 of the criteria for speedy deletion, because the page appears to consist of writings, information, discussions, or activities not closely related to Wikipedia’s goals. Please note that Wikipedia is not a free web hosting service. Under the criteria for speedy deletion, such pages may be deleted at any time.
If you think this page should not be deleted for this reason, you may contest the nomination by visiting the page and clicking the button labelled “Contest this speedy deletion”. This will give you the opportunity to explain why you believe the page should not be deleted. However, be aware that once a page is tagged for speedy deletion, it may be deleted without delay. Please do not remove the speedy deletion tag from the page yourself, but do not hesitate to add information in line with Wikipedia’s policies and guidelines. If the page is deleted, and you wish to retrieve the deleted material for future reference or improvement, then please contact the deleting administrator, or if you have already done so, you can place a request here. LakesideMinersCome Talk To Me! 15:33, 7 October 2025 (UTC)
