返回题库

最接近中位利润

Closest Median Profit

专题
Data Wrangling / 数据处理
难度
L2

题目详情

绩效评审周期开始了，你负责评估公司里的交易员。出于好奇，你想找出每笔交易利润中位数彼此最接近的两位交易员。请写一条 SQL 查询，返回这两位交易员以及他们的中位数利润差。只包含至少有一笔交易的交易员。

下面是一个期望输出示例：

trader1_name trader2_name median_profit_diff
James Kendrick 17

给定以下表：

`traders`

Column Type
id INTEGER
name VARCHAR

`trades`

Column Type
id INTEGER
trader_id INTEGER
profit FLOAT

The performance review cycle has begun and you are responsible for evaluating the traders at your firm. Out of curiosity, you want to find the two traders that had the closest median profit per trade with one another. Write a SQL query to return the two traders with the closest median profit along with the difference in median profit between the traders. Only include traders that had at least one trade.

Here's an example of how the output should look:

trader1_name trader2_name median_profit_diff
James Kendrick 17

You are given the following tables:

`traders`

Column Type
id INTEGER
name VARCHAR

`trades`

Column Type
id INTEGER
trader_id INTEGER
profit FLOAT
解析

先求出每位交易员每笔交易利润的中位数，再把所有交易员两两配对，计算中位数之差的绝对值，最后取差值最小的一对即可。

WITH median_profit_per_trader AS (
    SELECT
        tr.id,
        tr.name,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY td.profit) AS median_profit
    FROM traders tr
    JOIN trades td
      ON td.trader_id = tr.id
    GROUP BY tr.id, tr.name
), trader_pairs AS (
    SELECT
        a.name AS trader1_name,
        b.name AS trader2_name,
        ABS(a.median_profit - b.median_profit) AS median_profit_diff
    FROM median_profit_per_trader a
    JOIN median_profit_per_trader b
      ON a.id < b.id
)
SELECT
    trader1_name,
    trader2_name,
    median_profit_diff
FROM trader_pairs
ORDER BY median_profit_diff, trader1_name, trader2_name
LIMIT 1;

这个写法天然只包含至少有一笔交易的交易员，因为中位数是在 `trades` 表参与连接后计算出来的。

Original Explanation

To solve this question, we first compute the median profit for each trader at the firm. Next, we perform a cross join to get every trader pair in order to compare the median profit of the two traders. Finally, we get the pair that has the smallest difference in their median profits.

WITH median_profit_per_trader AS (
    SELECT 
        t1.id,
        t1.name,
        PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY t2.profit) AS "median_profit" 
    FROM 
        traders t1 
    INNER JOIN 
        trades t2 
        ON t1.id = t2.trader_id 
    GROUP BY 
        1, 2
)

SELECT 
    mppt1.name AS "trader1_name", 
    mppt2.name AS "trader2_name",
    ABS(mppt1.profit - mppt2.profit) AS "median_profit_diff"
FROM 
    median_profit_per_trader mppt1
CROSS JOIN 
    median_profit_per_trader mppt2 
WHERE 
    mppt1.id < mppt2.id 
ORDER BY 
    3, 1, 2
LIMIT 
    1