Untitled

avatar
unknown
plain_text
a year ago
12 kB
7
Indexable
WITH getRecommendationsV1_data AS (
  SELECT c.*,
  CAST(json_extract_SCALAR(APPLICATION_to_APPROVAL_Funnel_intermediatePredictions,"$.0.counter_atc_score") as float64) as CO_counter_atc_score,
CAST(json_extract_SCALAR(APPLICATION_to_APPROVAL_Funnel_intermediatePredictions,"$.1.base_ao_score") as float64) as CO_base_ata_score,
CAST(json_extract_SCALAR(APPLICATION_to_APPROVAL_Funnel_intermediatePredictions,"$.1.counter_scaled_atc_score") as float64) as CO_scaled_atc_score,
CAST(json_extract_SCALAR(APPLICATION_to_APPROVAL_Funnel_intermediatePredictions,"$.1.cpa_ratio") as float64) as CO_cpa_ratio,
CAST(json_extract_SCALAR(NONE_to_NONE_LightboxCutoff_intermediatePredictions,"$.0.lb_cutoff_score") as float64) as lb_cutoff_score
  FROM `ck-ds-spark.recsys_regression_test.recrecord_parser_rec_prd_47e7b9de575f5138_predictions_20240716` c
),
getRecommendations_data AS (
  SELECT c.*,
    CAST(json_extract_SCALAR(APPLICATION_to_APPROVAL_Funnel_intermediatePredictions,"$.0.counter_atc_score") as float64) as CO_counter_atc_score,
CAST(json_extract_SCALAR(APPLICATION_to_APPROVAL_Funnel_intermediatePredictions,"$.1.base_ao_score") as float64) as CO_base_ata_score,
CAST(json_extract_SCALAR(APPLICATION_to_APPROVAL_Funnel_intermediatePredictions,"$.1.counter_scaled_atc_score") as float64) as CO_scaled_atc_score,
CAST(json_extract_SCALAR(APPLICATION_to_APPROVAL_Funnel_intermediatePredictions,"$.1.cpa_ratio") as float64) as CO_cpa_ratio,
CAST(json_extract_SCALAR(NONE_to_NONE_LightboxCutoff_intermediatePredictions,"$.0.lb_cutoff_score") as float64) as lb_cutoff_score
  FROM `ck-ds-spark.recsys_regression_test.recrecord_parser_rec_new_47e7b9de575f5137_dark__predictions_20240716` c
)

