SQLPad Forum question: #70 Cumulative spend

#70 Cumulative spend

select date, customer_id,daily_spend,
SUM(daily_spend) OVER(partition by customer_id ORDER BY date
ROWS
BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW)cumulative_spend
from
(select date_format(payment_ts,'%Y-%m-%d')date,
customer_id, sum(amount)daily_spend
from payment
where customer_id in (1,2,3)
group by customer_id,date
order by customer_id,date) as inn;
I am getting same output as sample output but still it is showing wrong answer.

Answers

Comment

Hey Prasad,
Your query is almost 100% correct, the bug was dude to the first column needs to be a DATE type instead of STRING.
-- Corrected version:
select date, customer_id,daily_spend,
SUM(daily_spend) OVER(partition by customer_id ORDER BY date
ROWS
BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW)cumulative_spend
from
-- (select date_format(payment_ts,'%Y-%m-%d')date,
(select DATE(payment_ts)date,
customer_id, sum(amount)daily_spend
from payment
where customer_id in (1,2,3)
group by customer_id,date
order by customer_id,date) as inn;
Keep up the good work!
--Leon

Leon (949)

Feb. 18, 2022, 10:02 a.m.