I have a table of daily data that i'm trying to add trailing twelve months column to with the sum of the revenue by id, product, currency, and date truncated to month. I put together some random sample data. The question is, why do neither of the two aggregate function give the right answer?
create table test (day date, id int, product varchar, cur varchar, amount int);
insert into test
values
('10/1/2023', 259, 'A', 'USD', 558), ('9/30/2023', 1502, 'C', 'USD', 1991), ('9/29/2023', 1164, 'A', 'CAD', 1476), ('9/24/2023', 1469, 'A', 'CAD', 681), ('9/20/2023', 220, 'B', 'USD', 279), ('9/19/2023', 222, 'B', 'CAD', 1731), ('9/15/2023', 1822, 'B', 'CAD', 85), ('9/12/2023', 1888, 'A', 'USD', 251), ('9/10/2023', 1457, 'A', 'USD', 584), ('9/6/2023', 179, 'A', 'CAD', 1291), ('9/4/2023', 1307, 'C', 'USD', 944), ('9/3/2023', 374, 'B', 'USD', 894), ('8/29/2023', 866, 'B', 'CAD', 349), ('8/28/2023', 1053, 'B', 'CAD', 171), ('8/23/2023', 1493, 'C', 'USD', 1439), ('8/22/2023', 655, 'B', 'USD', 82), ('8/20/2023', 751, 'A', 'USD', 1994), ('8/19/2023', 517, 'B', 'CAD', 1425), ('8/18/2023', 1233, 'B', 'CAD', 570), ('8/16/2023', 77, 'C', 'USD', 384), ('8/12/2023', 315, 'A', 'USD', 1689), ('8/11/2023', 1968, 'A', 'USD', 1816), ('8/11/2023', 121, 'B', 'CAD', 628), ('8/9/2023', 1868, 'B', 'USD', 1940), ('8/8/2023', 1054, 'C', 'USD', 1176), ('8/6/2023', 336, 'C', 'CAD', 1864), ('8/6/2023', 1861, 'C', 'CAD', 908), ('7/31/2023', 1151, 'C', 'USD', 1531), ('7/29/2023', 1385, 'A', 'USD', 1115), ('7/27/2023', 468, 'B', 'CAD', 446), ('7/24/2023', 425, 'B', 'CAD', 1419), ('7/18/2023', 1784, 'B', 'USD', 1688), ('7/18/2023', 595, 'B', 'USD', 980), ('7/18/2023', 374, 'B', 'USD', 838), ('7/18/2023', 1159, 'C', 'USD', 10), ('7/17/2023', 374, 'A', 'USD', 1085), ('7/14/2023', 1838, 'B', 'CAD', 827), ('7/14/2023', 893, 'C', 'USD', 940), ('7/9/2023', 3, 'B', 'CAD', 1831), ('7/9/2023', 1655, 'C', 'CAD', 1802), ('7/6/2023', 787, 'C', 'CAD', 1493), ('7/5/2023', 247, 'A', 'USD', 1756), ('7/5/2023', 1073, 'B', 'CAD', 1660), ('7/4/2023', 1853, 'B', 'USD', 1955), ('7/4/2023', 371, 'A', 'USD', 202), ('7/1/2023', 376, 'A', 'USD', 1191), ('6/30/2023', 1990, 'B', 'CAD', 1234), ('6/28/2023', 1598, 'C', 'CAD', 1665), ('6/26/2023', 938, 'B', 'CAD', 1939), ('6/25/2023', 1940, 'A', 'USD', 678), ('6/22/2023', 1554, 'A', 'CAD', 1459), ('6/22/2023', 1491, 'C', 'CAD', 350), ('6/21/2023', 1215, 'C', 'USD', 1461), ('6/19/2023', 472, 'C', 'CAD', 250), ('6/16/2023', 1626, 'A', 'USD', 1120), ('6/15/2023', 1545, 'C', 'CAD', 1956), ('6/13/2023', 443, 'B', 'USD', 344), ('6/11/2023', 1251, 'B', 'USD', 1290), ('6/11/2023', 412, 'B', 'USD', 1542), ('6/10/2023', 350, 'C', 'USD', 1862), ('6/8/2023', 201, 'C', 'USD', 107), ('6/8/2023', 1171, 'B', 'CAD', 1364), ('6/6/2023', 1725, 'B', 'CAD', 570), ('6/6/2023', 1050, 'C', 'CAD', 947), ('6/6/2023', 1588, 'C', 'CAD', 83), ('6/4/2023', 163, 'C', 'USD', 319), ('6/4/2023', 276, 'A', 'USD', 1159), ('5/30/2023', 1000, 'C', 'CAD', 564), ('5/29/2023', 1700, 'C', 'USD', 1498), ('5/27/2023', 474, 'A', 'USD', 1127), ('5/26/2023', 551, 'A', 'CAD', 1904), ('5/26/2023', 311, 'C', 'USD', 1036), ('5/25/2023', 590, 'B', 'USD', 958), ('5/24/2023', 1562, 'A', 'CAD', 836), ('5/23/2023', 400, 'A', 'USD', 907), ('5/23/2023', 1411, 'B', 'CAD', 1675), ('5/21/2023', 311, 'C', 'CAD', 1461), ('5/20/2023', 1243, 'B', 'CAD', 429), ('5/20/2023', 129, 'A', 'CAD', 542), ('5/17/2023', 1206, 'A', 'USD', 319), ('5/16/2023', 1024, 'A', 'USD', 934), ('5/15/2023', 262, 'B', 'USD', 1397), ('5/14/2023', 1586, 'C', 'USD', 845), ('5/14/2023', 1465, 'A', 'CAD', 881), ('5/14/2023', 1673, 'C', 'CAD', 1305), ('5/14/2023', 1516, 'A', 'CAD', 1393), ('5/13/2023', 1470, 'C', 'USD', 1573), ('5/12/2023', 1666, 'A', 'CAD', 548), ('5/12/2023', 248, 'C', 'CAD', 1160), ('5/11/2023', 779, 'C', 'USD', 1457), ('5/9/2023', 979, 'B', 'CAD', 1263), ('5/7/2023', 1649, 'A', 'CAD', 1873), ('5/5/2023', 1295, 'A', 'USD', 140), ('5/4/2023', 976, 'C', 'CAD', 1007), ('5/3/2023', 1742, 'B', 'USD', 38), ('5/1/2023', 1214, 'A', 'CAD', 1271), ('4/29/2023', 1979, 'A', 'CAD', 1422), ('4/28/2023', 797, 'C', 'CAD', 17), ('4/28/2023', 1988, 'A', 'CAD', 1362), ('4/26/2023', 13, 'C', 'CAD', 174), ('4/25/2023', 777, 'B', 'CAD', 1859), ('4/23/2023', 771, 'A', 'CAD', 644), ('4/21/2023', 884, 'A', 'USD', 444), ('4/20/2023', 1983, 'B', 'CAD', 484), ('4/19/2023', 634, 'C', 'USD', 1636), ('4/18/2023', 1771, 'A', 'USD', 933), ('4/13/2023', 1088, 'B', 'CAD', 1601), ('4/9/2023', 1954, 'A', 'CAD', 1825), ('4/8/2023', 1315, 'B', 'CAD', 693), ('4/6/2023', 392, 'A', 'USD', 1152), ('4/6/2023', 1668, 'B', 'CAD', 1507), ('3/28/2023', 31, 'A', 'USD', 621), ('3/26/2023', 1588, 'C', 'CAD', 1551), ('3/20/2023', 620, 'C', 'CAD', 847), ('3/18/2023', 1777, 'A', 'CAD', 1968), ('3/18/2023', 1899, 'A', 'CAD', 2), ('3/18/2023', 863, 'A', 'USD', 1704), ('3/16/2023', 1789, 'A', 'CAD', 1729), ('3/12/2023', 1726, 'A', 'USD', 641), ('3/9/2023', 282, 'C', 'CAD', 1190), ('3/8/2023', 1966, 'A', 'USD', 1253), ('3/7/2023', 945, 'A', 'CAD', 174), ('3/6/2023', 422, 'C', 'USD', 931), ('3/2/2023', 452, 'B', 'USD', 1131), ('3/1/2023', 1838, 'C', 'USD', 1096), ('2/24/2023', 1425, 'C', 'CAD', 1265), ('2/23/2023', 544, 'C', 'CAD', 1193), ('2/21/2023', 904, 'B', 'USD', 1886), ('2/20/2023', 1110, 'C', 'CAD', 1637), ('2/16/2023', 717, 'B', 'USD', 97), ('2/15/2023', 1274, 'A', 'USD', 338), ('2/10/2023', 1090, 'A', 'CAD', 576), ('2/8/2023', 760, 'C', 'CAD', 1745), ('2/6/2023', 1469, 'B', 'CAD', 1349), ('2/4/2023', 928, 'B', 'USD', 1935), ('2/4/2023', 1458, 'C', 'USD', 497), ('2/3/2023', 350, 'C', 'CAD', 458), ('2/3/2023', 1901, 'B', 'USD', 1014), ('2/1/2023', 395, 'B', 'CAD', 4), ('1/24/2023', 957, 'A', 'CAD', 468), ('1/24/2023', 1690, 'C', 'USD', 1815), ('1/23/2023', 1820, 'C', 'CAD', 639), ('1/22/2023', 959, 'A', 'CAD', 821), ('1/21/2023', 1614, 'A', 'USD', 417), ('1/20/2023', 218, 'B', 'USD', 109), ('1/19/2023', 1923, 'A', 'USD', 379), ('1/16/2023', 760, 'C', 'CAD', 266), ('1/15/2023', 850, 'C', 'USD', 742), ('1/13/2023', 1868, 'C', 'USD', 847), ('1/13/2023', 1150, 'A', 'USD', 793), ('1/10/2023', 934, 'B', 'USD', 1519), ('1/6/2023', 1786, 'C', 'USD', 1105), ('12/31/2022', 1727, 'C', 'CAD', 1745), ('12/27/2022', 1469, 'C', 'USD', 387), ('12/26/2022', 1294, 'C', 'CAD', 1237), ('12/25/2022', 1943, 'A', 'USD', 954), ('12/25/2022', 428, 'A', 'CAD', 205), ('12/24/2022', 1676, 'A', 'CAD', 639), ('12/23/2022', 310, 'C', 'USD', 1719), ('12/21/2022', 1475, 'B', 'USD', 1285), ('12/19/2022', 861, 'A', 'USD', 894), ('12/19/2022', 1525, 'B', 'USD', 447), ('12/18/2022', 1099, 'A', 'CAD', 53), ('12/17/2022', 1339, 'C', 'USD', 961), ('12/17/2022', 1563, 'A', 'CAD', 1264), ('12/17/2022', 841, 'B', 'CAD', 1811), ('12/14/2022', 155, 'A', 'CAD', 733), ('12/13/2022', 1668, 'C', 'USD', 1230), ('12/13/2022', 1897, 'C', 'CAD', 191), ('12/10/2022', 1125, 'A', 'USD', 1885), ('12/10/2022', 629, 'C', 'USD', 1825), ('12/5/2022', 1012, 'A', 'USD', 1005), ('12/5/2022', 40, 'C', 'CAD', 1216), ('12/3/2022', 1613, 'A', 'USD', 1511), ('11/30/2022', 1111, 'C', 'CAD', 390), ('11/29/2022', 90, 'A', 'CAD', 933), ('11/29/2022', 1697, 'B', 'USD', 1357), ('11/27/2022', 1747, 'A', 'CAD', 827), ('11/23/2022', 70, 'C', 'CAD', 600), ('11/22/2022', 1210, 'C', 'USD', 308), ('11/21/2022', 1432, 'C', 'USD', 884), ('11/20/2022', 1585, 'B', 'CAD', 618), ('11/19/2022', 1379, 'A', 'USD', 1732), ('11/19/2022', 648, 'A', 'USD', 1502), ('11/18/2022', 1608, 'B', 'CAD', 1065), ('11/17/2022', 794, 'C', 'USD', 1702), ('11/17/2022', 1683, 'B', 'USD', 1054), ('11/16/2022', 1884, 'B', 'USD', 1713), ('11/16/2022', 1960, 'C', 'USD', 1425), ('11/14/2022', 1578, 'B', 'CAD', 1827), ('11/13/2022', 1742, 'B', 'CAD', 529), ('11/11/2022', 870, 'C', 'CAD', 1579), ('11/11/2022', 91, 'B', 'USD', 1822), ('11/10/2022', 1120, 'B', 'CAD', 319), ('11/10/2022', 1376, 'A', 'CAD', 193), ('11/9/2022', 227, 'B', 'CAD', 471), ('11/8/2022', 1323, 'B', 'CAD', 1297), ('11/6/2022', 950, 'C', 'CAD', 1021), ('11/4/2022', 1113, 'C', 'USD', 1102), ('10/31/2022', 1679, 'A', 'USD', 1128), ('10/30/2022', 1722, 'C', 'CAD', 1504), ('10/30/2022', 32, 'B', 'CAD', 1955), ('10/27/2022', 1247, 'A', 'CAD', 811), ('10/24/2022', 879, 'A', 'CAD', 1723), ('10/24/2022', 401, 'A', 'USD', 1388), ('10/24/2022', 1280, 'B', 'CAD', 1561), ('10/22/2022', 646, 'B', 'USD', 486), ('10/21/2022', 302, 'C', 'CAD', 1384), ('10/19/2022', 692, 'A', 'CAD', 1663), ('10/17/2022', 1728, 'C', 'USD', 396), ('10/17/2022', 1367, 'C', 'USD', 976), ('10/16/2022', 1877, 'A', 'CAD', 1761), ('10/11/2022', 213, 'A', 'CAD', 677), ('10/4/2022', 660, 'B', 'CAD', 1698), ('10/2/2022', 881, 'A', 'CAD', 1417), ('10/1/2022', 1651, 'A', 'CAD', 310), ('9/29/2022', 1444, 'B', 'CAD', 1264), ('9/29/2022', 1596, 'A', 'USD', 396), ('9/28/2022', 1562, 'C', 'USD', 280), ('9/26/2022', 1918, 'A', 'CAD', 625), ('9/25/2022', 1013, 'C', 'USD', 1313), ('9/24/2022', 1256, 'B', 'CAD', 1800), ('9/24/2022', 971, 'B', 'USD', 1170), ('9/23/2022', 1984, 'C', 'CAD', 1367), ('9/19/2022', 1574, 'B', 'USD', 956), ('9/18/2022', 1346, 'B', 'CAD', 472), ('9/17/2022', 442, 'B', 'CAD', 133), ('9/11/2022', 1002, 'B', 'CAD', 127), ('9/9/2022', 904, 'A', 'USD', 497), ('8/31/2022', 1354, 'B', 'USD', 590), ('8/28/2022', 1461, 'A', 'CAD', 1745), ('8/26/2022', 1176, 'B', 'CAD', 1916), ('8/24/2022', 629, 'C', 'CAD', 1534), ('8/23/2022', 391, 'C', 'USD', 1502), ('8/21/2022', 1447, 'B', 'USD', 1845), ('8/19/2022', 1030, 'C', 'USD', 1846), ('8/18/2022', 1860, 'C', 'CAD', 1051), ('8/18/2022', 1578, 'B', 'CAD', 86), ('8/18/2022', 657, 'B', 'CAD', 1408), ('8/16/2022', 412, 'A', 'USD', 89), ('8/16/2022', 947, 'C', 'CAD', 711), ('8/15/2022', 1453, 'C', 'USD', 1061), ('8/14/2022', 157, 'B', 'USD', 757), ('8/13/2022', 1429, 'B', 'USD', 187), ('8/13/2022', 1532, 'A', 'CAD', 1050), ('8/12/2022', 1478, 'B', 'USD', 1441), ('8/2/2022', 1071, 'B', 'USD', 102), ('7/31/2022', 629, 'B', 'USD', 1419), ('7/28/2022', 1150, 'A', 'USD', 830), ('7/28/2022', 805, 'B', 'USD', 1944), ('7/27/2022', 1630, 'B', 'CAD', 1182), ('7/21/2022', 1363, 'A', 'CAD', 57), ('7/19/2022', 1718, 'C', 'CAD', 1409), ('7/18/2022', 1183, 'A', 'CAD', 387), ('7/17/2022', 967, 'B', 'USD', 1734), ('7/15/2022', 1525, 'C', 'USD', 1089), ('7/14/2022', 1443, 'A', 'USD', 671), ('7/13/2022', 132, 'B', 'USD', 1817), ('7/10/2022', 1026, 'C', 'CAD', 1412), ('7/9/2022', 616, 'C', 'CAD', 1251), ('7/9/2022', 898, 'C', 'USD', 798), ('7/9/2022', 1314, 'A', 'CAD', 1459), ('7/8/2022', 500, 'B', 'CAD', 840), ('7/6/2022', 1450, 'A', 'CAD', 82), ('7/4/2022', 343, 'C', 'USD', 950), ('7/4/2022', 1422, 'A', 'USD', 1447), ('6/30/2022', 1268, 'C', 'CAD', 544), ('6/29/2022', 1705, 'A', 'CAD', 83), ('6/27/2022', 689, 'C', 'CAD', 1782), ('6/26/2022', 846, 'C', 'USD', 713), ('6/24/2022', 467, 'C', 'USD', 1641), ('6/22/2022', 1012, 'A', 'CAD', 79), ('6/20/2022', 968, 'C', 'CAD', 1681), ('6/20/2022', 854, 'C', 'CAD', 1454), ('6/19/2022', 12, 'B', 'USD', 1395), ('6/18/2022', 299, 'B', 'USD', 1372), ('6/15/2022', 626, 'B', 'USD', 543), ('6/15/2022', 1134, 'B', 'CAD', 1121), ('6/14/2022', 179, 'C', 'CAD', 1996), ('6/12/2022', 1702, 'A', 'USD', 411), ('6/11/2022', 1525, 'C', 'CAD', 3), ('6/11/2022', 122, 'A', 'CAD', 27), ('6/10/2022', 664, 'B', 'USD', 247), ('6/10/2022', 1514, 'B', 'CAD', 322), ('6/5/2022', 142, 'C', 'CAD', 1107), ('6/4/2022', 771, 'B', 'CAD', 1349), ('6/3/2022', 1447, 'B', 'CAD', 1263), ('5/29/2022', 1373, 'B', 'CAD', 895), ('5/29/2022', 1127, 'C', 'USD', 1626), ('5/28/2022', 137, 'B', 'USD', 555), ('5/24/2022', 722, 'C', 'CAD', 696), ('5/22/2022', 152, 'B', 'USD', 387), ('5/21/2022', 1128, 'C', 'USD', 1412), ('5/21/2022', 496, 'A', 'USD', 1101), ('5/19/2022', 207, 'A', 'CAD', 1570), ('5/18/2022', 885, 'A', 'USD', 862), ('5/16/2022', 572, 'B', 'CAD', 1514), ('5/12/2022', 561, 'A', 'CAD', 614), ('5/11/2022', 1183, 'B', 'CAD', 1349), ('5/10/2022', 1455, 'A', 'CAD', 1675), ('5/10/2022', 967, 'A', 'CAD', 1138), ('5/9/2022', 1833, 'B', 'USD', 733), ('5/7/2022', 1788, 'B', 'CAD', 512), ('5/7/2022', 737, 'A', 'CAD', 1210), ('5/5/2022', 216, 'B', 'CAD', 1945), ('5/2/2022', 1101, 'A', 'USD', 258), ('5/2/2022', 1290, 'C', 'USD', 1643), ('5/2/2022', 1449, 'A', 'CAD', 977), ('5/2/2022', 404, 'C', 'CAD', 1665), ('5/1/2022', 1667, 'C', 'USD', 1336), ('4/29/2022', 315, 'A', 'USD', 1766), ('4/29/2022', 1012, 'C', 'USD', 800), ('4/27/2022', 1576, 'C', 'USD', 586), ('4/25/2022', 1035, 'C', 'USD', 55), ('4/24/2022', 1358, 'B', 'USD', 523), ('4/19/2022', 441, 'A', 'CAD', 978), ('4/19/2022', 1158, 'A', 'CAD', 1037), ('4/18/2022', 1440, 'A', 'CAD', 1963), ('4/15/2022', 1280, 'A', 'USD', 658), ('4/14/2022', 444, 'B', 'USD', 1002), ('4/11/2022', 1790, 'A', 'CAD', 1704), ('4/10/2022', 1889, 'C', 'CAD', 1825), ('4/10/2022', 958, 'C', 'USD', 1255), ('4/9/2022', 1742, 'A', 'CAD', 729), ('4/8/2022', 648, 'A', 'USD', 1329), ('4/6/2022', 178, 'B', 'CAD', 1226), ('4/6/2022', 773, 'B', 'CAD', 160), ('4/6/2022', 940, 'C', 'USD', 730), ('4/5/2022', 572, 'C', 'CAD', 150), ('4/4/2022', 728, 'B', 'USD', 1503), ('4/4/2022', 195, 'B', 'CAD', 126), ('4/3/2022', 191, 'C', 'CAD', 1947), ('4/3/2022', 1708, 'B', 'USD', 290), ('4/2/2022', 1030, 'B', 'USD', 932), ('3/29/2022', 1377, 'C', 'USD', 1350), ('3/25/2022', 374, 'C', 'CAD', 777), ('3/21/2022', 1795, 'C', 'USD', 254), ('3/21/2022', 1991, 'C', 'USD', 100), ('3/20/2022', 356, 'A', 'CAD', 1280), ('3/18/2022', 1540, 'B', 'CAD', 210), ('3/16/2022', 1691, 'A', 'USD', 1643), ('3/14/2022', 1519, 'A', 'USD', 1963), ('3/12/2022', 1253, 'B', 'CAD', 827), ('3/11/2022', 960, 'B', 'CAD', 768), ('3/9/2022', 328, 'B', 'CAD', 264), ('3/3/2022', 339, 'C', 'CAD', 1185), ('3/2/2022', 672, 'B', 'CAD', 466), ('3/2/2022', 286, 'A', 'USD', 627), ('3/1/2022', 1830, 'A', 'CAD', 939), ('3/1/2022', 206, 'B', 'CAD', 1103), ('2/28/2022', 1402, 'A', 'USD', 90), ('2/26/2022', 1048, 'B', 'USD', 556), ('2/24/2022', 1272, 'B', 'USD', 1447), ('2/24/2022', 1431, 'C', 'CAD', 1843), ('2/24/2022', 468, 'A', 'CAD', 934), ('2/22/2022', 1998, 'C', 'USD', 44), ('2/22/2022', 247, 'B', 'USD', 327), ('2/18/2022', 956, 'B', 'USD', 1992), ('2/18/2022', 627, 'A', 'USD', 406), ('2/13/2022', 642, 'A', 'CAD', 384), ('2/13/2022', 1214, 'C', 'CAD', 1506), ('2/11/2022', 97, 'A', 'CAD', 556), ('2/11/2022', 434, 'A', 'CAD', 1259), ('2/7/2022', 738, 'A', 'USD', 354), ('2/7/2022', 492, 'B', 'USD', 140), ('2/6/2022', 498, 'A', 'USD', 1914), ('2/5/2022', 196, 'A', 'USD', 1144), ('2/5/2022', 1474, 'B', 'USD', 618), ('2/5/2022', 1639, 'A', 'CAD', 429), ('2/2/2022', 1847, 'A', 'USD', 1947), ('1/27/2022', 1304, 'C', 'CAD', 1331), ('1/27/2022', 613, 'C', 'CAD', 2000), ('1/26/2022', 1989, 'B', 'USD', 1675), ('1/26/2022', 1489, 'C', 'CAD', 611), ('1/26/2022', 715, 'C', 'CAD', 116), ('1/26/2022', 943, 'A', 'CAD', 397), ('1/23/2022', 1137, 'B', 'USD', 1415), ('1/23/2022', 153, 'B', 'CAD', 835), ('1/22/2022', 679, 'C', 'USD', 1286), ('1/21/2022', 1065, 'A', 'CAD', 1216), ('1/21/2022', 1282, 'A', 'CAD', 38), ('1/20/2022', 1759, 'B', 'USD', 1815), ('1/20/2022', 216, 'C', 'USD', 905), ('1/19/2022', 1756, 'A', 'USD', 995), ('1/18/2022', 1528, 'A', 'USD', 2000), ('1/17/2022', 459, 'C', 'CAD', 545), ('1/14/2022', 1261, 'A', 'CAD', 618), ('1/13/2022', 1931, 'B', 'USD', 1092), ('1/11/2022', 1448, 'C', 'CAD', 464), ('1/10/2022', 943, 'C', 'CAD', 700), ('1/9/2022', 977, 'B', 'CAD', 711), ('1/8/2022', 1567, 'B', 'USD', 228), ('1/7/2022', 1545, 'A', 'CAD', 1019), ('1/6/2022', 361, 'C', 'USD', 922), ('1/6/2022', 250, 'A', 'CAD', 1245), ('1/5/2022', 877, 'A', 'CAD', 1032), ('1/5/2022', 1584, 'C', 'CAD', 1959), ('1/5/2022', 198, 'C', 'CAD', 939), ('1/4/2022', 1069, 'B', 'USD', 699), ('9/17/2021', 1463, 'C', 'CAD', 733);
here
select sum(amount)
from test
where product = 'A'
and day >= '2022-11-01'
returns 66776, as that would be the right sum for trailing twelve months for october 2023.
The same result here:
with monthly as
(
select
id,
product,
cur,
date_trunc('month', day) :: date as month,
sum(amount) as monthly_total
from test
group by 1, 2, 3, 4
)
select
sum(monthly_total)
from monthly
where product = 'A'
and month <= '2023-10-01'
and month > '2022-10-01';
But the following give the wrong answer
select sum(ttm_total) s
from (
select
id,
product,
cur,
month,
sum(monthly_total) over (partition by id, product, cur
order by month
rows between 12 preceding and current row)
as ttm_total
from monthly) x
where product = 'A'
and month = '2023-10-01'
returns 558
This also returns 558:
with monthly as
(
select
id,
product,
cur,
date_trunc('month', day) :: date as month,
sum(amount) as monthly_total
from test
group by 1, 2, 3, 4
),
test as
(
SELECT
b1.id,
b1.product,
b1.cur,
b1.month,
(
SELECT SUM(monthly_total) AS ttm_total
FROM monthly AS b2
WHERE date_diff('month', b2.month, b1.month) <= 12
AND b1.id = b2.id
AND b1.product = b2.product
AND b1.cur = b2.cur
) AS ttm_total
FROM monthly AS b1
)
select
sum(ttm_total) s -- 558
from test
where product = 'A'
and month = '2023-10-01';
nm, figured it out ... The problem was that there were missing months for each set of (id, product, cur). This solved it:
with monthly as
(
select
id,
product,
cur,
date_trunc('month', day) :: date as month,
sum(amount) as monthly_total
from test
group by 1, 2, 3, 4
),
dates as
(
SELECT *
FROM
(
SELECT
date_add(
'day', row_number() OVER () - 1, '2019-01-01'
)::date AS full_date,
date_part('day', full_date) AS month_day_number
FROM some_table_with_enough_rows_to_account_for_all_dates
) AS d
WHERE full_date <= '2024-01-01'
ORDER BY full_date DESC
),
all_months as
(
select
d.month,
m.id,
m.product,
m.cur,
(select monthly_total
from monthly m2
where m2.id = m.id
and m2.product = m.product
and m2.cur = m.cur
and m2.month = d.month) as monthly_total
from
(
select
distinct date_trunc('month', full_date) :: date as month
from dates
where month between '2021-01-01' and getdate()
) d
cross join
(
select
id,
product,
cur
from monthly
group by 1, 2, 3
) as m
group by id, product, cur, month
order by id, product, cur, month
)
select sum(ttm_total) s
from (
select
id,
product,
cur,
month,
sum(monthly_total) over (partition by id, product, cur
order by month
rows between 11 preceding and current row) as ttm_total
from all_months) x
where product = 'A'
and month = '2023-10-01'
(i copy pasted the dates table from my db, but it could be simplified for this particular scenario to only contain months, not individual days)
The snippet with date_diff('month', b2.month, b1.month) <= 12 also works, just need to change <=12 to <12