CREATE TABLE orders (
    amount INT,
    order_date DATE,
    order_id INT PRIMARY KEY,
    product_id VARCHAR(5)
);
 
CREATE TABLE calendar_dim (
    cal_date DATE PRIMARY KEY
);
 
INSERT INTO calendar_dim (cal_date) VALUES
('2024-01-01'),
('2024-01-02'),
('2024-01-03'),
('2024-01-04'),
('2024-01-05'),
('2024-01-06'),
('2024-01-07'),
('2024-01-08'),
('2024-01-09'),
('2024-01-10'),
('2024-01-11'),
('2024-01-12'),
('2024-01-13'),
('2024-01-14'),
('2024-01-15'),
('2024-01-16'),
('2024-01-17'),
('2024-01-18'),
('2024-01-19'),
('2024-01-20'),
('2024-01-21'),
('2024-01-22'),
('2024-01-23'),
('2024-01-24'),
('2024-01-25'),
('2024-01-26'),
('2024-01-27'),
('2024-01-28'),
('2024-01-29'),
('2024-01-30'),
('2024-01-31');
 
INSERT INTO orders (order_id, order_date, product_id, amount) VALUES
(1, '2024-01-01', 'p1', 100),
(2, '2024-01-01', 'p1', 150),
(3, '2024-01-03', 'p1', 120),
(4, '2024-01-04', 'p2', 200),
(5, '2024-01-05', 'p1', 180),
(6, '2024-01-06', 'p1', 110),
(7, '2024-01-07', 'p1', 220),
(8, '2024-01-08', 'p2', 130),
(9, '2024-01-09', 'p1', 190),
(10, '2024-01-10', 'p2', 240),
(11, '2024-01-11', 'p1', 140),
(12, '2024-01-12', 'p2', 200),
(13, '2024-01-13', 'p2', 260),
(14, '2024-01-14', 'p2', 150),
(15, '2024-01-15', 'p1', 210),
(16, '2024-01-16', 'p2', 280),
(17, '2024-01-16', 'p2', 160),
(18, '2024-01-18', 'p2', 220),
(19, '2024-01-19', 'p1', 300),
(20, '2024-01-20', 'p1', 170),
(21, '2024-01-21', 'p1', 230),
(22, '2024-01-22', 'p2', 320),
(23, '2024-01-23', 'p1', 180),
(24, '2024-01-24', 'p1', 240),
(25, '2024-01-25', 'p1', 340),
(26, '2024-01-26', 'p2', 190),
(27, '2024-01-27', 'p1', 250),
(28, '2024-01-28', 'p2', 360),
(29, '2024-01-29', 'p1', 200),
(30, '2024-01-30', 'p2', 260);
 
with cte as (
select product_id,order_date,sum(amount) as sales
 from orders
group by product_id,order_date
)
, all_products_dates as (
select distinct product_id, cal_date as order_date
from cte 
cross join calendar_dim
)
select a.product_id,a.order_date,coalesce(cte.sales,0) as sales
,sum(coalesce(cte.sales,0)) over(partition by a.product_id order by a.order_date rows between 2 preceding and current row) as rolling3_sum
from all_products_dates a 
left join cte on a.product_id=cte.product_id and a.order_date=cte.order_date
ORDER BY a.product_id , a.order_date;

