题解 | 分析客户逾期情况

with t1 as(
    select l.customer_id,l.overdue_days,c.pay_ability from
        loan_tb l
    join
        customer_tb c on l.customer_id = c.customer_id
),
    t2 as (
        select
            pay_ability,
            count(customer_id) as customer_count,
            count(case
                    when
                        overdue_days is not null
                            then 1
                        end) as overdue_customer
        from
            t1
        group by pay_ability
        order by pay_ability
        
    )
select 
    pay_ability, CONCAT(round(overdue_customer / customer_count * 100,1),'%') as overdue_ratio
from
    t2
order by
    overdue_ratio desc

Your code is solving the problem step by step by using two Common Table Expressions (CTEs), which break down the logic into manageable parts. Let's break it down in detail:

1. CTE t1: Join Loan and Customer Tables

with t1 as(
    select l.customer_id, l.overdue_days, c.pay_ability 
    from loan_tb l
    join customer_tb c on l.customer_id = c.customer_id
)

• Purpose: This CTE (t1) combines the two tables, loan_tb and customer_tb, based on the customer_id column.
• Columns Selected:l.customer_id: The customer ID from the loan_tb table.l.overdue_days: The number of days the customer is overdue on their loan from the loan_tb table.c.pay_ability: The repayment ability level of the customer from the customer_tb table.
• Join Operation: The JOIN clause joins the two tables on customer_id, ensuring that you have the overdue_days and pay_ability for each customer.
• Result: The t1 CTE will output a dataset with each customer's customer_id, overdue_days, and pay_ability.

2. CTE t2: Count Customers and Overdue Customers by Repayment Ability

t2 as (
    select
        pay_ability,
        count(customer_id) as customer_count,
        count(case
                when overdue_days is not null then 1
                end) as overdue_customer
    from
        t1
    group by pay_ability
    order by pay_ability
)

• Purpose: This CTE (t2) aggregates the results from t1 by repayment ability (pay_ability). It calculates two key things:customer_count: The total number of customers in each repayment ability group.overdue_customer: The number of customers in each repayment ability group who have overdue days (overdue_days is not null).
• Logic:count(customer_id) as customer_count: This counts the total number of customers for each pay_ability group.count(case when overdue_days is not null then 1 end) as overdue_customer: This counts the customers who have overdue days, i.e., customers who are overdue on their loan. overdue_days is not null ensures that only customers with overdue days are counted.
• Grouping: GROUP BY pay_ability ensures the count is done separately for each repayment ability group (A, B, C, etc.).
• Ordering: ORDER BY pay_ability orders the results by repayment ability level (this is not necessary for the final output but may help with debugging or clarity).

3. Final Select: Calculate Overdue Customer Percentage

select 
    pay_ability, 
    CONCAT(round(overdue_customer / customer_count * 100, 1),'%') as overdue_ratio
from
    t2
order by
    overdue_ratio desc

• Columns Selected:pay_ability: The repayment ability level of the customer.CONCAT(round(overdue_customer / customer_count * 100, 1),'%') as overdue_ratio: This calculates the percentage of overdue customers in each repayment ability group.overdue_customer / customer_count * 100: This calculates the ratio of overdue customers to the total number of customers in the group, multiplied by 100 to convert it to a percentage.round(..., 1): The round() function rounds the result to 1 decimal place.CONCAT(..., '%'): The CONCAT() function appends the % symbol to the calculated percentage.
• Result: This gives you the percentage of overdue customers for each repayment ability level, rounded to one decimal place, with the % sign appended.
• Ordering: ORDER BY overdue_ratio desc sorts the final results in descending order based on the overdue percentage, meaning repayment ability levels with higher overdue percentages will be shown first.