As I started some weeks ago playing with the excellent Sql Challenges of Jacob Sebastian (challenge #1, challenge #2 & challenge #3) , I thought it was really good exercise. It is a good way to find elegant solutions to common problems and a really interesting and funny way to learn sql.
That’s why I start today my own series of Sql Challenges. The target is to have fun with Sql (yes you can ;-) and to focus on certain key features of the language that most developers don’t use.
So here is the first challenge.
The Context
You have a table with products and prices:
ID NAME PRICE
----------- -------------------------------- ---------------------
1 PRODUCT 1 100,00
2 PRODUCT 2 220,00
3 PRODUCT 3 70,00
And a table with discount coupons:
ID NAME VALUE IS_PERCENT
----------- -------------------------------- ----------- ----------
1 COUPON 1 : -15$ 15 0
2 COUPON 2 : -5$ 5 0
3 COUPON 3 : -10% 10 1
4 COUPON 4 : -12$ 12 0
One to two coupons should be used with each product but the discount price can not be less than 60% of the original price
The Challenge
The deal is to find for each product the minimum price that should be paid for a product using any combination of coupons with the previous restrictions. You have also to label the coupons applied.
This is the target table you have to produce:
ID NAME PRICE DISCOUNT_PRICE COUPON_NAMES
----------- -------------------- --------------------- --------------------- ----------------------------------------
1 PRODUCT 1 100,00 73,00 COUPON 1 : -15$ + COUPON 4 : -12$
2 PRODUCT 2 220,00 183,00 COUPON 3 : -10% + COUPON 1 : -15$
3 PRODUCT 3 70,00 43,00 COUPON 1 : -15$ + COUPON 4 : -12$
Notes:
- This have to be done in only ONE sql query
- This query should target any version of Sql Server (2000,2005 or 2008)
- You have to use the exact sample data provided under without changing the parameters names (otherwise I can’t test it easily)
- Last date to submit your entries: 3 April 2009.
- Send me your entries to [challenge at rui dot fr] with the prefix [CHALLENGE]
I will publish a solution with the key points some days after the last submit date.
Sample data to use:
DECLARE @T TABLE (ID INT IDENTITY, NAME NVARCHAR(20),PRICE MONEY)
INSERT INTO @T (NAME,PRICE) VALUES ('PRODUCT 1',100)
INSERT INTO @T (NAME,PRICE) VALUES ('PRODUCT 2',220)
INSERT INTO @T (NAME,PRICE) VALUES ('PRODUCT 3',70)
DECLARE @C TABLE (ID INT IDENTITY, NAME NVARCHAR(20), VALUE INT, IS_PERCENT BIT)
INSERT INTO @C (NAME,VALUE,IS_PERCENT) VALUES ('COUPON 1 : -15$',15,0)
INSERT INTO @C (NAME,VALUE,IS_PERCENT) VALUES ('COUPON 2 : -5$',5,0)
INSERT INTO @C (NAME,VALUE,IS_PERCENT) VALUES ('COUPON 3 : -10%',10,1)
INSERT INTO @C (NAME,VALUE,IS_PERCENT) VALUES ('COUPON 4 : -12$',12,0)
Have fun ;-)