META 最大买方与卖方

Top Buyer and Seller

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

题目详情

写一条 SQL 查询，找出过去 30 天里买入和卖出 META 股票次数最多的交易员。允许并列，并按总交易次数从高到低排序返回结果。

给定下列表 `transactions_table`

Column Name	Data Type
transaction_date	datetime
stock_symbol	text
buyer_id	bigint
seller_id	bigint
transaction_id	bigint
purchase_price	float

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

trader_id	transactions
12345	85

Write a SQL query to find the trader who bought and sold the most META stock in the past 30 days. Allow for ties and return the answer ordered by total transactions in descending order.

You are given the following table called `transactions_table`

Column Name	Data Type
transaction_date	datetime
stock_symbol	text
buyer_id	bigint
seller_id	bigint
transaction_id	bigint
purchase_price	float

Here is an example of how the output should look:

trader_id	transactions
12345	85

解析

先把过去 30 天内所有 `META` 交易中，买方和卖方分别拆成“交易员参与记录”，再按交易员汇总总参与次数，最后用排名函数取并列第一即可。

WITH trader_transactions AS (
    SELECT buyer_id AS trader_id
    FROM transactions_table
    WHERE stock_symbol = 'META'
      AND transaction_date >= CURRENT_TIMESTAMP - INTERVAL '30 days'

    UNION ALL

    SELECT seller_id AS trader_id
    FROM transactions_table
    WHERE stock_symbol = 'META'
      AND transaction_date >= CURRENT_TIMESTAMP - INTERVAL '30 days'
), trader_transaction_counts AS (
    SELECT
        trader_id,
        COUNT(*) AS transactions,
        DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
    FROM trader_transactions
    GROUP BY trader_id
)
SELECT
    trader_id,
    transactions
FROM trader_transaction_counts
WHERE rnk = 1
ORDER BY transactions DESC, trader_id;

这里使用 `UNION ALL` 是因为同一位交易员的买入和卖出都应该分别计入总交易次数；`DENSE_RANK()` 则可以正确保留并列第一。

Original Explanation

To solve this question we will first create a temporary result set called `trader_transactions` that will find all the purchases and sales of META stock over the past 30 days. This table will store the number of purchases each trader made as well as the number of sales they made.

Next, we will create a temporary result set called `trader_transaction_costs` that will get the total transactions (purchases and sales) made by each trader and will assign a ranking to each trader based on the number of total transactions made.

Finally, we get those traders that have a rank of 1.

WITH trader_transactions AS (
    SELECT
        buyer_id AS trader_id,
        COUNT(transaction_id) AS transactions
    FROM 
        transactions_table tt1 
    WHERE
        transaction_date >= CURRENT_DATE - INTERVAL '30' DAY
        AND stock_symbol = 'META'
    GROUP BY 
       1
    UNION ALL
    SELECT
        seller_id AS trader_id,
        COUNT(transaction_id) AS transactions 
    FROM 
        transactions_table tt2 
    WHERE
        transaction_date >= CURRENT_DATE - INTERVAL '30' DAY
        AND stock_symbol = 'META'
    GROUP BY 
       1
), trader_transaction_counts AS (
    SELECT
        tt.trader_id
        SUM(tt.transactions) AS transaction_count 
        RANK() OVER(PARTITION BY tt.trader_id ORDER BY SUM(tt.transactions) DESC) AS rnk
    FROM 
        trader_transactions tt 
    GROUP BY
        1
)

SELECT 
    ttc.trader_id,
    ttc.transactions
FROM 
    trader_transaction_counts ttc 
WHERE 
    rnk = 1