How to calculate tiered pricing using PostgreSQL

Question

I'm trying to calculate tiered rates for a stay at some lodging. Lets say we have a weekly, half week, and daily rate for a property.

period_name | nights | rate
-------------------------------------
WEEK        | 7      | 100
HALFWEEK    | 3      | 50
DAY         | 1      | 25

How would I query this with a total number of nights and get a break down of what periods qualify, going from longest to shortest? Some examples results

10 nights

We break 10 into (7 days) + (3 days). The 7 days will be at the WEEK rate (100). The 3 days will be at the HALFWEEK rate (50). Here it qualifies for (1 WEEK @ 100) + (1 HALFWEEK @ 50)

period_name | nights | rate | num | subtotal
----------------------------------------------
WEEK        | 7      | 100  | 1   | 100
HALFWEEK    | 3      | 50   | 1   | 50

4 nights

We break 4 into (3 days) + (1 day). The 3 days will be at the HALFWEEK rate (50). The 1 day will be at the DAY rate (25). Here it qualifies for (1 HALFWEEK @ 50) + (1 DAY @ 25)

period_name | nights | rate | num | subtotal
----------------------------------------------
HALFWEEK    | 3      | 50   | 1   | 50
DAY         | 1      | 25   | 1   | 25

16 nights

We break 16 into (14 days) + (2 days). The 14 days will be at the WEEK rate (multiplied by 2), (100 * 2). The 2 days will be at the DAY rate (2 x 25). Here it qualifies for (2 WEEK @ 100) + (2 DAY @ 25)

period_name | nights | rate | num | subtotal
----------------------------------------------
WEEK        | 7      | 100  | 2   | 200
DAY         | 1      | 25   | 2   | 50

I thought about using the lag window function, but now sure how I'd keep track of the days already applied by the previous period.

S-Man · Accepted Answer

You can do this with a CTE RECURSIVE query.

http://sqlfiddle.com/#!17/0ac709/1

Tier table (which can be dynamically expanded):

id  name       days  rate  
--  ---------  ----  ----  
1   WEEK       7     100   
2   DAYS       1     25    
3   HALF_WEEK  3     50    
4   MONTH      30    200

Days data:

Result:

num_id  num  days                                             total_price  
------  ---  -----------------------------------------------  -----------  
1       10   {"MONTH: 0","WEEK: 1","HALF_WEEK: 1","DAYS: 0"}  150          
2       31   {"MONTH: 1","WEEK: 0","HALF_WEEK: 0","DAYS: 1"}  225          
3       30   {"MONTH: 1","WEEK: 0","HALF_WEEK: 0","DAYS: 0"}  200          
4       19   {"MONTH: 0","WEEK: 2","HALF_WEEK: 1","DAYS: 2"}  300          
5       14   {"MONTH: 0","WEEK: 2","HALF_WEEK: 0","DAYS: 0"}  200          
6       108  {"MONTH: 3","WEEK: 2","HALF_WEEK: 1","DAYS: 1"}  875          
7       3    {"MONTH: 0","WEEK: 0","HALF_WEEK: 1","DAYS: 0"}  50           
8       5    {"MONTH: 0","WEEK: 0","HALF_WEEK: 1","DAYS: 2"}  100          
9       1    {"MONTH: 0","WEEK: 0","HALF_WEEK: 0","DAYS: 1"}  25           
10      2    {"MONTH: 0","WEEK: 0","HALF_WEEK: 0","DAYS: 2"}  50           
11      7    {"MONTH: 0","WEEK: 1","HALF_WEEK: 0","DAYS: 0"}  100

The idea:

First I took this query to calculate your result for one value (19):

SELECT 
    days / 7 as WEEKS,
    days % 7 / 3 as HALF_WEEKS,
    days % 7 % 3 / 1 as DAYS 
FROM
    (SELECT 19 as days) s

Here you can see the recursive structure for the module operation terminated by an integer division. Because a more generic version should be necessary I thought about a recursive version. With PostgreSQL WITH RECURSIVE clause this is possible

https://www.postgresql.org/docs/current/static/queries-with.html

So thats the final query

WITH RECURSIVE days_per_tier(row_no, name, days, rate, counts, mods, num_id, num) AS (
    SELECT 
        row_no, 
        name, 
        days, 
        rate,
        num.num / days, 
        num.num % days, 
        num.id, 
        num.num
    FROM (
        SELECT 
            *, 
            row_number() over (order by days DESC) as row_no             -- C
        FROM 
            testdata.tiers) tiers,                      -- A
            (SELECT id, num FROM testdata.numbers) num  -- B
    WHERE row_no = 1

    UNION

    SELECT 
        days_per_tier.row_no + 1,
        tiers.name, 
        tiers.days,
        tiers.rate,
        mods / tiers.days,                                      -- D
        mods % tiers.days,                                      -- E
        days_per_tier.num_id,
        days_per_tier.num                               
    FROM 
        days_per_tier,  
        (SELECT 
            *, 
            row_number() over (order by days DESC) as row_no 
            FROM testdata.tiers) tiers 
    WHERE days_per_tier.row_no + 1 = tiers.row_no
 ) 
SELECT
    num_id,
    num,
    array_agg(name || ': ' || counts ORDER BY days DESC) as days, 
    sum(total_rate_per_tier) as total_price                    -- G
FROM (
    SELECT 
        *,
        rate * counts as total_rate_per_tier                   -- F
    FROM days_per_tier) s
GROUP BY num_id, num 
ORDER BY num_Id

The WITH RECURSIVE contains the starting point of the recursion UNION the recursion part. The starting point simply gets the tiers (A) and numbers (B). To order the tiers due to their days I add a row count (C; only necessary if the corresponding ids are not in the right order as in my example. This could happen if you add another tier).

The recursion part takes the previous SELECT result (which is stored in days_per_tier) and calculates the next remainder and integer division (D, E). All other columns are only for holding the origin values (exception the increasing row counter which is responsible for the recursion itself).

After the recursion the counts and rates are multiplied (F) and then grouped by the origin number id which generated the total sum (G)

Edit: Added the rate function and the sqlfiddle link.

How to calculate tiered pricing using PostgreSQL

Answers (2)

Related Questions