-1

Not sure what the error here is but the returned result won't give the running total. I keep getting the same numbers returned for both ad_rev and running_total_ad_rev. Maybe someone could point out what the issue is? Thank you!

SELECT
days,
sum(ad_revenue) as ad_rev,
sum(sum(ad_revenue)) over (partition by days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total_ad_rev

FROM(
SELECT
DATE_DIFF(activity_date,creation_date,DAY) AS days,
ad_revenue

FROM
 table1 INNER JOIN table2 
USING (id)
WHERE
creation_date >= *somedate* 
and
activity_date = *somedate*
GROUP BY 1,2
ORDER BY 1)
GROUP BY 1
ORDER BY 1
Nata
  • 49
  • 6
  • can you provide sample of the data? – AlienDeg Oct 14 '22 at 07:39
  • I am working with 2 tables, and the columns I am using from them are are follows: table1 (user_id, creation_date, activity_date, campaign_id, int_ad_revenue) and table2 (campaign_id, campaign_name). The second table there is only because I need to filter by the campaign_name yet it is irrelevant to the rolling sum calculation.. – Nata Oct 14 '22 at 10:16

1 Answers1

1

You can't need partition by days if you want have running sum. Also you need to calculate daily_revenue step earlier. Feels like this is what you trying to achieve.

SELECT
  days,
  daily_revenue,
  SUM(ad_revenue) OVER ( ORDER BY days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as running_total_ad_rev
FROM(
  SELECT
    DATE_DIFF(activity_date,creation_date,DAY) AS days,
    SUM(ad_revenue) AS daily_revenue
  FROM
    table1 
  INNER JOIN table2 
    USING (id)
  WHERE
    creation_date >= *somedate* 
    and
    activity_date = *somedate*
  GROUP BY 1
  ORDER BY 1)
ORDER BY 1
AlienDeg
  • 1,288
  • 1
  • 13
  • 23
  • I don't really need to return the daily_revenue, I just included there because I was getting the same number as the rolling sum :/ – Nata Oct 14 '22 at 10:12
  • my table one query is as follows: WITH table 1 AS ( SELECT DISTINCT user_id, creation_date, activity_date, campaign_id, SUM(int_ad_revenue) AS int_ad_revenue FROM table0 ) – Nata Oct 14 '22 at 10:19
  • 1
    you are totally right tho, no need to partition, all works as I need it, thanks a lot! – Nata Oct 14 '22 at 15:48