By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS subscriptions
(account_id int,
plan_id int,
start_date timestamp);
INSERT INTO subscriptions
(account_id, plan_id, start_date)
VALUES (1, 1, '2018-01-03 14:52:13'),
(2, 3, '2018-01-03 17:40:05'),
(3, 4, '2018-01-04 09:14:01'),
(1, 3, '2018-02-07 11:10:17'),
(1, 2, '2018-02-07 11:11:17'),
(2, 4, '2018-03-25 07:09:00');
CREATE TABLE IF NOT EXISTS plans
(id int,
plan_interval varchar(5),
price decimal(8,2));
INSERT INTO plans
(id, plan_interval, price)
VALUES (1, 'month', 3900),
(2, 'month', 9900),
(3, 'year', 99900);
select *
from subscriptions s join
plans p
on s.plan_id = p.id
| account_id | plan_id | start_date | id | plan_interval | price |
|---|---|---|---|---|---|
| 1 | 1 | 2018-01-03 14:52:13 | 1 | month | 3900.00 |
| 2 | 3 | 2018-01-03 17:40:05 | 3 | year | 99900.00 |
| 1 | 3 | 2018-02-07 11:10:17 | 3 | year | 99900.00 |
| 1 | 2 | 2018-02-07 11:11:17 | 2 | month | 9900.00 |
select s.*, p.price / 12
from (select s.*,
lag(plan_id) over (partition by account_id order by start_date) as prev_plan_id,
lag(start_date) over (partition by account_id order by start_date) as prev_start_date
from subscriptions s
) s join
plans p
on s.prev_plan_id = p.id
where plan_id = 4 and prev_plan_id <> 4
| account_id | plan_id | start_date | prev_plan_id | prev_start_date | p.price / 12 |
|---|---|---|---|---|---|
| 2 | 4 | 2018-03-25 07:09:00 | 3 | 2018-01-03 17:40:05 | 8325.000000 |