> I'm interested in seeing how well this could be attained in SQLite with window functions[1] since SQLite doesn't seem to support a CREATE AGGREGATE. SQLite3

> I'm interested in seeing how well this could be attained in SQLite with window functions[1] since SQLite doesn't seem to support a CREATE AGGREGATE. SQLite3 very much does support user-defined aggregate functions, just not with a CREATE AGGREGATE statement. You have to use one of the `sqlite3_create_function*()` C functions[0][1][2] to create a user-defined aggregate function: The sixth, seventh and eighth parameters passed to the three "sqlite3_create_function*" functions, xFunc, xStep and xFinal, are pointers to C-language functions that implement the SQL function or aggregate. A scalar SQL function requires an implementation of the xFunc callback only; NULL pointers must be passed as the xStep and xFinal parameters. An aggregate SQL function requires an implementation of xStep and xFinal and NULL pointer must be passed for xFunc. To delete an existing SQL function or aggregate, pass NULL pointers for all three function callbacks. The sixth, seventh, eighth and ninth parameters (xStep, xFinal, xValue and xInverse) passed to sqlite3_create_window_function are pointers to C-language callbacks that implement the new function. xStep and xFinal must both be non-NULL. xValue and xInverse may either both be NULL, in which case a regular aggregate function is created, or must both be non-NULL, in which case the new function may be used as either an aggregate or aggregate window function. More details regarding the implementation of aggregate window functions are available here. > There would also need to be some python[2] to provide the PL/SQL logic shown. Ah, so you do know about the `sqlite3_create_function*()` functions. You don't have to use Python, though you can of course. [0] https://sqlite.org/c3ref/create_function.html [1] https://sqlite.org/appfunc.html#the_aggregate_function_callbacks [2] https://sqlite.org/windowfunctions.html#udfwinfunc*