Optimal solution for interview question

Question

Recently in a job interview, I was given the following problem.

Say I have the following table

widget_Name        |     widget_Costs      | In_Stock
---------------------------------------------------------
a                   |         15.00          |    1
b                   |         30.00          |    1
c                   |         20.00          |    1
d                   |         25.00          |    1

where widget_name is holds the name of the widget, widget_costs is the price of a widget, and in stock is a constant of 1.

Now for my business insurance I have a certain deductible. I am looking to find a sql statement that will tell me every widget and it's price exceeds the deductible. So if my dedudctible is $50.00 the above would just return

widget_Name        |     widget_Costs      | In_Stock
---------------------------------------------------------
a                   |         15.00          |    1
d                   |         25.00          |    1

Since widgets b and c where used to meet the deductible

The closest I could get is the following

SELECT 
    *
FROM (
     SELECT 
          widget_name, 
          widget_price
     FROM interview.tbl_widgets
     minus
     SELECT widget_name,widget_price
     FROM (
          SELECT 
               widget_name,
               widget_price, 
               50 - sum(widget_price) over (ORDER BY widget_price  ROWS between unbounded preceding and current row) as running_total
          FROM interview.tbl_widgets 
     ) 
     where  running_total >= 0
)
;

Which gives me

widget_Name        |     widget_Costs      | In_Stock
---------------------------------------------------------
c                   |         20.00          |    1
d                   |         25.00          |    1

because it uses a and b to meet the majority of the deductible

I was hoping someone might be able to show me the correct answer

EDIT: I understood the interview question to be asking this. Given a table of widgets and their prices and given a dollar amount, substract as many of the widgets you can up to the dollar amount and return those widgets and their prices that remain

Answer 1

I'll put an answer up, just in case it's easier than it looks, but if the idea is just to return any widget that costs more than the deductible then you'd do something like this:

Select
  Widget_Name, Widget_Cost, In_Stock
From
  Widgets
Where
  Widget_Cost > 50 -- SubSelect for variable deductibles?

For your sample data my query returns no rows.

Answer 2

I believe I understand your question, but I'm not 100%. Here is what I'm assuming you mean:

Your deductible is say, $50. To meet the deductible you have you "use" two items. (Is this always two? How high can it go? Can it be just one? What if they don't total exactly $50, there is a lot of missing information). You then want to return the widgets that aren't being used towards deductible. I have the following.

CREATE TABLE #test
(
    widget_name char(1),
    widget_cost money
    )

INSERT INTO #test (widget_name, widget_cost)
SELECT 'a', 15.00 UNION ALL
SELECT 'b', 30.00 UNION ALL
SELECT 'c', 20.00 UNION ALL
SELECT 'd', 25.00 


SELECT * FROM #test t1 
WHERE t1.widget_name NOT IN (
SELECT t1.widget_name FROM #test t1
CROSS JOIN #test t2
WHERE t1.widget_cost + t2.widget_cost = 50 AND t1.widget_name != t2.widget_name)

Which returns

widget_name widget_cost
----------- ---------------------
a           15.00
d           25.00