SQLTeam.com | Weblogs | Forums

SQL Query issue

​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

  1. "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

  1. With the table “mytest”, write an SQL query that returns only customers whose cumulative purchases are above 100€.

  2. 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

You need to make a reasonable attempt at your own homework/assignment first, then we can guide you from there.

1 Like