select * from (
SELECT count(*) as cnt,
v1.recommenderRequestId,
v1.contentId,
	sum(case when	(v1.APPLICATION_to_APPROVAL_Eligibility = v2.APPLICATION_to_APPROVAL_Eligibility) or (v1.APPLICATION_to_APPROVAL_Eligibility is NULL and v2.APPLICATION_to_APPROVAL_Eligibility is NULL) then 1 else 0 end)/count(*) AS APPLICATION_to_APPROVAL_Eligibility,
  sum(case when	(v1.APPLICATION_to_APPROVAL_Eligibility_modelNames = v2.APPLICATION_to_APPROVAL_Eligibility_modelNames) or (v1.APPLICATION_to_APPROVAL_Eligibility_modelNames is NULL and v2.APPLICATION_to_APPROVAL_Eligibility_modelNames is NULL) then 1 else 0 end)/count(*) AS APPLICATION_to_APPROVAL_Eligibility_modelNames,
  sum(case when	(v1.APPLICATION_to_APPROVAL_Funnel = v2.APPLICATION_to_APPROVAL_Funnel) or (v1.APPLICATION_to_APPROVAL_Funnel is NULL and v2.APPLICATION_to_APPROVAL_Funnel is NULL) then 1 else 0 end)/count(*) AS APPLICATION_to_APPROVAL_Funnel,
  sum(case when	(v1.APPLICATION_to_APPROVAL_Funnel_modelNames = v2.APPLICATION_to_APPROVAL_Funnel_modelNames) or (v1.APPLICATION_to_APPROVAL_Funnel_modelNames is NULL and v2.APPLICATION_to_APPROVAL_Funnel_modelNames is NULL) then 1 else 0 end)/count(*) AS APPLICATION_to_APPROVAL_Funnel,
    sum(case when	(v1.APPROVAL_to_FUNDING_Funnel = v2.APPROVAL_to_FUNDING_Funnel) or (v1.APPROVAL_to_FUNDING_Funnel is NULL and v2.APPROVAL_to_FUNDING_Funnel is NULL) then 1 else 0 end)/count(*) AS APPROVAL_to_FUNDING_Funnel,
    sum(case when	(v1.APPROVAL_to_FUNDING_Funnel_modelNames = v2.APPROVAL_to_FUNDING_Funnel_modelNames) or (v1.APPROVAL_to_FUNDING_Funnel_modelNames is NULL and v2.APPROVAL_to_FUNDING_Funnel_modelNames is NULL) then 1 else 0 end)/count(*) AS APPROVAL_to_FUNDING_Funnel,
    sum(case when (v1.ApprovalBadge_Eligibility = v2.ApprovalBadge_Eligibility ) or (v1.ApprovalBadge_Eligibility is NULL and v2.ApprovalBadge_Eligibility is NULL) then 1 else 0 end)/count(*) AS ApprovalBadge_Eligibility,
    sum(case when (abs(v1.CLICK_to_APPLICATION_Funnel-v2.CLICK_to_APPLICATION_Funnel)<=1e-4) or (v1.CLICK_to_APPLICATION_Funnel is NULL and v2.CLICK_to_APPLICATION_Funnel is NULL) or (v1.CLICK_to_APPLICATION_Funnel_modelNames NOT LIKE '%tennessee%') or (v2.CLICK_to_APPLICATION_Funnel_modelNames NOT LIKE '%tennessee%') then 1 else 0 end)/count(*) AS CLICK_to_APPLICATION_Funnel,
    sum(case when (v1.CLICK_to_APPLICATION_Funnel_modelNames = v2.CLICK_to_APPLICATION_Funnel_modelNames ) or (v1.CLICK_to_APPLICATION_Funnel_modelNames is NULL and v2.CLICK_to_APPLICATION_Funnel_modelNames is NULL) or (v1.CLICK_to_APPLICATION_Funnel_modelNames NOT LIKE '%tennessee%') or (v2.CLICK_to_APPLICATION_Funnel_modelNames NOT LIKE '%tennessee%') then 1 else 0 end)/count(*) AS CLICK_to_APPLICATION_Funnel_modelNames,
    sum(case when (abs(v1.IMPRESSION_to_CLICK_Funnel-v2.IMPRESSION_to_CLICK_Funnel)<=1e-4 ) or (v1.IMPRESSION_to_CLICK_Funnel is NULL and v2.IMPRESSION_to_CLICK_Funnel is NULL) or (v1.IMPRESSION_to_CLICK_Funnel_modelNames NOT LIKE '%tennessee%') or (v2.IMPRESSION_to_CLICK_Funnel_modelNames NOT LIKE '%tennessee%') then 1 else 0 end)/count(*) AS IMPRESSION_to_CLICK_Funnel,
    sum(case when (v1.IMPRESSION_to_CLICK_Funnel_modelNames = v2.IMPRESSION_to_CLICK_Funnel_modelNames ) or (v1.IMPRESSION_to_CLICK_Funnel_modelNames is NULL and v2.IMPRESSION_to_CLICK_Funnel_modelNames is NULL or (v1.IMPRESSION_to_CLICK_Funnel_modelNames NOT LIKE '%tennessee%') or (v2.IMPRESSION_to_CLICK_Funnel_modelNames NOT LIKE '%tennessee%')) then 1 else 0 end)/count(*) AS IMPRESSION_to_CLICK_Funnel_modelNames,
    sum(case when (v1.NONE_to_NONE_CreditLimit = v2.NONE_to_NONE_CreditLimit ) or (v1.NONE_to_NONE_CreditLimit is NULL and v2.NONE_to_NONE_CreditLimit is NULL) then 1 else 0 end)/count(*) AS NONE_to_NONE_CreditLimit,
    sum(case when (v1.NONE_to_NONE_CreditLimit_modelNames = v2.NONE_to_NONE_CreditLimit_modelNames ) or (v1.NONE_to_NONE_CreditLimit_modelNames is NULL and v2.NONE_to_NONE_CreditLimit_modelNames is NULL) then 1 else 0 end)/count(*) AS NONE_to_NONE_CreditLimit_modelNames,
    sum(case when (v1.NONE_to_NONE_LightboxCutoff = v2.NONE_to_NONE_LightboxCutoff ) or (v1.NONE_to_NONE_LightboxCutoff is NULL and v2.NONE_to_NONE_LightboxCutoff is NULL) then 1 else 0 end)/count(*) AS NONE_to_NONE_LightboxCutoff,
    sum(case when (v1.NONE_to_NONE_LightboxCutoff_modelNames = v2.NONE_to_NONE_LightboxCutoff_modelNames ) or (v1.NONE_to_NONE_LightboxCutoff_modelNames is NULL and v2.NONE_to_NONE_LightboxCutoff_modelNames is NULL) then 1 else 0 end)/count(*) AS NONE_to_NONE_LightboxCutoff_modelNames,
    sum(case when (v1.NONE_to_NONE_LoanAmount = v2.NONE_to_NONE_LoanAmount ) or (v1.NONE_to_NONE_LoanAmount is NULL and v2.NONE_to_NONE_LoanAmount is NULL) then 1 else 0 end)/count(*) AS NONE_to_NONE_LoanAmount,
    sum(case when (v1.NONE_to_NONE_LoanAmount_modelNames = v2.NONE_to_NONE_LoanAmount_modelNames ) or (v1.NONE_to_NONE_LoanAmount_modelNames is NULL and v2.NONE_to_NONE_LoanAmount_modelNames is NULL) then 1 else 0 end)/count(*) AS NONE_to_NONE_LoanAmount_modelNames,
    sum(case when (v1.NONE_to_NONE_TimeToFund = v2.NONE_to_NONE_TimeToFund ) or (v1.NONE_to_NONE_TimeToFund is NULL and v2.NONE_to_NONE_TimeToFund is NULL) then 1 else 0 end)/count(*) AS NONE_to_NONE_TimeToFund,
    sum(case when (v1.NONE_to_NONE_TimeToFund_modelNames = v2.NONE_to_NONE_TimeToFund_modelNames ) or (v1.NONE_to_NONE_TimeToFund_modelNames is NULL and v2.NONE_to_NONE_TimeToFund_modelNames is NULL) then 1 else 0 end)/count(*) AS NONE_to_NONE_TimeToFund_modelNames,
    sum(case when (v1.approvalOddsModel = v2.approvalOddsModel ) or (v1.approvalOddsModel is NULL and v2.approvalOddsModel is NULL) then 1 else 0 end)/count(*) AS approvalOddsModel,
  sum(case when (v1.contentRank = v2.contentRank ) or (v1.contentRank is NULL and v2.contentRank is NULL) then 1 else 0 end)/count(*) AS contentRank,
  sum(case when (v1.offerMultiplier = v2.offerMultiplier ) or (v1.offerMultiplier is NULL and v2.offerMultiplier is NULL) then 1 else 0 end)/count(*) AS offerMultiplier,
  sum(case when (v1.offerPricing = v2.offerPricing ) or (v1.offerPricing is NULL and v2.offerPricing is NULL) then 1 else 0 end)/count(*) AS offerPricing,
  sum(case when (v1.opt_weightset = v2.opt_weightset ) or (v1.opt_weightset is NULL and v2.opt_weightset is NULL) then 1 else 0 end)/count(*) AS opt_weightset,
    sum(case when (v1.storyExploreModel = v2.storyExploreModel ) or (v1.storyExploreModel is NULL and v2.storyExploreModel is NULL) then 1 else 0 end)/count(*) AS storyExploreModel,
    sum(case when (v1.storyModel = v2.storyModel ) or (v1.storyModel is NULL and v2.storyModel is NULL) then 1 else 0 end)/count(*) AS storyModel,
    sum(case when (v1.targetingExploreModel = v2.targetingExploreModel ) or (v1.targetingExploreModel is NULL and v2.targetingExploreModel is NULL) then 1 else 0 end)/count(*) AS targetingExploreModel,
    sum(case when (v1.targetingModel = v2.targetingModel ) or (v1.targetingModel is NULL and v2.targetingModel is NULL) then 1 else 0 end)/count(*) AS targetingModel,
    sum(case when (v1.vantage3 = v2.vantage3 ) or (v1.vantage3 is NULL and v2.vantage3 is NULL) then 1 else 0 end)/count(*) AS vantage3,
    sum(case when (v1.verticalName = v2.verticalName ) or (v1.verticalName is NULL and v2.verticalName is NULL) then 1 else 0 end)/count(*) AS verticalName,
    sum(case when (v1.APPLICATION_to_APPROVAL_Pricing = v2.APPLICATION_to_APPROVAL_Pricing ) or (v1.APPLICATION_to_APPROVAL_Pricing is NULL and v2.APPLICATION_to_APPROVAL_Pricing is NULL) then 1 else 0 end)/count(*) AS APPLICATION_to_APPROVAL_Pricing,
    sum(case when (v1.APPLICATION_to_APPROVAL_Pricing_modelNames = v2.APPLICATION_to_APPROVAL_Pricing_modelNames ) or (v1.APPLICATION_to_APPROVAL_Pricing_modelNames is NULL and v2.APPLICATION_to_APPROVAL_Pricing_modelNames is NULL) then 1 else 0 end)/count(*) AS APPLICATION_to_APPROVAL_Pricing_modelNames,
    sum(case when (v1.ApprovalBadge_Pricing = v2.ApprovalBadge_Pricing ) or (v1.ApprovalBadge_Pricing is NULL and v2.ApprovalBadge_Pricing is NULL) then 1 else 0 end)/count(*) AS ApprovalBadge_Pricing,
    sum(case when (v1.NONE_to_NONE_OfferSuppression = v2.NONE_to_NONE_OfferSuppression ) or (v1.NONE_to_NONE_OfferSuppression is NULL and v2.NONE_to_NONE_OfferSuppression is NULL) then 1 else 0 end)/count(*) AS NONE_to_NONE_OfferSuppression,
    sum(case when (v1.NONE_to_NONE_OfferSuppression_modelNames = v2.NONE_to_NONE_OfferSuppression_modelNames ) or (v1.NONE_to_NONE_OfferSuppression_modelNames is NULL and v2.NONE_to_NONE_OfferSuppression_modelNames is NULL) then 1 else 0 end)/count(*) AS NONE_to_NONE_OfferSuppression_modelNames,
    sum(case when (v1.approvalDecision = v2.approvalDecision ) or (v1.approvalDecision is NULL and v2.approvalDecision is NULL) then 1 else 0 end)/count(*) AS approvalDecision,
    sum(case when (v1.monthlyPaymentAmount = v2.monthlyPaymentAmount ) or (v1.monthlyPaymentAmount is NULL and v2.monthlyPaymentAmount is NULL) then 1 else 0 end)/count(*) AS monthlyPaymentAmount,
    sum(case when (v1.CO_counter_atc_score = v2.CO_counter_atc_score ) or (v1.CO_counter_atc_score is NULL and v2.CO_counter_atc_score is NULL) then 1 else 0 end)/count(*) AS CO_counter_atc_score,
sum(case when (v1.CO_base_ata_score = v2.CO_base_ata_score ) or (v1.CO_base_ata_score is NULL and v2.CO_base_ata_score is NULL) then 1 else 0 end)/count(*) AS CO_base_ata_score,
sum(case when (v1.CO_scaled_atc_score = v2.CO_scaled_atc_score ) or (v1.CO_scaled_atc_score is NULL and v2.CO_scaled_atc_score is NULL) then 1 else 0 end)/count(*) AS CO_scaled_atc_score,
sum(case when (v1.CO_cpa_ratio = v2.CO_cpa_ratio ) or (v1.CO_cpa_ratio is NULL and v2.CO_cpa_ratio is NULL) then 1 else 0 end)/count(*) AS CO_cpa_ratio,
sum(case when (v1.lb_cutoff_score = v2.lb_cutoff_score ) or (v1.lb_cutoff_score is NULL and v2.lb_cutoff_score is NULL) then 1 else 0 end)/count(*) AS lb_cutoff_score

FROM
 	getRecommendationsV1_data v1
FULL JOIN
 	getRecommendations_data v2
ON
 	v1.recommenderRequestId = v2.recommenderRequestId AND
	v1.surface = v2.surface AND
 	v1.section = v2.section AND
 	v1.ContentID = v2.ContentID 
WHERE v2.traceId IS NOT NULL
and ((v1.surface in ('find-a-card','credit-card-snipes')
and v1.section = 'cc-debt-to-pl-refi')
or (v1.surface in ('find-a-loan','pl-loans-snipes')))
and v1.flex_strfield26 is not NULL
and v2.flex_strfield26 is not NULL
group by 2,3
) where CLICK_to_APPLICATION_Funnel < 1.0
Editor is loading...
Leave a Comment