Q1JFQVRFIFRBQkxFIG9yZGVycyAoCiAgICBhbW91bnQgSU5ULAogICAgb3JkZXJfZGF0ZSBEQVRFLAogICAgb3JkZXJfaWQgSU5UIFBSSU1BUlkgS0VZLAogICAgcHJvZHVjdF9pZCBWQVJDSEFSKDUpCik7CgpDUkVBVEUgVEFCTEUgY2FsZW5kYXJfZGltICgKICAgIGNhbF9kYXRlIERBVEUgUFJJTUFSWSBLRVkKKTsKCklOU0VSVCBJTlRPIGNhbGVuZGFyX2RpbSAoY2FsX2RhdGUpIFZBTFVFUwooJzIwMjQtMDEtMDEnKSwKKCcyMDI0LTAxLTAyJyksCignMjAyNC0wMS0wMycpLAooJzIwMjQtMDEtMDQnKSwKKCcyMDI0LTAxLTA1JyksCignMjAyNC0wMS0wNicpLAooJzIwMjQtMDEtMDcnKSwKKCcyMDI0LTAxLTA4JyksCignMjAyNC0wMS0wOScpLAooJzIwMjQtMDEtMTAnKSwKKCcyMDI0LTAxLTExJyksCignMjAyNC0wMS0xMicpLAooJzIwMjQtMDEtMTMnKSwKKCcyMDI0LTAxLTE0JyksCignMjAyNC0wMS0xNScpLAooJzIwMjQtMDEtMTYnKSwKKCcyMDI0LTAxLTE3JyksCignMjAyNC0wMS0xOCcpLAooJzIwMjQtMDEtMTknKSwKKCcyMDI0LTAxLTIwJyksCignMjAyNC0wMS0yMScpLAooJzIwMjQtMDEtMjInKSwKKCcyMDI0LTAxLTIzJyksCignMjAyNC0wMS0yNCcpLAooJzIwMjQtMDEtMjUnKSwKKCcyMDI0LTAxLTI2JyksCignMjAyNC0wMS0yNycpLAooJzIwMjQtMDEtMjgnKSwKKCcyMDI0LTAxLTI5JyksCignMjAyNC0wMS0zMCcpLAooJzIwMjQtMDEtMzEnKTsKCklOU0VSVCBJTlRPIG9yZGVycyAob3JkZXJfaWQsIG9yZGVyX2RhdGUsIHByb2R1Y3RfaWQsIGFtb3VudCkgVkFMVUVTCigxLCAnMjAyNC0wMS0wMScsICdwMScsIDEwMCksCigyLCAnMjAyNC0wMS0wMScsICdwMScsIDE1MCksCigzLCAnMjAyNC0wMS0wMycsICdwMScsIDEyMCksCig0LCAnMjAyNC0wMS0wNCcsICdwMicsIDIwMCksCig1LCAnMjAyNC0wMS0wNScsICdwMScsIDE4MCksCig2LCAnMjAyNC0wMS0wNicsICdwMScsIDExMCksCig3LCAnMjAyNC0wMS0wNycsICdwMScsIDIyMCksCig4LCAnMjAyNC0wMS0wOCcsICdwMicsIDEzMCksCig5LCAnMjAyNC0wMS0wOScsICdwMScsIDE5MCksCigxMCwgJzIwMjQtMDEtMTAnLCAncDInLCAyNDApLAooMTEsICcyMDI0LTAxLTExJywgJ3AxJywgMTQwKSwKKDEyLCAnMjAyNC0wMS0xMicsICdwMicsIDIwMCksCigxMywgJzIwMjQtMDEtMTMnLCAncDInLCAyNjApLAooMTQsICcyMDI0LTAxLTE0JywgJ3AyJywgMTUwKSwKKDE1LCAnMjAyNC0wMS0xNScsICdwMScsIDIxMCksCigxNiwgJzIwMjQtMDEtMTYnLCAncDInLCAyODApLAooMTcsICcyMDI0LTAxLTE2JywgJ3AyJywgMTYwKSwKKDE4LCAnMjAyNC0wMS0xOCcsICdwMicsIDIyMCksCigxOSwgJzIwMjQtMDEtMTknLCAncDEnLCAzMDApLAooMjAsICcyMDI0LTAxLTIwJywgJ3AxJywgMTcwKSwKKDIxLCAnMjAyNC0wMS0yMScsICdwMScsIDIzMCksCigyMiwgJzIwMjQtMDEtMjInLCAncDInLCAzMjApLAooMjMsICcyMDI0LTAxLTIzJywgJ3AxJywgMTgwKSwKKDI0LCAnMjAyNC0wMS0yNCcsICdwMScsIDI0MCksCigyNSwgJzIwMjQtMDEtMjUnLCAncDEnLCAzNDApLAooMjYsICcyMDI0LTAxLTI2JywgJ3AyJywgMTkwKSwKKDI3LCAnMjAyNC0wMS0yNycsICdwMScsIDI1MCksCigyOCwgJzIwMjQtMDEtMjgnLCAncDInLCAzNjApLAooMjksICcyMDI0LTAxLTI5JywgJ3AxJywgMjAwKSwKKDMwLCAnMjAyNC0wMS0zMCcsICdwMicsIDI2MCk7Cgp3aXRoIGN0ZSBhcyAoCnNlbGVjdCBwcm9kdWN0X2lkLG9yZGVyX2RhdGUsc3VtKGFtb3VudCkgYXMgc2FsZXMKIGZyb20gb3JkZXJzCmdyb3VwIGJ5IHByb2R1Y3RfaWQsb3JkZXJfZGF0ZQopCiwgYWxsX3Byb2R1Y3RzX2RhdGVzIGFzICgKc2VsZWN0IGRpc3RpbmN0IHByb2R1Y3RfaWQsIGNhbF9kYXRlIGFzIG9yZGVyX2RhdGUKZnJvbSBjdGUgCmNyb3NzIGpvaW4gY2FsZW5kYXJfZGltCikKc2VsZWN0IGEucHJvZHVjdF9pZCxhLm9yZGVyX2RhdGUsY29hbGVzY2UoY3RlLnNhbGVzLDApIGFzIHNhbGVzCixzdW0oY29hbGVzY2UoY3RlLnNhbGVzLDApKSBvdmVyKHBhcnRpdGlvbiBieSBhLnByb2R1Y3RfaWQgb3JkZXIgYnkgYS5vcmRlcl9kYXRlIHJvd3MgYmV0d2VlbiAyIHByZWNlZGluZyBhbmQgY3VycmVudCByb3cpIGFzIHJvbGxpbmczX3N1bQpmcm9tIGFsbF9wcm9kdWN0c19kYXRlcyBhIApsZWZ0IGpvaW4gY3RlIG9uIGEucHJvZHVjdF9pZD1jdGUucHJvZHVjdF9pZCBhbmQgYS5vcmRlcl9kYXRlPWN0ZS5vcmRlcl9kYXRlCk9SREVSIEJZIGEucHJvZHVjdF9pZCAsIGEub3JkZXJfZGF0ZTs=