[throwaway2037]

This is good post. You had me until this part:

    > So you can do all that, or you can write a few lines of python in Chronon.

It all seems a bit handvwavy here. Will Chronon work as well as the SQL version or be correct? I vote for an LLM tool to help you write those queries. Or is that effectively what Chronon is doing?

[better365]

For correctness, yes, it works as well as the SQL version. And the aggregation can be extensible for other operations easily. For example, we have an operation of last, which is not even available in standard SQL.

[mulmen]

I’ll stop short of calling comparisons to standard SQL disingenuous but it’s definitely unrealistic because no standard SQL implementation exists.

What does this “last” operation do? There’s definitely a LAST_VALUE() window function in the databases I use. It is available in Postgres, Redshift, EMR, Oracle, MySQL, MSSQL, Bigquery, and certainly others I am not aware of.

[better365]

That's fair.

Actually, Last is usually called last_k(n), so that you can specify the number of the values in the result array. For example, if the input column is page_view_id and n = 300, it will return the last 300 page_view_id as an array. If a window is used, for example, 7d, it will truncate the results to the past 7d. The LAST_VALUE() seems to return the last value from an ordered set. Hope that helps. Thanks for your interests.

[mulmen]

In SQL we do that with a RANK window function then apply a filter to that rank. It can also be done with a correlated subquery.