I have user_cost information and hourly discount information. I am trying to get the below result using below two tables.
The discount_per_time is applied every time of user_cost, and the users of the same time shares the discount. Discounts are applied sequentially for each row, and are performed until no more discounts are available.
I thought it would be possible using presto's window function, so I tried it for a few days but couldn't do it. Can I create the below result through query?
table: user_cost
time user_id cost
1 aaa 20
1 bbb 5
2 aaa 11
2 bbb 5
3 aaa 15
4 aaa 1
4 bbb 1
table: discount_per_time
discount_id cost
d-1 10
d-2 5
RESULT:
time user_id type cost discount_id discounted_cost
1 aaa discount d-1 10
1 aaa discount d-2 5
1 aaa usage 5
1 bbb usage 5
2 aaa discount d-1 10
2 aaa discount d-2 1
2 bbb discount d-2 4
2 bbb usage 1
3 aaa discount d-1 10
3 aaa discount d-2 5
4 aaa discount d-1 1
4 bbb discount d-1 1
+ add:
- You should be able to see which discounts apply to which users and how much per time.
- The discounted_cost per time cannot be greater than the usage cost and the discountable cost.
- If all of the cost of the time is discounted, "usage" is not displayed (instead, the value before discount can be inferred through discounted_cost).
e.g.
- User 'aaa' uses a cost of 20 and user 'bbb' uses a cost of 5 in a time. Both the 10 discount of d-1 and the 5 discount of d-2 are applied to aaa, so the final cost of aaa becomes 5. All discounts are applied to aaa, so the final cost of bbb is still 5.
- aaa uses 11 and bbb uses 5. d-1 gives aaa a discount of 10, d-2 gives a discount of 1 so aaa does not charge. 4 out of 5 of d-2 left. This can be applied to bbbs. So the final cost of bbb is 1.
- When both users' cost is 1, those are discounted by using only 2 out of 10 of d-1. There is no final usage for both users.