您将如何实现这个查询？记录集或 Sql

Question

我刚刚在几天前的面试测试中看到这个问题，我有下表，

Region Company Sales ratio_of_sales_in_region  percentile_in_region
 NA     A1      1000            0.25                25
 NA     A2      1000             0.25                50
 NA     A3      1000             0.25                75
 NA     A4      1000             0.25                100 
 EU     B1      2000            0.5                  50
 EU     B2      1000             0.25                75
 EU     B3      1000             0.25                100
.......

我需要提取每个地区第 30 个百分位的公司和销售额 结果将是

Region  30th_percentile_company  30th_percentile_sales
NA           A2                       (1000*0.25 + 500 * 0.05)
EU           B1                        2000    (as B1 accounts for more than 30%)

查询需要检查上述条件，例如一家公司是否已占 30% 以上，并计算每个地区 30% 销售额的权重。

编辑：我试图通过添加新列来解释百分位数的含义。我很困惑，但我看到了所询问的结果表，它清楚地说明了 30% 的含义

Answer 1

SELECT
   Region,
   MIN(Company) as [30th_percentile_company],  --potentially, two companies would from the same region would have the exact same percentile_in_region.
FROM
(
SELECT
   Region,
   MIN(percentile_in_region) as percentile_in_region
WHERE
   percentile_in_region > 30
GROUP BY
   Region
) a

INNER JOIN
   TableName T1
ON
   T1.Region = a.Region
   AND T1.percentile_in_region = a.precentile_in_region

GROUP BY
   Region