Untitled
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