I am working with a customer trying to optimize queries being generated from a Python application. It produces multiple level CTE such as attached below. I've tried some projection tuning and moving around WHERE and groups and joins based on the query plan but I don't seem to be making much of a dent. We've also tried materializing the WITH clauses with no significant change in run. Any other thoughts on how we can improve this query? It currently takes about 15 minutes to run - we have explain and profile if it would help:
WITH loan_per_date_with_calcs AS
(
SELECT COALESCE(charge_off_amount,0) AS charge_off_amount,
COALESCE(charge_off_sale_fees,0) AS charge_off_sale_fees,
COALESCE(charge_off_sale_proceeds,0) AS charge_off_sale_proceeds,
COALESCE(original_loan_principal,0) AS original_principal,
originator AS originator,
originator_loan_id AS originator_loan_id,
period_end_date AS period_end_date,
COALESCE(post_charge_off_recoveries,0) AS post_charge_off_recoveries,
COALESCE(post_charge_off_recovery_fee,0) AS post_charge_off_recovery_fee,
COALESCE(sub_product_type,'Unknown') AS sub_product_type,
COALESCE(unscheduled_principal,0) AS unscheduled_principal_balance,
vantage_score_band AS vantage_score_band,
vintage_band AS vintage_band,
sub_product_type = sub_product_type AS sub_product_type_all_filter,
CASE
WHEN (ROW_NUMBER() OVER (PARTITION BY originator,originator_loan_id ORDER BY period_end_date ASC) = 1) THEN original_principal
ELSE 0
END AS original_principal_row_number_asc_case,
ROW_NUMBER() OVER (PARTITION BY originator,originator_loan_id ORDER BY period_end_date ASC) AS row_number_asc,
ROW_NUMBER() OVER (PARTITION BY originator,originator_loan_id ORDER BY period_end_date ASC) = 1 AS row_number_asc_equals_one
FROM processedtu_unsec_monthly_revised
WHERE period_end_date <= '2018-05-31'
),
stratted AS
(
SELECT loan_per_date_with_calcs.period_end_date AS period_end_date,
loan_per_date_with_calcs.vantage_score_band AS vantage_score_band,
loan_per_date_with_calcs.vintage_band AS vintage_band,
SUM(loan_per_date_with_calcs.charge_off_amount) AS charge_off,
SUM(loan_per_date_with_calcs.charge_off_amount) + SUM(loan_per_date_with_calcs.charge_off_sale_fees) + SUM(loan_per_date_with_calcs.post_charge_off_recovery_fee) AS "charge_off,debt_sale_fees,_recovery_fees",
SUM(loan_per_date_with_calcs.charge_off_sale_fees) AS debt_sale_fees,
SUM(loan_per_date_with_calcs.charge_off_sale_proceeds) AS debt_sale_proceeds,
SUM(loan_per_date_with_calcs.charge_off_sale_proceeds) + SUM(loan_per_date_with_calcs.post_charge_off_recoveries) AS "debt_sale_proceeds,_gross_recoveries",
SUM(loan_per_date_with_calcs.post_charge_off_recoveries) AS gross_recoveries,
(SUM(loan_per_date_with_calcs.charge_off_amount) + SUM(loan_per_date_with_calcs.charge_off_sale_fees) + SUM(loan_per_date_with_calcs.post_charge_off_recovery_fee)) -(SUM(loan_per_date_with_calcs.charge_off_sale_proceeds) + SUM(loan_per_date_with_calcs.post_charge_off_recoveries)) AS losses,
SUM(loan_per_date_with_calcs.original_principal_row_number_asc_case) AS new_loan_original_principal,
SUM(loan_per_date_with_calcs.post_charge_off_recovery_fee) AS recovery_fees,
SUM(loan_per_date_with_calcs.charge_off_sale_fees) + SUM(loan_per_date_with_calcs.post_charge_off_recovery_fee) AS sum_debt_sale_fees_and_recovery_fees,
SUM(loan_per_date_with_calcs.unscheduled_principal_balance) AS unscheduled_principal_received
FROM loan_per_date_with_calcs
GROUP BY loan_per_date_with_calcs.period_end_date,
loan_per_date_with_calcs.vantage_score_band,
loan_per_date_with_calcs.vintage_band
),
cumulative_metrics AS
(
SELECT stratted.period_end_date AS period_end_date,
stratted.vantage_score_band AS vantage_score_band,
stratted.vintage_band AS vintage_band,
stratted.charge_off AS charge_off,
stratted."charge_off,_debt_sale_fees,_recovery_fees" AS "charge_off,_debt_sale_fees,_recovery_fees",
stratted.debt_sale_fees AS debt_sale_fees,
stratted.debt_sale_proceeds AS debt_sale_proceeds,
stratted."debt_sale_proceeds,_gross_recoveries" AS "debt_sale_proceeds,_gross_recoveries",
stratted.gross_recoveries AS gross_recoveries,
stratted.losses AS losses,
stratted.new_loan_original_principal AS new_loan_original_principal,
stratted.recovery_fees AS recovery_fees,
stratted.sum_debt_sale_fees_and_recovery_fees AS sum_debt_sale_fees_and_recovery_fees,
stratted.unscheduled_principal_received AS unscheduled_principal_received,
SUM(stratted.new_loan_original_principal) OVER (PARTITION BY stratted.vantage_score_band,stratted.vintage_band ORDER BY stratted.period_end_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_loan_size,
SUM(stratted.losses) OVER (PARTITION BY stratted.vantage_score_band,stratted.vintage_band ORDER BY stratted.period_end_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_losses,
SUM(stratted.unscheduled_principal_received) OVER (PARTITION BY stratted.vantage_score_band,stratted.vintage_band ORDER BY stratted.period_end_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_unscheduled_principal_received
FROM stratted
)
SELECT cumulative_metrics.period_end_date,
cumulative_metrics.vantage_score_band,
cumulative_metrics.vintage_band,
cumulative_metrics.charge_off,
cumulative_metrics."charge_off,_debt_sale_fees,_recovery_fees",
cumulative_metrics.debt_sale_fees,
cumulative_metrics.debt_sale_proceeds,
cumulative_metrics."debt_sale_proceeds,_gross_recoveries",
cumulative_metrics.gross_recoveries,
cumulative_metrics.losses,
cumulative_metrics.new_loan_original_principal,
cumulative_metrics.recovery_fees,
cumulative_metrics.sum_debt_sale_fees_and_recovery_fees,
cumulative_metrics.unscheduled_principal_received,
cumulative_metrics.cumulative_loan_size,
cumulative_metrics.cumulative_losses,
cumulative_metrics.cumulative_unscheduled_principal_received,
cumulative_metrics.cumulative_losses / NULLIF(cumulative_metrics.cumulative_loan_size,0) AS cumulative_losses__perc,
cumulative_metrics.cumulative_unscheduled_principal_received / NULLIF(cumulative_metrics.cumulative_loan_size,0) AS cumulative_unscheduled_principal_received__perc_
FROM cumulative_metrics;
