Closest Median Profit Between Traders

Coding · Medium · Free problem

You are evaluating traders at your firm during a performance review. You want to find the two traders whose median profit per trade is closest to each other.

Write a SQL query that returns the pair of traders with the smallest absolute difference in median profit per trade. Only include traders who made at least one trade. Output columns: trader1_name, trader2_name, median_profit_diff.

Tables:

traders

| Column | Type | |--------|---------| | id | INTEGER | | name | VARCHAR |

trades

| Column | Type | |-----------|---------| | id | INTEGER | | trader_id | INTEGER | | profit | FLOAT |

Example output:

| trader1_name | trader2_name | median_profit_diff | |--------------|--------------|--------------------| | James | Kendrick | 17 |

Hints

  1. Start by computing the median profit for each trader using a window or aggregate function. PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY profit) handles this in PostgreSQL.
  2. To compare all pairs of traders, self-join the results. Use the condition a.id < b.id to avoid counting each pair twice and to exclude self-comparisons.
  3. Order by ABS(a.median_profit - b.median_profit) ASC and use LIMIT 1 to get the closest pair.

Worked Solution

How to Think About It: This is a three-step SQL problem: (1) compute the median profit per trader, (2) generate all pairs of traders, (3) find the pair with the smallest difference. The main subtlety is computing the median in SQL, which is not a built-in aggregate in many dialects. We use PERCENTILE_CONT(0.5) which is available in PostgreSQL and most modern SQL engines.

Algorithm:

  1. CTE -- median per trader: Join traders to trades, group by trader, and compute the median using PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY profit). The INNER JOIN naturally filters to traders with at least one trade.
  1. Cross join for pairs: Self-join the CTE on itself with the condition t1.id < t2.id to avoid duplicate pairs and self-pairs.
  1. Find minimum difference: Compute ABS(t1.median_profit - t2.median_profit), order ascending, and take the top row.

Code:

```sql WITH median_profit_per_trader AS ( SELECT t.id, t.name, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tr.profit) AS median_profit FROM traders t INNER JOIN trades tr ON t.id = tr.trader_id GROUP BY t.id, t.name )

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 CROSS JOIN median_profit_per_trader b WHERE a.id < b.id ORDER BY median_profit_diff ASC, a.name ASC, b.name ASC LIMIT 1; ```

Complexity: If there are $k$ traders with trades, the CTE runs in $O(k \cdot m \log m)$ where $m$ is the average number of trades per trader (for the sort inside PERCENTILE_CONT). The cross join produces $O(k^2)$ pairs. For most firm sizes this is fine.

Answer: Use a CTE to compute median profit per trader via PERCENTILE_CONT, then cross join to find all pairs, and select the pair with the smallest absolute difference in median profit.

Intuition

This problem tests three core SQL skills: aggregation with a non-trivial statistic (median), self-joins for pairwise comparison, and correct deduplication of symmetric pairs. The a.id < b.id condition is the key detail -- without it, you get each pair twice (A,B and B,A) plus self-pairs (A,A). In practice, computing medians in SQL varies by dialect: PostgreSQL uses PERCENTILE_CONT, MySQL requires a subquery-based approach, and some engines support MEDIAN() directly. Knowing which functions your target engine supports is a practical skill that comes up in data engineering interviews.

The cross join approach is $O(k^2)$ in the number of traders, which is fine for a firm with hundreds of traders. For millions of entities, you would want a sorted merge approach, but at firm scale the brute-force pairwise comparison is the clean solution.

Related problems

Open the full interactive solver →