MySQL 8.0

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

select version();


version()


8.0.30


CREATE TABLE transactions (
  id INT,
  timestamp date,
  quantity INT
);

INSERT INTO transactions VALUES ( 1, '2021-10-15', '10');
INSERT INTO transactions VALUES ( 1, '2021-10-16', '3');
INSERT INTO transactions VALUES ( 1, '2021-10-17', '0');
INSERT INTO transactions VALUES ( 1, '2021-10-18', '9');
INSERT INTO transactions VALUES ( 1, '2021-10-19', '15');
INSERT INTO transactions VALUES ( 1, '2021-10-20', '15');
INSERT INTO transactions VALUES ( 1, '2021-10-21', '15');
SELECT b.date, SUM(COALESCE(a.quantity, 0))
FROM (
 SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 7 DAY), t3*1000 + t2*100 + t1*10 + t0)) AS `date` 
       FROM (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
) b 
LEFT JOIN transactions a ON DATE(a.timestamp) <= b.date
WHERE b.date BETWEEN DATE(NOW()) - INTERVAL 6 DAY AND DATE(NOW())
GROUP BY b.date
ORDER BY b.date ASC


date
SUM(COALESCE(a.quantity, 0))


2022-09-22
67

2022-09-23
67

2022-09-24
67

2022-09-25
67

2022-09-26
67

2022-09-27
67

2022-09-28
67

date	SUM(COALESCE(a.quantity, 0))
2022-09-22	67
2022-09-23	67
2022-09-24	67
2022-09-25	67
2022-09-26	67
2022-09-27	67
2022-09-28	67