Reassigning bundle values to individual products SQL Oracle

Es-Dot 0
Viewed 19

My dataset tracks customer orders. Customers can buy a bundle, and are then charged one bundle_price:

enter image description here

Customer 1 bought bundle A, for a bundle_price of 200. The different products within the bundle are also listed with its original list_price. I want to reassign this bundle_price to each individual product, so that I can exclude bundles from my dataset. I want to add two columns: List_Ratio and Ratio_Bundle_Price. The data should look like this:

enter image description here

I am thinking to use a case statement with a window function, but not sure how to exactly execute this. Any help would be appreciated.

oracle sql
1 Answers

You can use:

SELECT t.*,
       list_price / SUM(list_price) OVER (PARTITION BY customer)
         AS list_ratio,
       ROUND(
         list_price
         * SUM(discounted_price) OVER (PARTITION BY customer)
         / SUM(list_price) OVER (PARTITION BY customer),
         2
       ) AS ratio_bundle_price
FROM   table_name t

Which, for the sample data:

CREATE TABLE table_name (customer, product_number, bundle_product, list_price, discounted_price) AS
  SELECT 1, 'A', 'Y',   0, 200 FROM DUAL UNION ALL
  SELECT 1, 'B', 'N', 180,   0 FROM DUAL UNION ALL
  SELECT 1, 'C', 'N',  20,   0 FROM DUAL UNION ALL
  SELECT 1, 'D', 'N',  70,   0 FROM DUAL;

Outputs:

CUSTOMER PRODUCT_NUMBER BUNDLE_PRODUCT LIST_PRICE DISCOUNTED_PRICE LIST_RATIO RATIO_BUNDLE_PRICE
1 A Y 0 200 0 0
1 D N 70 0 .2592592592592592592592592592592592592593 51.85
1 C N 20 0 .0740740740740740740740740740740740740741 14.81
1 B N 180 0 .6666666666666666666666666666666666666667 133.33

fiddle

MT00
Related