SQLize Online / PHPize Online  /  SQLtest Online

create table persons ( id int auto_increment primary key, name varchar(255), wealth_sorce varchar(255) ); insert into persons (name, wealth_sorce) values ('Bill Gates', 'Microsoft'), ('Jeff Bezos', 'Amazon'); create table persons_wealth ( person_id int, wealth_sum bigint, updated_at date ); insert into persons_wealth values (1, 50000000000, '2020-01-01'),(2, 70000000000, '2020-01-01'), (1, 56000000000, '2021-01-01'),(2, 90000000000, '2021-01-01'); select distinct person_id, name, first_value(wealth_sum) over (partition by person_id order by updated_at) as start_wealth, first_value(wealth_sum) over (partition by person_id order by updated_at desc) as end_wealth from persons_wealth w join persons p on p.id = w.person_id; with wealth_data as ( select distinct person_id, name, first_value(wealth_sum) over (partition by person_id order by updated_at) as start_wealth, first_value(wealth_sum) over (partition by person_id order by updated_at desc) as current_wealth from persons_wealth w join persons p on p.id = w.person_id where updated_at >= '2020-01-01' ) select person_id, name, current_wealth, (current_wealth - start_wealth) / start_wealth as wealth_change_since_2020_01_01 from wealth_data