customerID purchase_date revenue
1 2015-10-11 20
2 2015-10-12 25
1 2015-10-15 10
2 2015-10-16 21
1 2015-10-19 30
3 2015-10-20 75
3 2015-10-25 50
2 2015-10-17 54
2 2015-10-19 5
1 2015-10-20 45
1 2015-10-22 10
3 2015-10-27 5
I have a table named mytest
I want to solve the following questions
- "With" the "table" “mytest”, write an SQL query that calculates the first(!) date (YYYY-MM-DD) on which a customer’s cumulative purchase_revenue has reached 100€ (>=).
Output columns: date (DATE), customer_id (INTEGER), purchase_revenue
-
With the table “mytest”, write an SQL query that returns only customers whose cumulative purchases are above 100€.
-
With the table “mytest”, write an SQL query that calculates the number of days between the first and last purchase for each customer and the revenue per day in this timespan.
Output columns: customer_id (INTEGER), days_between_last_and_first_purchase (INTEGER), revenue_per_day (FLOAT)
Instruction:
I was close to the first question, but i couldnt figure it out furthermore
Here is my solution:
select min(Date(a.purchase_date)) as mydate, a.customerID, sum(b.revenue) as rev
from mytest a, mytest b
WHERE a.purchase_date >=(b.purchase_date) AND a.customerID=b.customerID
group by a.customerID,a.purchase_date
having sum(b.revenue)>=100
order by a.customerID;
I got the output:
mydate customerID rev
2015-10-20 1 105
2015-10-22 1 115
2015-10-17 2 100
2015-10-19 2 105
2015-10-25 3 125
2015-10-27 3 130
Please, I want solutions to be as simple as possible. Also I dont want OVER(PARTITION BY) function in the solution
Thanks in advance