--- title: "ClickHouse® tips #1: calculating aggregations after a date" excerpt: "Tips and recipes to learn how to make the most of ClickHouse®, curated weekly by the Tinybird team. Part 1." authors: "Javi Santana" categories: "The Data Base" createdOn: "2023-09-25 00:00:00" publishedOn: "2021-01-26 00:00:00" updatedOn: "2025-04-24 00:00:00" status: "published" ---

Imagine you have a table like

And you want to calculate, per day, the sum(amount) of previous and following days.

For example, for the day 2020-01-05 you have to calculate sumIf(amount, ts < '2020-01-05') and sumIf(amount, ts >= '2020-01-05')

I think there are many ways to do it but this works:

Check out this snapshot for a step-by-step explanation of what’s going on here.

I think a way to exploit that values for each day don’t need to be calculated every time for each day using a nice function, arrayCumSum, plus some other array magic.

Check this out for a step-by-step explanation.

I feel there should be an easier way but that’s just a feeling.

‍