MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE orders (
  `row_number` INTEGER,
  `order_no` INTEGER,
  `customer_id` INTEGER,
  `order_date` DATETIME,
  `dollar_value` FLOAT
);
INSERT INTO orders
  (`row_number`, `order_no`, `customer_id`, `order_date`, `dollar_value`)
VALUES
  ('1', '8888883', '1111100', '2022-02-14 01:10:04', '2256.0'),
  ('2', '8888837', '1111100', '2022-02-12 05:46:32', '1457.2'),
  ('3', '8888812', '1111171', '2022-02-11 22:53:50', '1757.2'),
  ('4', '8888887', '1111171', '2022-02-11 06:49:36', '1350.2'),
  ('5', '8888804', '1111100', '2022-02-11 03:10:07', '1853.6'),
  ('6', '8888866', '1111171', '2022-02-11 01:20:26', '1053.0'),
  ('7', '8888833', '1111181', '2022-02-10 21:09:05', '253.2'),
  ('8', '8888874', '1111171', '2022-02-10 18:06:55', '1958.6'),
  ('9', '8888829', '1111171', '2022-02-10 10:11:59', '1456.2'),
  ('10', '8888802', '1111171', '2022-02-10 09:55:31', '956.6'),
  ('11', '8888835', '1111100', '2022-02-09 19:40:24', '756.4'),
  ('12', '8888810', '1111123', '2022-02-09 01:34:56', '3179.5'),
  ('13', '8888850', '1111100', '2022-02-08 20:00:20', '629.0'),
  ('14', '8888821', '1111171', '2022-02-08 17:59:05', '1249.45'),
  ('15', '8888809', '1111171', '2022-02-08 06:25:15', '1250.0'),
  ('16', '8888837', '1111147', '2022-02-08 06:18:15', '184.6'),
  ('17', '8888836', '1111171', '2022-02-07 12:01:47', '88.28'),
  ('18', '8888808', '1111147', '2022-02-05 12:02:49', '3008.7'),
  ('19', '8888890', '1111147', '2022-02-05 11:48:16', '1543.31'),
  ('20', '8888805', '1111147', '2022-02-05 11:37:55', '2617.4');
SELECT o.customer_id, o.order_date,
       COUNT(c.customer_id) AS trans_count
FROM orders o
JOIN (SELECT customer_id, order_date
     FROM orders
     ) c ON c.customer_id = o.customer_id AND c.order_date BETWEEN o.order_date - INTERVAL 1 DAY AND o.order_date
GROUP BY o.customer_id, o.order_date
ORDER BY o.order_date DESC


customer_id
order_date
trans_count


1111100
2022-02-14 01:10:04
1

1111100
2022-02-12 05:46:32
1

1111171
2022-02-11 22:53:50
3

1111171
2022-02-11 06:49:36
5

1111100
2022-02-11 03:10:07
1

1111171
2022-02-11 01:20:26
4

1111181
2022-02-10 21:09:05
1

1111171
2022-02-10 18:06:55
3

1111171
2022-02-10 10:11:59
2

1111171
2022-02-10 09:55:31
1

1111100
2022-02-09 19:40:24
2

1111123
2022-02-09 01:34:56
1

1111100
2022-02-08 20:00:20
1

1111171
2022-02-08 17:59:05
2

1111171
2022-02-08 06:25:15
2

1111147
2022-02-08 06:18:15
1

1111171
2022-02-07 12:01:47
1

1111147
2022-02-05 12:02:49
3

1111147
2022-02-05 11:48:16
2

1111147
2022-02-05 11:37:55
1

customer_id	order_date	trans_count
1111100	2022-02-14 01:10:04	1
1111100	2022-02-12 05:46:32	1
1111171	2022-02-11 22:53:50	3
1111171	2022-02-11 06:49:36	5
1111100	2022-02-11 03:10:07	1
1111171	2022-02-11 01:20:26	4
1111181	2022-02-10 21:09:05	1
1111171	2022-02-10 18:06:55	3
1111171	2022-02-10 10:11:59	2
1111171	2022-02-10 09:55:31	1
1111100	2022-02-09 19:40:24	2
1111123	2022-02-09 01:34:56	1
1111100	2022-02-08 20:00:20	1
1111171	2022-02-08 17:59:05	2
1111171	2022-02-08 06:25:15	2
1111147	2022-02-08 06:18:15	1
1111171	2022-02-07 12:01:47	1
1111147	2022-02-05 12:02:49	3
1111147	2022-02-05 11:48:16	2
1111147	2022-02-05 11:37:55	1