2CXGBy2G

1CREATE TABLE IF NOT EXISTS `payments` (
2  `id` int(6) unsigned NOT NULL,
3  `deal_id` int(6) unsigned NOT NULL,
4  `payment_date` date NOT NULL,
5  `amount`int(6) unsigned NOT NULL,
6  PRIMARY KEY (`id`, `deal_id`)
7) DEFAULT CHARSET=utf8;
8INSERT INTO payments (`id`, `deal_id`, `payment_date`, `amount`) VALUES
9  (1, 42, '2019-10-01', 5000),
10  (2, 151, '2019-10-01', 45000),
11  (3, 42, '2019-10-17', 18000),
12  (4, 63, '2019-10-19', 5000),
13  (5, 11, '2019-10-22', 25000);
14
15
16select
17    id,
18    deal_id,
19    payment_date,
20    SUM(amount) OVER w sum_amount,
21    ROW_NUMBER() OVER w number,
22    amount,
23    payment_date - FIRST_VALUE(payment_date)  OVER w AS days_from_first_transaction
24from payments
25WINDOW w AS (PARTITION BY deal_id ORDER BY payment_date ASC);
26