~楓花雪岳~: [Challenge] 計算每項產品最低價的折扣組合

星期三, 5月 08, 2013

[Challenge] 計算每項產品最低價的折扣組合

Beyond Relational TSQL Challenge 11

The context of this challenge is about dealing with combinations and how to extract valuable data. For this challenge we will work with two data tables.

The products with their prices:

ID NAME    PRICE
-- ------- ---------
1  PROD 1  100,00
2  PROD 2  220,00
3  PROD 3  15,00
4  PROD 4  70,00
5  PROD 5  150,00

And the discount coupons:

ID NAME         VALUE  IS_PERCENT
-- -----------  ------ ----------
1  CP 1 : -15$  15     0
2  CP 2 : -5$   5      0
3  CP 3 : -10%  10     1
4  CP 4 : -12$  12     0

On the IS_PERCENT column, you should understand, if 0 value is considered as real money value, if 1 then it is a percent value of the price.

The Challenge

For this shopping application, customers could use one to two coupons for the same product but the discount price can not be less than 70% of the original price and the total amount of the discount can not exceed 30$.

It is important to note that coupons are applied in a cumulative way. The second coupon is applied on the result of the original price + first coupon.

With these conditions, the boss ask for a report that shows for each product the minimum price that should be paid for a product using any combination of the discount coupons. For this report you need also to show:

Original price (PRICE)
Discount price (DISC_PRICE)
Total amount of the discount (TOT_DISC)
Total rate of the discount (RATE)
An info field with names of coupons applied (COUPON_NAMES)

Regarding the original data tables, here is the final report you should produce:

ID NAME    PRICE    DISC_PRICE  TOT_DISC  RATE    COUPON_NAMES
-- ------  -------- ----------- --------- ------- -------------------------
1  PROD 1  100.00$  73.00$      27.00$    27.00%  CP 4 : -12$ + CP 1 : -15$
2  PROD 2  220.00$  193.00$     27.00$    12.27%  CP 4 : -12$ + CP 1 : -15$
3  PROD 3  15.00$   13.50$      1.50$     10.00%  CP 3 : -10%
4  PROD 4  70.00$   49.50$      20.50$    29.28%  CP 1 : -15$ + CP 3 : -10%
5  PROD 5  150.00$  120.00$     30.00$    20.00%  CP 3 : -10% + CP 1 : -15$

個人解法

;
WITH CTE AS
(
  SELECT 
    ID , 
    [Name] , 
    [Value] , 
    IS_PERCENT , 
    1 AS Counts ,
    CAST([Name] AS varchar(100)) AS combination ,
    ID AS C1 ,
    NULL AS C2
  FROM @C
  UNION ALL
  SELECT 
    C.ID , 
    C.[Name] , 
    C.[Value] , 
    C.IS_PERCENT , 
    Counts + 1 ,
    CAST(T.combination + ' + ' + C.[Name] AS varchar(100)) ,
    C1 ,
    C.ID 
  FROM CTE AS T
    JOIN @C AS C ON T.[Name] <> C.[Name]
  WHERE Counts < 2
) 
SELECT 
  Z.ID , 
  Z.NAME , 
  CAST(Z.PRICE AS varchar(10)) + '$' AS Price , 
  CAST(Z.DISC_PRICE AS varchar(10)) + '$' AS DISC_PRICE , 
  CAST(Z.TOT_DISC AS varchar(10)) + '$' AS TOT_DIC , 
  CAST(Z.RATE * 100 AS varchar(10)) + '%' AS RATE , 
  Z.COUPON_NAMES
FROM
  (
    SELECT 
      X.* , 
      RANK() OVER (PARTITION BY X.ID ORDER BY TOT_Disc DESC) AS ROWNO
    FROM
      (
        SELECT 
          R.* , 
          R.Price - R.DISC_PRICE AS TOT_DISC , 
          (R.Price - R.DISC_PRICE) / R.Price AS RATE
        FROM
          (
            SELECT 
              F.ID , 
              F.Name , 
              F.Price , 
              F.Counts , 
              F.combination AS COUPON_NAMES, 
              CASE
                WHEN F.IS_PERCENT = 0 THEN F.Price1 - F.Value
                WHEN F.IS_PERCENT = 1 THEN F.Price1 - (F.Price1 * F.Value / 100)
                WHEN F.IS_PERCENT IS NULL THEN F.Price1
              END AS DISC_PRICE
            FROM
              (
                SELECT T.* , 
                  CASE 
                    WHEN C1.IS_PERCENT = 0 THEN T.Price - C1.Value
                    WHEN C1.IS_PERCENT = 1 THEN T.Price - (T.Price * C1.Value / 100)
                  END AS Price1 ,
                  C2.[Value] , C2.IS_PERCENT
                FROM
                  (
                    SELECT
                      T1.ID , 
                      T1.Name , 
                      T1.Price ,
                      T2.Counts , 
                      T2.combination , 
                      T2.C1 , 
                      T2.C2 
                    FROM @T AS T1 
                      CROSS JOIN CTE AS T2
                  ) AS T
                  LEFT JOIN @C AS C1 ON T.C1 = C1.ID
                  LEFT JOIN @C AS C2 ON T.C2 = C2.ID
              ) AS F
          ) AS R
      ) AS X
      WHERE X.TOT_DISC <= 30 
        AND X.RATE < 0.3
  ) AS Z
WHERE Z.ROWNO = 1
ORDER BY ID

利用 CTE 把 coupon 的組合跑出來後，跟產品進行 CROSS JOIN 就可以找出每項的 coupon 組合，因為折扣是可以累積，也就是說遇上第三種 coupon 的話，先扣或後扣會產生不一樣結果，這是要注意的地方。

因為折扣是累加，發現萬一該產生最低折扣組合兩者皆為直接扣金額的話（第一、二和四 coupon），產生的結果應該會變成兩個，如產品一，不論是先使用 coupon 1 或 coupon 4 得到的結果都是一樣；產品二則是有三種折扣組合會得到最低價格，這是我發現跟題目結果不一樣的地方。