CODEWITHSUNDEEP
sqlpostgresql
xaroulis gekas
xaroulis gekas

Reputation: 165

Query with cumulative total revenue in Sql

My data look like this:

Platform,Userid,In-app Product,Country,Currency,Timestamp,Transaction Receipt,Price
IOS,5.669802981,offer4,US,USD,28/03/2017 02:30,240200367307.75848,1.99
ANDROID,6.831350998,offer4,US,USD,27/03/2017 23:23,436138823984.4136,3.99
ANDROID,6.831233793,pack2.cash,CA,USD,27/03/2017 18:32,192318559985.1093,4.112

I want to find the cumulative sum of price per day.

Any idea on how to create a query that gives cumulative revenue per day?

Maybe i need to use a window function? I tried something like this:

select date_trunc('day',"Timestamp") as day,"Price",sum("Price") as cumul OVER (order by day) FROM cleandataset ORDER BY day

But it doesnt work For example i want the results to be something like this

day price cumulativetotalrevenue
1/1/2016 10 10
2/1/2016 5  15
3/1/2016 20 45

Upvotes: 0

Views: 479

Answers (2)

achilleb
achilleb

Reputation: 168

There is two ways to calculate the cumulative revenue here

  1. To output the cumulative revenue for each day for each transactions, your initial query was almost right but you misplaced the 'as cumul'. Try :
SELECT DATE("Timestamp") AS day, "Price", SUM("Price") OVER (ORDER BY DATE("Timestamp")) AS cumulative
FROM cleandataset
ORDER BY DATE("Timestamp");
  1. If you want to calculate the cumulative revenue for each day disregarding each sale's price, you can use :
SELECT day, daily_price, SUM(daily_price) OVER (ORDER BY day)
FROM (
         SELECT DATE("Timestamp") AS day, SUM("Price") AS daily_price
         FROM cleandataset
         GROUP BY DATE("Timestamp")
) req
ORDER BY day;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You seem to want:

select date_trunc('day', "Timestamp") as day, "Price",
       sum("Price") over (order by date_trunc('day', "Timestamp"))
from cleandataset 
order by day;

Your version had some typos.

Upvotes: 1

Related Questions