Calculating progressive pricing in PostgreSQL

Question

I need to calculate revenue based on how many items a user has. So for example, the first 10 items are free, up to 100 items are 0.50, up to 200 are 0.25 and up to 500 are 0.15 for example.

I have no idea where to start with this, can I get some direction please?

EG. If a user has 365 items, this would be (10 * 0) + (90 * 0.5) + (100 * 0.25) + (165 * 0.15)

Ideally I'd be doing this in python or something, but the dashboarding tool doesn't have that capability...

EDIT: I should have mentioned that the number of items isn't actually the number they have, it's the limit they have chosen. The limit is saved as a single number in a subscription event. So for each user I will have an integer representing their max items eg. 365

Answer 1

First number items using window function row_number,
then use a case expression to assign a proper value for each item.
Simple example: http://sqlfiddle.com/#!17/32e4a/9

SELECT user_id,
 SUM(
    CASE
       WHEN rn <= 10 THEN 0
       WHEN rn <= 100 THEN 0.5
       WHEN rn <= 200 THEN 0.25
       WHEN rn <= 500 THEN 0.15
       ELSE 0.05
    END
   ) As revenue 
FROM (
  SELECT *,
    row_number() OVER (partition by user_id order by item_no ) As rn
  FROM mytable
) x
GROUP BY user_id

---

I should have mentioned that the number of items isn't actually the number they have, it's the limit they have chosen. The limit is saved as a single number in a subscription event. So for each user I will have an integer representing their max items eg. 365

In this case the below query probably fits your needs:
Demo: http://sqlfiddle.com/#!17/e7a6a/2

SELECT *,
      (SELECT SUM(
          CASE
             WHEN rn <= 10 THEN 0
             WHEN rn <= 100 THEN 0.5
             WHEN rn <= 200 THEN 0.25
             WHEN rn <= 500 THEN 0.15
             ELSE 0.05
           END
            ) 
          FROM generate_series(1,t.user_limit) rn
       )
FROM mytab t;