我有一个sql视图，它显示为零售公司生成的客户id、月份和收入（美元），如下所示。

+-----------+-----------+-------------------+
| CLIENT_ID |   MONTH   | REVENUE_GENERATED |
+-----------+-----------+-------------------+
|    29837  | Jan 2020  |            123362 |
|    12412  | Jan 2020  |              2973 |
|    12412  | Jan 2020  |              1824 |
|    54398  | Mar 2020  |                12 |
|    81724  | Mar 2020  |               436 |
+-----------+-----------+-------------------+

我想看看实施奖励计划的成本（就收入损失多少而言），该计划根据以下层级和折扣提供折扣（与产生的收入成比例）：

+--------------+----------+
| REVENUE_TIER | DISCOUNT |
+--------------+----------+
| 0-250        | 0%       |
| 250-1000     | 1%       |
| 1000-2500    | 2%       |
| 2500-5000    | 4%       |
| 5000-10,000  | 6%       |
| >10,000      | 8%       |
+--------------+----------+

使用这些信息评估成本的最佳方法是什么？我想确定每个月每桶收入的损失额。
我假设首先为每一层创建bucket，每月统计每个bucket中的客户，然后根据每月每个bucket中的客户计数计算成本？i、 e.生成此

+----------+-------+----------+-----------+-----------+-------------+---------+
|  MONTH   | 0-250 | 250-1000 | 1000-2500 | 2500-5000 | 5000-10,000 | >10,000 |
+----------+-------+----------+-----------+-----------+-------------+---------+
| Jan 2020 |    99 |       28 |        47 |         5 |          65 |       0 |
| Feb 2020 |   128 |      188 |        17 |        84 |          98 |      47 |
| Mar 2020 |   122 |       67 |        12 |         1 |           1 |       1 |
+----------+-------+----------+-----------+-----------+-------------+---------+

然后将计数乘以收入层得到成本。
如果需要进一步澄清，我会在这里支持你。
生成计数表的sql代码：

SELECT
   MONTH,
   COUNT(CASE WHEN REVENUE_GENERATED <= 250 THEN 1 END) AS "0-250",
   COUNT(CASE WHEN REVENUE_GENERATED > 250 AND REVENUE_GENERATED <= 1000 THEN 1 END) AS "250-1000",
   COUNT(CASE WHEN RREVENUE_GENERATED > 1000 AND REVENUE_GENERATED <= 2500 THEN 1 END) AS "1000-2500",
   COUNT(CASE WHEN REVENUE_GENERATED > 2500 AND REVENUE_GENERATED <= 5000 THEN 1 END) AS "2500-5000",
   COUNT(CASE WHEN REVENUE_GENERATED > 5000 AND REVENUE_GENERATED <= 10000 THEN 1 END) AS "5000-10000",
   COUNT(CASE WHEN REVENUE_GENERATED > 10000 THEN 1 END) AS "+10000"
FROM
   REVENUE
GROUP BY
   MONTH;