Optimal Interview Solution

Question

Recently, in an interview, I was asked the following problem.

Let's 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 contains the name of the widget, widget_costs is the price of the widget, and in stock is constant 1.

Now for my business insurance I have a certain deductible. I am looking to find a sql expression that every widget will tell me, and this price exceeds the deductible. So if my dedudctible is $ 50.00, then this will return

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

Since widgets b and c used to satisfy the franchise

Closest I can get 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
)
;

What gives me

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

since he uses a and b to satisfy most of the franchise

I was hoping someone could show me the correct answer

: , . , , , ,

Answer 1

Bin Packing, , SQL.

SO Bin Packing + SQL, Sum () , " * , () < 150" . , , NOT IN.

brianegge, , , ,

.. , , . NP-Hard, ANSI SQL. , , .

, . 5 4.

, , ( ).

, Bin Packing sql, , .

Answer 2

, , , , , - , , , - :

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

.

Answer 3

, , 100%. :

, , 50 . , "" . ( : , ? , 50 , ). , . .

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)

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