SQL报表Top值查询求助：生成贷款债务极值SQL语句

Hey there! Let's work through this SQL problem together. First, I'll make some reasonable assumptions about your Cohorts table structure since you didn't list all columns—we can tweak these if they don't match your actual data:

• Your table includes school identifiers like SCHOOL_ID and SCHOOL_NAME to distinguish each institution
• There's a COMPLETION_RATE column (stored as a decimal, e.g., 10% = 0.10) representing the degree completion rate
• The CUML_DEBT_Pxx columns represent cumulative debt percentiles for each school (P90 = 90th percentile of student debt at the school, etc.)

Scenario 1: Filter schools with completion rate ≤ 10% #

If you want to target schools where the completion rate is explicitly 10% or lower, here's how to get the top 10 highest and lowest debt schools:

Top 10 Highest Debt Schools #

We'll sort by the 90th percentile debt (CUML_DEBT_P90) since this represents the highest debt bracket at each school:

SELECT 
    SCHOOL_ID,
    SCHOOL_NAME,
    COMPLETION_RATE,
    CUML_DEBT_P90,
    CUML_DEBT_P75,
    CUML_DEBT_P25,
    CUML_DEBT_P10
FROM Cohorts
WHERE COMPLETION_RATE <= 0.10  -- Adjust to 10 if your completion rate is stored as an integer
ORDER BY CUML_DEBT_P90 DESC
LIMIT 10;  -- Use TOP 10 for SQL Server, FETCH FIRST 10 ROWS ONLY for Oracle

Top 10 Lowest Debt Schools #

We'll sort by the 10th percentile debt (CUML_DEBT_P10) to capture the lowest debt bracket:

SELECT 
    SCHOOL_ID,
    SCHOOL_NAME,
    COMPLETION_RATE,
    CUML_DEBT_P90,
    CUML_DEBT_P75,
    CUML_DEBT_P25,
    CUML_DEBT_P10
FROM Cohorts
WHERE COMPLETION_RATE <= 0.10
ORDER BY CUML_DEBT_P10 ASC
LIMIT 10;

Scenario 2: Filter schools in the 10th percentile of completion rates #

If you mean schools whose completion rate falls in the lowest 10% of all schools (not just ≤10%), use window functions to calculate percentile ranks first:

-- CTE to calculate completion rate percentiles for all schools
WITH SchoolCompletionMetrics AS (
    SELECT 
        SCHOOL_ID,
        SCHOOL_NAME,
        COMPLETION_RATE,
        CUML_DEBT_P90,
        CUML_DEBT_P75,
        CUML_DEBT_P25,
        CUML_DEBT_P10,
        PERCENT_RANK() OVER (ORDER BY COMPLETION_RATE) AS completion_percentile
    FROM Cohorts
)

-- Top 10 highest debt schools in the lowest 10% of completion rates
SELECT 
    SCHOOL_ID,
    SCHOOL_NAME,
    COMPLETION_RATE,
    CUML_DEBT_P90,
    CUML_DEBT_P75,
    CUML_DEBT_P25,
    CUML_DEBT_P10
FROM SchoolCompletionMetrics
WHERE completion_percentile <= 0.10
ORDER BY CUML_DEBT_P90 DESC
LIMIT 10;

-- Top 10 lowest debt schools in the lowest 10% of completion rates
SELECT 
    SCHOOL_ID,
    SCHOOL_NAME,
    COMPLETION_RATE,
    CUML_DEBT_P90,
    CUML_DEBT_P75,
    CUML_DEBT_P25,
    CUML_DEBT_P10
FROM SchoolCompletionMetrics
WHERE completion_percentile <= 0.10
ORDER BY CUML_DEBT_P10 ASC
LIMIT 10;

Key Notes to Adjust for Your Data #

• Completion Rate Format: If your COMPLETION_RATE is stored as an integer (e.g., 10 = 10%), change the WHERE clause to COMPLETION_RATE <= 10.
• Debt Metric: Swap out CUML_DEBT_P90/CUML_DEBT_P10 for other columns if you want to use a different debt measure (e.g., median debt if you have a CUML_DEBT_P50 column).
• SQL Dialect Differences: Replace LIMIT 10 with the syntax your database uses:
  • SQL Server: SELECT TOP 10 ...
  • Oracle: ... FETCH FIRST 10 ROWS ONLY

内容的提问来源于stack exchange，提问作者Jay