CODEWITHSUNDEEP
sqlgoogle-bigquery
Sanji
Sanji

Reputation: 3

How to get the Daily budget Consumed but not exceeding the total budget in SQL/BigQuery

Here I have a Daily budget and Total budget. The daily budget is consumed which is a part of Total Budget

Cost per Click(cpc) $1
total budget $1,000
Daily Cap $150

below is the desired table

Select  'pubg'  app_pkg,    100 as Click,   1 as CPC,   150 as Daily_budget,    1000 as Total_budget union All
Select  'pubg'  ,   125 ,   1,  150,    1000,   union All
Select  'pubg'  ,   150 ,   1,  150,    1000,   union All
Select  'pubg'  ,   175 ,   1,  150,    1000,   union All
Select  'pubg'  ,   200 ,   1,  150,    1000,   union All
Select  'pubg'  ,   225 ,   1,  150,    1000,   union All
Select  'pubg'  ,   250 ,   1,  150,    1000,   union All
Select  'pubg'  ,   275 ,   1,  150,    1000,   union All
Select  'pubg'  ,   300 ,   1,  150,    1000,   union All
Select  'pubg'  ,   325 ,   1,  150,    1000

and I want the result in this format

app_pkg Clicks  Daily_rev
pubg    100     $100
pubg    125     $125
pubg    150     $150
pubg    175     $150
pubg    200     $150
pubg    225     $150
pubg    250     $150
pubg    275     $25
pubg    300     $0
pubg    325     $0

The Daily_rev is not exceeding the Daily budget and The Sum(daily_rev) is equal to Total_budget.

Upvotes: 0

Views: 72

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Below is for BigQuery Standard SQL

#standardSQL
SELECT app_pkg, Click,
  GREATEST(0,
    LEAST(
      total_budget - IFNULL(SUM(LEAST(click * cpc, daily_budget)) OVER(PARTITION BY app_pkg ORDER BY click ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0),
      LEAST(click * cpc, daily_budget)
    )
  ) AS daily_rev
FROM `project.dataset.table`
-- ORDER BY click

when applied to sample data from your question - output is

Row app_pkg Click   daily_rev    
1   pubg    100     100  
2   pubg    125     125  
3   pubg    150     150  
4   pubg    175     150  
5   pubg    200     150  
6   pubg    225     150  
7   pubg    250     150  
8   pubg    275     25   
9   pubg    300     0    
10  pubg    325     0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You want a cumulative sum. I am guessing that you want this from the smallest clicks to the largest. Here is the logic:

select t.*,
       greatest(least(total_budget - sum(least(click * cpc, daily_budget)) over (partition by app_pkg order by click),
                      least(click * cpc, daily_budget)
                     ), 0
               ) as daily_rev
from t
order by click;

Some explanation. The expression:

least(click * cpc, daily_budget)

calculates the revenue on the day subject to the daily limit.

Then, the daily_rev is a bit more complicated. The cumulative sum of the daily revenue is used. First, if the cumulative sum easily fits in the total, then the daily limit value is used. Otherwise the difference between the total and the cumulative value.

For some rows, the difference may be negative. Those are capped at 0 using greatest().

Upvotes: 1

Related Questions