PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Max QTY with price <= 100 (Query)

Name Price Qty
A 3 30
B 5 3
C 5 3
D 6 20

i have a table contain data like this. i want to get what is the maximum of sum(qty) that i can provide Price <= 100

expected output : 32
coming from : 30 qty A and 2 qty from B or C

Tags:

2

Answers


  1. 0

    This can be solved by using a recursive common table expression to calculate a running total. I have used an initial cte to expand your data to one row per item (30 rows for item A, 3 rows for item B, etc) and have ordered these by cost. The recursive part of the query the loops through each of these in order until the total price reaches 100.

    create table t (
    name varchar not null,
    price integer not null,
    qty integer not null
    );

insert into t (name, price, qty) values('A', 3, 30), ('B', 5, 3), ('C', 5, 3), ('D', 6, 20);

with recursive expanded as (
    select name, price, row_number() over(order by price, name)
    from t
    cross join generate_series(1, qty)
    ),
purchased (name, remaining, next_row_number) as (
    select null::text, 100, 1::bigint
    union all
    select expanded.name, purchased.remaining - expanded.price, expanded.row_number + 1
    from purchased
    join expanded on expanded.row_number = purchased.next_row_number
    where expanded.price <= purchased.remaining
    )
select count(name) as qty
from purchased;
    Login or Signup to reply.
  2. 0

    First, get a rolling sum of quantity and price * quantity. Use a window ordered by price to ensure you spend your money on the best deals first. Since price is not unique, we can also order by the primary key (I’m using name) to ensure consistent results.

    Use lead to keep track of the next possible option, price, and available quantity.

      select
    name,
    price,
    qty,
    sum(qty) over w as "running purchased",
    sum(price * qty) over w as "running cost",
    lead(name) over w as "next option",
    lead(price) over w as "next price",
    lead(qty) over w as "next option"
  from items
  window w as (order by price, name asc rows unbounded preceding)

    At this point we have all the information we need to finish the calculation.

    1. Pick the first row closest to, but not over, your limit.
    2. 100 - "running cost" is the "remaining money"
    3. floor( "remaining money" / "next price" ) is the "next purchased"
    4. "next price" * "next purchased" is the "next cost"
    5. 100 - "running cost" - "next cost" is the "remaining money"

    It might be easier to do the final calculation outside of SQL using the results, or you can finish up in SQL…

    Use a CTE to pick the row closest to, but not over, your limit and calculate how many can be purchased with your remainder.

    Finally, use another CTE to sum this all up and calculate how much money is left over.

    with running_total as (
  select
    name,
    price,
    qty,
    sum(qty) over w as "running purchased",
    sum(price * qty) over w as "running cost",
    lead(name) over w as "next option",
    lead(price) over w as "next price",
    lead(qty) over w as "next option"
  from items
  window w as (order by price, name asc rows unbounded preceding)
),
final_purchase as (
  select 
    *, 
    floor( (100 - "running cost") / "next price" ) as "next purchased"
  from running_total
  where "running cost" <= 100
)
select
  *,
  "next purchased" * "next price" as "next cost",
  "running purchased" + "next purchased" as "total purchased",
  "running cost" + ("next purchased" * "next price") as "total spent",
  100 - ("running cost" + ("next purchased" * "next price")) as "remaining money"
from final_purchase

    This might have performance advantages over a recursive approach.

    Demonstration.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search