users_balance.sql

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(6) unsigned NOT NULL,
  `phone` int(9) unsigned NOT NULL,
  `recharge_amount` int(3)  NOT NULL,
  `balance_before_recharge` int(3) NOT NULL,
  PRIMARY KEY (`id`,`phone`)
) DEFAULT CHARSET=utf8;
INSERT INTO `users` (`id`, `phone`, `recharge_amount`, `balance_before_recharge`) VALUES
  ('1', '930000001', '5', '2'),
  ('2', '930000001', '10', '3'),
  ('3', '930000001', '11', '4'),
  ('4', '930000002', '14', '7'),
  ('5', '930000003', '13', '6');

ALTER TABLE `users`
ADD  `balance_spent` decimal(19,3) NULL;

update users u join
       (select users.*,
              (select next.balance_before_recharge
               from users next
               where next.id > users.id
               order by id asc
               limit 1
              ) as next_col
        from users
       ) uu
       on uu.id = u.id
    set u.balance_spent = ((u.recharge_amount + u.balance_before_recharge - uu.next_col) / (u.recharge_amount + u.balance_before_recharge));