clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (40608 in the last week).

SELECT VERSION();
VERSION()
8.0.25
 hidden batch(es)


DROP TABLE IF EXISTS `transactions`; CREATE TABLE IF NOT EXISTS `transactions` ( `client_acount_id` CHAR(36), `amount` DECIMAL(10, 2), `created_at` DATETIME ); INSERT INTO `transactions` (`client_acount_id`, `amount`, `created_at`) VALUES ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2021-01-03 02:44:00'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2021-01-08 12:22:48'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-08-03 16:03:38'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-08-30 10:08:57'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-09-30 10:09:43'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-09-30 10:09:43'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-01 10:37:13'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-01 12:34:07'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-02 12:48:54'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-02 14:53:34'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-03 12:56:40'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-03 18:27:28'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-05 10:42:42'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-05 10:42:48'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-05 16:31:00'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-06 12:11:08'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-30 09:10:13'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-03-31 05:00:22'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-03-01 00:00:39'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-02-02 04:12:37'), ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-02-29 18:59:11');
 hidden batch(es)


SELECT `der`.`year_month`, SUM(`der`.`amount`) `amount` FROM ( SELECT IF( DAY(`created_at`) > 5 /* Start pay day: 6 */, DATE_FORMAT(`created_at`, '%Y-%m'), DATE_FORMAT(DATE_ADD(`created_at`, INTERVAL -1 MONTH), '%Y-%m') ) `year_month`, `amount` FROM `transactions` WHERE `client_acount_id` = '9d2bb89c-372d-11ea-827a-16ec3745526d' ) `der` GROUP BY `der`.`year_month` ORDER BY `der`.`year_month` DESC;
year_month amount
2021-01 29.99
2020-12 29.99
2020-10 59.98
2020-09 329.89
2020-08 29.99
2020-07 29.99
2020-03 29.99
2020-02 59.98
2020-01 29.99
 hidden batch(es)