最赚钱的策略

Most Profitable Strategy

专题: Data Wrangling / 数据处理
难度: L4
来源: OpenQuant

题目详情

一家量化对冲基金有一个由 35 名交易员组成的团队。这些交易员执行的交易策略属于四类之一：`momentum`、`trend`、`mean reversion` 和 `stat arb`。

根据下面给出的表，对每位在过去一周内至少执行过同一策略 5 次的交易员，找出其最赚钱的交易策略以及该策略带来的利润。若有多种策略利润相同，则返回该交易员最早部署的那种策略。输出交易员姓名、交易策略和总利润。结果按利润降序、姓名升序排序。

示例输出：

full_name	strategy	total_profit
Brian Hu	momentum	7500
John Doe	trend	3300
Michael Lewis	stat arb	3300
Samuel Wellington	momentum	1000

表：

`traders`

Column Name	Data Type
trader_id	bigint
first_name	text
last_name	text
email	text
age	int
joined_company_dt	datetime

`trade_results`

Column Name	Data Type
trade_id	bigint
trader_id	bigint
trade_execution_dt	datetime
profit	bigint
strategy	text

A quantitative hedge fund consists of a team of 35 traders. These traders execute trading strategies that fall into one of four categories: 'momentum', 'trend', 'mean reversion', and 'stat arb'.

Using the tables given below, for each trader that has made at least 5 trades of the same strategy in the past week, find their most profitable trading strategy and the profit they earned from that strategy. If more than one strategy is equally profitable, return the first trading strategy deployed. Output the trader's name, trading strategy and total profit. Sort the result by profit in descending order and name in ascending order

Example Output:

full_name	strategy	total_profit
Brian Hu	momentum	7500
John Doe	trend	3300
Michael Lewis	stat arb	3300
Samuel Wellington	momentum	1000

Tables:

`traders`

Column Name	Data Type
trader_id	bigint
first_name	text
last_name	text
email	text
age	int
joined_company_dt	datetime

`trade_results`

Column Name	Data Type
trade_id	bigint
trader_id	bigint
trade_execution_dt	datetime
profit	bigint
strategy	text

解析

为了解这道题，我们可以把过程拆成几个连续步骤。

第 1 步，创建一个临时表 `trader_profit_per_strategy`。这个表先筛选过去 7 天内执行的交易，然后按 (交易员, 策略) 分组，以计算该交易员在该策略上的总利润，以及该策略首次被该交易员使用的日期。最后，只保留交易员在该策略上至少执行过 5 次交易的记录。

此时，这张表大致如下：

trader_id	full_name	strategy	first_trade_date	total_profit
...	...	...	...	...

第 2 步，创建另一个临时表 `trader_profit_per_strategy_ranked`，用来去掉不需要的记录。题目要求找出每位交易员最赚钱的策略，并在利润并列时按最早交易的策略打破平局。在这一步里，我们使用窗口函数，按某个策略对该交易员的盈利能力对每个 (交易员, 策略) 组合进行排名。

最后，筛选利润排名和首次交易时间排名都为 1 的记录，就能得到每位交易员最赚钱的策略；若有并列，则取最早交易的那一个。

WITH trader_profit_per_strategy AS (
  SELECT 
    t.trader_id, 
    t.first_name || ' ' || t.last_name AS full_name,
    tr.strategy,
    MIN(tr.trade_execution_dt) AS first_trade_date,
    SUM(tr.profit) AS profit 
  FROM 
    traders t 
  INNER JOIN 
    trade_results tr 
    ON t.trader_id = tr.trader_id 
  WHERE 
    tr.trade_execution_dt >= CURRENT_DATE - INTERVAL '7' DAY
  GROUP BY 
    1, 2, 3
  HAVING
    COUNT(tr.trade_id) >= 5
), trader_profit_per_strategy_ranked AS (
  SELECT
    ttps.trader_id,
    tpps.full_name, 
    tpps.strategy, 
    tpps.profit,
    ROW_NUMBER() OVER (
       PARTITION BY tpps.trader_id 
       ORDER BY tpps.profit DESC, tpps.first_trade_date ASC
    ) AS rnk
  FROM 
    trader_profit_per_strategy tpps 
)

SELECT 
  tppsr.full_name,
  tppsr.strategy,
  tppsr.profit
FROM 
  trader_profit_per_strategy_ranked tppsr 
WHERE 
  tppsr.rnk = 1
ORDER BY 
  tppsr.profit DESC, tppsr.full_name ASC

Original Explanation

To solve this question we will break it up into sequential parts. In part 1, we create a temporary table called `trader_profit_per_strategy`. This table is generated by first filtering for trades that were executed in the past 7 days. Next, we group by (trader, strategy) pairs in order to calculate the total profit earned by the trader using the strategy along with the first date when this strategy was used by the trader. Finally, we restrict (trader, strategy) pairs to those in which the trader has executed the strategy at least 5 times.

At this point our table resembles the following structure:

trader_id	full_name	strategy	first_trade_date	total_profit
...	...	...	...	...

Part 2 involves creating another temporary table called `trader_profit_per_strategy_ranked` that removes unwanted records. The question asks us to get the most profitable strategy for the trader and to resolve ties by the strategy that was traded first. In this step, we accomplish this goal by using a window function to rank each (trader, strategy) pair by how profitable the strategy was for that specific trader.

Finally, we can compute our answer by finding where the ranking for the strategy's profit and the ranking for the strategy's first trading date is 1. This means we are selecting the most trader's most profitable strategy, and if there are ties

WITH trader_profit_per_strategy AS (
  SELECT 
    t.trader_id, 
    t.first_name || ' ' || t.last_name AS full_name,
    tr.strategy,
    MIN(tr.trade_execution_dt) AS first_trade_date,
    SUM(tr.profit) AS profit 
  FROM 
    traders t 
  INNER JOIN 
    trade_results tr 
    ON t.trader_id = tr.trader_id 
  WHERE 
    tr.trade_execution_dt >= CURRENT_DATE - INTERVAL '7' DAY
  GROUP BY 
    1, 2, 3
  HAVING
    COUNT(tr.trade_id) >= 5
), trader_profit_per_strategy_ranked AS (
  SELECT
    ttps.trader_id,
    tpps.full_name, 
    tpps.strategy, 
    tpps.profit,
    ROW_NUMBER() OVER (
       PARTITION BY tpps.trader_id 
       ORDER BY tpps.profit DESC, tpps.first_trade_date ASC
    ) AS rnk
  FROM 
    trader_profit_per_strategy tpps 
)

SELECT 
  tppsr.full_name,
  tppsr.strategy,
  tppsr.profit
FROM 
  trader_profit_per_strategy_ranked tppsr 
WHERE 
  tppsr.rnk = 1
ORDER BY 
  tppsr.profit DESC, tppsr.full_name ASC