SQL: Get date when cumulative sum reaches a mark

Question

I have a table in the following format:

   APP_iD|  Date       |   Impressions
    113  2015-01-01     10
    113  2015-01-02     5
    113  2015-01-03     50
    113  2015-01-04     35
    113  2015-01-05     30
    113  2015-01-06     75

Now, I need to know the date when cumulative SUM of those impressions crossed 65/100/150 and so on. I tried using CASE WHEN statement:

CASE WHEN SUM(impressions) >100
     THEN date

but it doesn't sum the data across the column. It just does checks against the individual row.

My final result should look like:

APP_iD  | Date_65   | Date_100   | Date_150
113       2015-01-03  2015-01-04   2015-01-06

Does anyone know how to do this? Is this even possible?

Answer 1

Use sum() over() to get the running sum and check for the required values with a case expression. Finally aggregate the results to get one row per each app_id.

select app_id,max(dt_65),max(dt_100),max(dt_150) 
from (
select app_id
,case when sum(impressions) over(partition by app_id order by dt) between 65 and 99 then dt end dt_65
,case when sum(impressions) over(partition by app_id order by dt) between 100 and 149 then dt end dt_100
,case when sum(impressions) over(partition by app_id order by dt) >= 150 then dt end dt_150
from t) x
group by app_id