with chikn as (select

tokenflow_eth.hextoint(substr(data,1,66)) as tokenID,

tokenflow_eth.hextoint(substr(data,195,64))*pow(10,-18) as price,

concat('0x', substr(data,91,40)) as seller,

concat('0x', substr(data,155,40)) as buyer, *,

case when tokenID in (1231, 2138, 2757, 3604, 4386, 6969, 8312, 9117, 10000) then 'unique'

when tokenID in (5, 86, 113, 190, 211, 215, 300, 326, 452, 547, 601, 649, 671, 705, 778, 782, 783, 787, 854, 1065, 1078, 1091, 1159, 1283, 1334, 1502, 1618, 1620, 1671, 1675, 1706, 1745, 1750, 1807, 1837, 1899, 1939, 1952, 1965, 1987, 2085, 2131, 2193, 2244, 2247, 2279, 2551, 2613, 2630, 2699, 2753, 2848, 2852, 2871, 2970, 2984, 3024, 3130, 3184, 3228, 3236, 3265, 3296, 3359, 3371, 3382, 3399, 3401, 3406, 3646, 3651, 3793, 3794, 3805, 3922, 3982, 4004, 4090, 4101, 4135, 4163, 4183, 4270, 4370, 4437, 4531, 4567, 4718, 4878, 4897, 4953, 4967, 5006, 5066, 5109, 5172, 5229, 5407, 5449, 5477, 5585, 5675, 5715, 5726, 5802, 5925, 5950, 6023, 6065, 6083, 6103, 6124, 6226, 6470, 6539, 6548, 6615, 6667, 6746, 6834, 7065, 7094, 7103, 7134, 7207, 7294, 7312, 7348, 7378, 7403, 7416, 7490, 7539, 7551, 7606, 7620, 7677, 7709, 7793, 7840, 7893, 7895, 7911, 7966, 8067, 8087, 8098, 8158, 8251, 8384, 8419, 8446, 8467, 8478, 8481, 8529, 8566, 8592, 8617, 8657, 8664, 8674, 8700, 8715, 8749, 8758, 8921, 8951, 8953, 9013, 9044, 9095, 9139, 9322, 9327, 9332, 9427, 9431, 9491, 9538, 9606, 9621, 9728, 9733, 9755, 9815, 9867, 9935, 9967, 9980, 9993) then 'legendary'

when tokenID in (33, 38, 90, 125, 145, 147, 181, 200, 234, 259, 260, 315, 316, 318, 324, 327, 333, 347, 348, 384, 411, 444, 446, 454, 468, 479, 483, 542, 555, 563, 576, 610, 616, 640, 642, 672, 684, 690, 701, 720, 726, 764, 774, 807, 809, 814, 815, 843, 849, 852, 859, 873, 885, 914, 927, 944, 952, 955, 958, 985, 1012, 1022, 1107, 1112, 1121, 1125, 1128, 1143, 1210, 1219, 1223, 1226, 1232, 1241, 1256, 1266, 1322, 1351, 1352, 1374, 1380, 1392, 1394, 1424, 1425, 1439, 1487, 1506, 1523, 1602, 1612, 1635, 1645, 1664, 1683, 1711, 1721, 1722, 1733, 1747, 1749, 1755, 1757, 1774, 1795, 1826, 1827, 1830, 1838, 1884, 1908, 1910, 1918, 1930, 1947, 1955, 1977, 1993, 2031, 2032, 2048, 2050, 2072, 2076, 2086, 2105, 2128, 2143, 2152, 2153, 2161, 2188, 2206, 2207, 2210, 2212, 2220, 2240, 2302, 2323, 2340, 2342, 2357, 2363, 2370, 2389, 2399, 2446, 2536, 2547, 2552, 2561, 2563, 2566, 2636, 2642, 2647, 2652, 2654, 2692, 2704, 2714, 2715, 2799, 2819, 2821, 2851, 2864, 2881, 2931, 2942, 2962, 2981, 3000, 3007, 3013, 3014, 3051, 3063, 3077, 3082, 3083, 3099, 3107, 3108, 3109, 3138, 3140, 3145, 3192, 3200, 3201, 3244, 3257, 3258, 3267, 3294, 3299, 3312, 3314, 3318, 3328, 3329, 3339, 3340, 3347, 3358, 3361, 3372, 3383, 3416, 3425, 3438, 3531, 3534, 3545, 3548, 3578, 3596, 3612, 3636, 3653, 3667, 3671, 3690, 3691, 3702, 3707, 3730, 3740, 3741, 3777, 3807, 3810, 3821, 3950, 3957, 4003, 4010, 4013, 4062, 4070, 4071, 4080, 4129, 4172, 4175, 4185, 4199, 4206, 4211, 4250, 4253, 4254, 4259, 4264, 4268, 4273, 4279, 4297, 4303, 4309, 4328, 4332, 4335, 4340, 4407, 4411, 4413, 4458, 4470, 4476, 4487, 4493, 4517, 4527, 4548, 4549, 4557, 4565, 4580, 4582, 4593, 4594, 4600, 4628, 4686, 4690, 4707, 4731, 4749, 4756, 4773, 4780, 4786, 4797, 4849, 4855, 4859, 4861, 4900, 4902, 4913, 4940, 4950, 4961, 4989, 4994, 4997, 5002, 5028, 5044, 5052, 5081, 5089, 5110, 5117, 5140, 5148, 5166, 5167, 5259, 5282, 5283, 5366, 5368, 5369, 5399, 5448, 5466, 5490, 5491, 5492, 5546, 5565, 5613, 5631, 5635, 5642, 5657, 5693, 5769, 5772, 5774, 5798, 5801, 5807, 5814, 5821, 5824, 5828, 5835, 5847, 5876, 5933, 5949, 5991, 6003, 6010, 6013, 6016, 6025, 6043, 6051, 6086, 6089, 6104, 6150, 6168, 6191, 6217, 6222, 6236, 6240, 6254, 6262, 6286, 6325, 6363, 6366, 6394, 6446, 6488, 6507, 6530, 6544, 6578, 6609, 6631, 6644, 6648, 6651, 6653, 6683, 6710, 6717, 6718, 6719, 6735, 6753, 6757, 6761, 6762, 6807, 6854, 6866, 6867, 6869, 6930, 6939, 6941, 6945, 6949, 6981, 7002, 7006, 7023, 7072, 7075, 7088, 7095, 7105, 7108, 7141, 7149, 7158, 7166, 7199, 7223, 7237, 7281, 7292, 7310, 7330, 7335, 7351, 7380, 7384, 7394, 7398, 7399, 7436, 7450, 7467, 7469, 7477, 7501, 7503, 7513, 7537, 7556, 7559, 7564, 7568, 7574, 7594, 7619, 7624, 7655, 7674, 7703, 7722, 7768, 7782, 7807, 7846, 7859, 7880, 7899, 7922, 7929, 7930, 7941, 8002, 8026, 8029, 8045, 8071, 8079, 8137, 8147, 8163, 8185, 8250, 8263, 8337, 8342, 8368, 8375, 8376, 8392, 8400, 8403, 8424, 8441, 8456, 8471, 8473, 8475, 8492, 8507, 8519, 8542, 8554, 8558, 8565, 8579, 8583, 8588, 8604, 8619, 8627, 8648, 8654, 8667, 8682, 8687, 8689, 8701, 8726, 8728, 8742, 8776, 8780, 8798, 8808, 8821, 8837, 8840, 8842, 8846, 8855, 8860, 8864, 8882, 8890, 8906, 8969, 8990, 9002, 9023, 9024, 9099, 9121, 9128, 9147, 9157, 9168, 9172, 9179, 9195, 9205, 9243, 9310, 9331, 9349, 9353, 9357, 9367, 9379, 9390, 9392, 9396, 9413, 9430, 9454, 9462, 9470, 9488, 9497, 9499, 9511, 9523, 9532, 9547, 9554, 9555, 9560, 9578, 9599, 9652, 9664, 9673, 9679, 9683, 9690, 9718, 9756, 9821, 9827, 9844, 9849, 9859, 9874, 9886, 9931, 9941, 9969, 9972, 9979) then 'elite'

when tokenID in (3, 4, 17, 35, 39, 51, 55, 63, 67, 72, 73, 75, 94, 110, 126, 136, 148, 173, 175, 186, 191, 198, 201, 208, 217, 222, 223, 230, 231, 240, 242, 244, 247, 261, 265, 270, 271, 272, 282, 283, 296, 301, 304, 312, 321, 329, 338, 352, 354, 355, 356, 358, 360, 362, 365, 368, 382, 399, 400, 405, 407, 421, 430, 442, 451, 453, 459, 463, 473, 481, 482, 485, 486, 487, 507, 522, 525, 550, 554, 568, 578, 582, 592, 600, 606, 607, 618, 628, 637, 644, 647, 653, 664, 673, 692, 695, 731, 734, 741, 744, 754, 760, 770, 779, 781, 793, 802, 806, 810, 816, 820, 829, 840, 847, 868, 871, 883, 888, 895, 932, 948, 949, 963, 975, 986, 991, 992, 1023, 1031, 1043, 1047, 1067, 1069, 1075, 1079, 1080, 1101, 1131, 1132, 1136, 1142, 1148, 1151, 1152, 1161, 1166, 1169, 1172, 1180, 1182, 1190, 1197, 1198, 1203, 1207, 1215, 1217, 1246, 1261, 1262, 1267, 1268, 1286, 1288, 1292, 1294, 1297, 1298, 1306, 1319, 1331, 1365, 1367, 1370, 1402, 1412, 1414, 1416, 1417, 1418, 1420, 1428, 1436, 1442, 1458, 1468, 1484, 1486, 1488, 1489, 1490, 1495, 1518, 1527, 1539, 1546, 1547, 1552, 1564, 1579, 1590, 1591, 1593, 1601, 1603, 1627, 1630, 1631, 1640, 1642, 1651, 1658, 1660, 1661, 1670, 1672, 1685, 1687, 1702, 1708, 1748, 1791, 1794, 1805, 1819, 1831, 1836, 1842, 1848, 1852, 1869, 1885, 1887, 1892, 1894, 1896, 1901, 1905, 1921, 1926, 1929, 1938, 1961, 1966, 1968, 1978, 1985, 2000, 2003, 2004, 2007, 2013, 2019, 2022, 2023, 2034, 2044, 2052, 2059, 2077, 2081, 2087, 2101, 2104, 2106, 2124, 2132, 2134, 2137, 2142, 2149, 2172, 2179, 2196, 2199, 2202, 2209, 2213, 2217, 2222, 2223, 2224, 2235, 2245, 2253, 2288, 2292, 2298, 2300, 2301, 2303, 2310, 2334, 2337, 2341, 2344, 2347, 2364, 2367, 2371, 2388, 2391, 2397, 2398, 2424, 2434, 2435, 2441, 2443, 2450, 2456, 2457, 2467, 2470, 2473, 2480, 2481, 2495, 2501, 2518, 2546, 2549, 2555, 2560, 2573, 2595, 2603, 2617, 2618, 2621, 2625, 2627, 2633, 2641, 2643, 2656, 2657, 2659, 2660, 2665, 2669, 2680, 2683, 2702, 2708, 2712, 2726, 2729, 2762, 2767, 2771, 2775, 2776, 2790, 2800, 2810, 2837, 2841, 2849, 2855, 2856, 2874, 2888, 2894, 2906, 2907, 2908, 2909, 2913, 2919, 2932, 2937, 2938, 2944, 2964, 2965, 2967, 2977, 2993, 2995, 3001, 3002, 3030, 3039, 3

when tokenID in (1, 7, 10, 14, 19, 20, 40, 41, 43, 45, 50, 54, 56, 57, 58, 59, 62, 64, 66, 68, 70, 81, 83, 84, 91, 93, 103, 106, 107, 108, 114, 116, 119, 121, 122, 124, 128, 130, 132, 140, 141, 142, 150, 151, 155, 157, 163, 169, 170, 171, 172, 174, 179, 182, 183, 184, 187, 189, 196, 197, 203, 204, 205, 206, 212, 213, 214, 216, 218, 219, 220, 229, 232, 235, 236, 246, 248, 250, 252, 254, 262, 264, 268, 269, 273, 274, 280, 286, 291, 294, 295, 299, 302, 306, 309, 311, 313, 317, 319, 322, 328, 330, 332, 336, 351, 357, 369, 370, 373, 374, 375, 378, 379, 383, 389, 391, 402, 413, 415, 417, 418, 422, 425, 431, 432, 433, 435, 437, 438, 439, 441, 447, 448, 455, 457, 469, 474, 476, 477, 478, 484, 491, 493, 495, 497, 500, 503, 508, 509, 510, 511, 513, 516, 519, 524, 526, 527, 531, 534, 536, 537, 539, 540, 543, 544, 546, 548, 552, 557, 560, 566, 573, 574, 580, 583, 596, 603, 605, 609, 611, 615, 619, 620, 630, 633, 641, 643, 648, 650, 652, 654, 657, 660, 661, 662, 665, 669, 674, 678, 679, 680, 682, 699, 703, 707, 708, 709, 712, 713, 715, 716, 717, 719, 721, 723, 724, 727, 729, 735, 737, 745, 746, 752, 753, 758, 765, 777, 780, 785, 786, 789, 791, 795, 796, 797, 801, 804, 808, 811, 817, 824, 825, 827, 828, 830, 832, 836, 838, 841, 844, 845, 846, 848, 856, 858, 860, 863, 867, 869, 870, 874, 875, 879, 880, 881, 882, 884, 886, 887, 889, 891, 893, 897, 899, 900, 903, 905, 906, 910, 911, 912, 915, 917, 919, 921, 923, 931, 939, 940, 942, 943, 946, 947, 951, 954, 957, 959, 962, 966, 969, 972, 974, 978, 981, 984, 987, 989, 994, 995, 996, 999, 1005, 1006, 1009, 1013, 1014, 1017, 1019, 1021, 1028, 1029, 1030, 1033, 1035, 1037, 1042, 1045, 1049, 1051, 1055, 1059, 1061, 1068, 1073, 1077, 1081, 1084, 1085, 1087, 1088, 1089, 1093, 1099, 1100, 1108, 1109, 1111, 1113, 1114, 1115, 1116, 1117, 1118, 1127, 1133, 1135, 1139, 1146, 1147, 1153, 1155, 1156, 1158, 1167, 1170, 1171, 1173, 1174, 1175, 1178, 1183, 1186, 1187, 1188, 1189, 1191, 1194, 1195, 1202, 1204, 1205, 1206, 1211, 1214, 1216, 1221, 1227, 1230, 1233, 1234, 1235, 1236, 1237, 1239, 1249, 1254, 1255, 1259, 1264, 1276, 1281, 1285, 1290, 1296, 1301, 1305, 1307, 1309, 1310, 1312, 1314, 1321, 1324, 1325, 1333, 1335, 1338,

else 'common'

end as rarity

from avalanche.core.fact_event_logs

where 1=1 and

--tx_hash='0xf1c076b053513b3838fefafe9602307db4a6c4bbb14f270725482cb58ba05499' and

origin_function_signature='0x2d296bf1' AND

price>0 and

event_name is null and

contract_address='0x8927985b358692815e18f2138964679dca5d3b79'

--where contract_address=lower('0x8927985B358692815E18F2138964679DcA5d3b79')

QUALIFY ROW_NUMBER() OVER (PARTITION BY tx_hash ORDER BY event_index desc) = 1

)

select sum(price) as daily_sales, date(block_timestamp) as dates, rarity, count(tokenID) as sales_count

, sum(daily_sales) OVER( partition BY NULL ORDER BY dates ASC rows UNBOUNDED PRECEDING ) "CUMULATIVE SUM"

from chikn

where CURRENT_DATE-dates <90

group by dates, rarity