I have below table:
My dataset tracks customer orders. Customers can buy a bundle, and are then charged one bundle discounted_price, or can buy individual products and are charged a discounted_price per product.
Customer 1 bought bundle A, for a discounted_price of 200. The different products within the bundle are also listed with its original list_price. I want to calculate the discount of the whole bundle, i.e. (180 20 70)/200 = 0.74. Customer 2 did not buy a bundle. In this case I want to calculate the discount per individual product, i.e. 15/20 = 0.75
So the result should look like:
In SQL Oracle, I tried below query, but it is not giving me the accurate discount_% when a product is a bundle
select
customer,
product_number,
bundle,
list_price,
discounted_price,
case when sum(list_price) <> 0 then round(sum(discounted_price) / sum(list_price,2))*100
else round(discounted_price / (list_price,2)*100 end as discount_%
from customer_orders
group by customer
CodePudding user response：
Prices in the question tables are not all the same (???) I took the last ones. Here is the code and result:
WITH
orders AS
(
Select 1 "CUST_ID", 'A' "PROD_ID", 'Y' "BUNDLE", 0 "LIST_PRICE", 200 "DISCOUNTED_PRICE" From Dual Union All
Select 1 "CUST_ID", 'B' "PROD_ID", 'N' "BUNDLE", 180 "LIST_PRICE", 0 "DISCOUNTED_PRICE" From Dual Union All
Select 1 "CUST_ID", 'C' "PROD_ID", 'N' "BUNDLE", 20 "LIST_PRICE", 0 "DISCOUNTED_PRICE" From Dual Union All
Select 1 "CUST_ID", 'D' "PROD_ID", 'N' "BUNDLE", 70 "LIST_PRICE", 0 "DISCOUNTED_PRICE" From Dual Union All
Select 2 "CUST_ID", 'C' "PROD_ID", 'N' "BUNDLE", 20 "LIST_PRICE", 15 "DISCOUNTED_PRICE" From Dual Union All
Select 2 "CUST_ID", 'B' "PROD_ID", 'N' "BUNDLE", 180 "LIST_PRICE", 110 "DISCOUNTED_PRICE" From Dual Union All
Select 2 "CUST_ID", 'E' "PROD_ID", 'N' "BUNDLE", 500 "LIST_PRICE", 400 "DISCOUNTED_PRICE" From Dual
),
bundles AS
( Select CUST_ID, DISCOUNTED_PRICE From orders Where BUNDLE ='Y' )
Select
o.CUST_ID "CUST_ID",
o.PROD_ID "PROD_ID",
o.BUNDLE "BUNDLE",
o.LIST_PRICE "LIST_PRICE",
o.DISCOUNTED_PRICE "DISCOUNTED_PRICE",
--
CASE
WHEN b.CUST_ID Is Not Null
THEN Round(b.DISCOUNTED_PRICE /
Sum(CASE WHEN o.BUNDLE = 'N' And o.LIST_PRICE > 0 And o.DISCOUNTED_PRICE = 0 THEN o.LIST_PRICE ELSE 0 END)
OVER(PARTITION BY o.CUST_ID ORDER BY o.CUST_ID), 2)
ELSE Round(CASE WHEN o.BUNDLE = 'N' And o.LIST_PRICE > 0 And o.DISCOUNTED_PRICE > 0 THEN o.DISCOUNTED_PRICE ELSE 0 END /
CASE WHEN o.BUNDLE = 'N' And o.LIST_PRICE > 0 And o.DISCOUNTED_PRICE > 0 THEN o.LIST_PRICE ELSE 0 END, 2)
END "DISCOUNT_RATIO"
From
orders o
Left Join
bundles b ON (b.CUST_ID = o.CUST_ID)
Order By
o.CUST_ID,
o.PROD_ID
--
-- R e s u l t
--
-- CUST_ID PROD_ID BUNDLE LIST_PRICE DISCOUNTED_PRICE DISCOUNT_RATIO
-- ---------- ------- ------ ---------- ---------------- --------------
-- 1 A Y 0 200 .74
-- 1 B N 180 0 .74
-- 1 C N 20 0 .74
-- 1 D N 70 0 .74
-- 2 B N 180 110 .61
-- 2 C N 20 15 .75
-- 2 E N 500 400 .8
Addition
Suppose that Customer 2 has both bundled and individual orders then it can be handled like below:
WITH
orders AS
(
Select 1 "CUST_ID", 'A' "PROD_ID", 'Y' "BUNDLE", 0 "LIST_PRICE", 200 "DISCOUNTED_PRICE" From Dual Union All
Select 1 "CUST_ID", 'B' "PROD_ID", 'N' "BUNDLE", 180 "LIST_PRICE", 0 "DISCOUNTED_PRICE" From Dual Union All
Select 1 "CUST_ID", 'C' "PROD_ID", 'N' "BUNDLE", 20 "LIST_PRICE", 0 "DISCOUNTED_PRICE" From Dual Union All
Select 1 "CUST_ID", 'D' "PROD_ID", 'N' "BUNDLE", 70 "LIST_PRICE", 0 "DISCOUNTED_PRICE" From Dual Union All
Select 2 "CUST_ID", 'C' "PROD_ID", 'N' "BUNDLE", 20 "LIST_PRICE", 15 "DISCOUNTED_PRICE" From Dual Union All
Select 2 "CUST_ID", 'B' "PROD_ID", 'N' "BUNDLE", 180 "LIST_PRICE", 110 "DISCOUNTED_PRICE" From Dual Union All
Select 2 "CUST_ID", 'E' "PROD_ID", 'N' "BUNDLE", 500 "LIST_PRICE", 400 "DISCOUNTED_PRICE" From Dual Union All
Select 2 "CUST_ID", 'A' "PROD_ID", 'Y' "BUNDLE", 0 "LIST_PRICE", 154 "DISCOUNTED_PRICE" From Dual Union All
Select 2 "CUST_ID", 'B' "PROD_ID", 'N' "BUNDLE", 180 "LIST_PRICE", 0 "DISCOUNTED_PRICE" From Dual Union All
Select 2 "CUST_ID", 'C' "PROD_ID", 'N' "BUNDLE", 20 "LIST_PRICE", 0 "DISCOUNTED_PRICE" From Dual
),
bundles AS
( Select CUST_ID, PROD_ID, DISCOUNTED_PRICE From orders Where BUNDLE ='Y' )
Select
o.CUST_ID "CUST_ID",
o.PROD_ID "PROD_ID",
o.BUNDLE "BUNDLE",
o.LIST_PRICE "LIST_PRICE",
o.DISCOUNTED_PRICE "DISCOUNTED_PRICE",
CASE WHEN b.CUST_ID Is Not Null and b.PROD_ID Is Not Null THEN 'Bundled' ELSE 'Individual' END "ORDER_TYPE",
--
CASE
WHEN b.CUST_ID Is Not Null and b.PROD_ID Is Not Null
THEN Round(b.DISCOUNTED_PRICE /
Sum(CASE WHEN o.BUNDLE = 'N' And o.LIST_PRICE > 0 And o.DISCOUNTED_PRICE = 0 THEN o.LIST_PRICE ELSE 0 END)
OVER(PARTITION BY o.CUST_ID ORDER BY o.CUST_ID), 2)
ELSE Round(CASE WHEN o.BUNDLE = 'N' And o.LIST_PRICE > 0 And o.DISCOUNTED_PRICE > 0 THEN o.DISCOUNTED_PRICE ELSE 0 END /
CASE WHEN o.BUNDLE = 'N' And o.LIST_PRICE > 0 And o.DISCOUNTED_PRICE > 0 THEN o.LIST_PRICE ELSE 0 END, 2)
END "DISCOUNT_RATIO"
From
orders o
Left Join
bundles b ON (b.CUST_ID = o.CUST_ID And (b.PROD_ID = o.PROD_ID OR o.DISCOUNTED_PRICE = 0))
Order By
o.CUST_ID,
CASE WHEN b.CUST_ID Is Not Null and b.PROD_ID Is Not Null THEN 'Bundled' ELSE 'Not Bundled' END,
o.PROD_ID
--
-- R e s u l t
--
-- CUST_ID PROD_ID BUNDLE LIST_PRICE DISCOUNTED_PRICE ORDER_TYPE DISCOUNT_RATIO
-- ---------- ------- ------ ---------- ---------------- ---------- --------------
-- 1 A Y 0 200 Bundled .74
-- 1 B N 180 0 Bundled .74
-- 1 C N 20 0 Bundled .74
-- 1 D N 70 0 Bundled .74
-- 2 A Y 0 154 Bundled .77
-- 2 B N 180 0 Bundled .77
-- 2 C N 20 0 Bundled .77
-- 2 B N 180 110 Individual .61
-- 2 C N 20 15 Individual .75
-- 2 E N 500 400 Individual .8
Notice that by adjusting the bundles CTE, Left Join's ON clause and CASE expressions' conditions you can anticipate and handle even more complicated structures. This works when Bundle Product ID is lets say X and PROD_IDs within bundle are 'Y' and 'Z' or whatever. Regards...
CodePudding user response：
Try the following:
select customer, product_number, bundle, list_price, discounted_price,
case
when (list_price = 0 or discounted_price = 0) then
round(max(discounted_price) over (partition by customer) / sum(list_price) over (partition by customer), 2) * 100
when (list_price > 0 and discounted_price > 0) then
round(discounted_price / list_price, 2) * 100
end as Discount_Percentage
from customer_orders;
To avoid division by zero in case that some customers have both a list_price and discounted_price equal to 0, you may use coalesce and nullif functions as the following:
select customer, product_number, bundle, list_price, discounted_price,
case
when (list_price = 0 and discounted_price > 0) or (discounted_price = 0 and list_price > 0) then
round(
coalesce(
max(discounted_price) over (partition by customer) / nullif(sum(list_price) over (partition by customer), 0)
, 0)
, 2) *100
when (list_price > 0 and discounted_price > 0) then
round(discounted_price / list_price, 2) * 100
when (list_price = 0 and discounted_price = 0) then 0
end as Discount_Percentage
from customer_orders;
See a demo from db<>fiddle.
If a customer can buy a bundle and another product out of the bundle, i.e. product A and product E, then try the following:
select customer, product_number, bundle, list_price, discounted_price,
case
when (list_price = 0 or discounted_price = 0) then
round(max(case when bundle='Y' then discounted_price else 0 end) over (partition by customer) /
sum(case when list_price > 0 and discounted_price = 0 then list_price else 0 end) over (partition by customer), 2) * 100
when (list_price > 0 and discounted_price > 0) then
round(discounted_price / list_price, 2) * 100
end as Discount_Percentage
from customer_orders
order by customer, product_number;