sql – How to calculate the discount?

Question:

Hello everyone. There are 3 tables – Discounts, Clients and Orders

Discount

id_discount count_orders percent_discount
     1           5             1,5
     2           10            2,5

Clients

id_client  Name Surname
     1     Ivan  Petrov
     2     Vasya Vasev

Orders

id   order_sum id_client

The question is, how to calculate the total amount, given the number of orders that the client has made? If the total number of orders is 5 or more, then there is a 1.5% discount, if 10 or more, then 2.5%. Otherwise, there is no discount. Thanks in advance

Answer:

Firebird is not at hand, so I write a query that is as close to standards as possible, it will work with a high probability. You did not indicate to which specific amount you want to apply the discount, so you thought that you should issue all orders, calculating the amount with a discount for each of them, with the assumption that at the moment the amount in orders is without a discount.

select O.id,O.id_client,
       O.order_sum-(O.order_sum/100*coalesce(D.percent_discount,0))
  from Orders O
  left join
       (select id_client,max(D.count_orders) as d_cnt
          from (select id_client,count(1) as cnt from Orders
                 group by id_client) as C,
               Discount as D
         where D.count_orders<=cnt
         group by id_client) as S
    on S.id_client=O.id_client
  left join Discount as D
    on D.count_orders=S.d_cnt
  1. Subquery C selects the current order quantities by customer
  2. All discounts with a smaller or equal amount are glued to them.
  3. Subquery S. The quantity for the discount is taken for the most suitable number of orders
  4. On the left (since there may not be any discounts), discounts are added, taking into account the required amount

Glue the table clients as desired.

Scroll to Top