Forum Academy Marketplace Showcase Pricing Features

Group by day and display last value of that day

Hi everyone,

The title of my topic is not really easy to understand so let me try to explain with a simple example.
I have the following data from an API call:

ID, execution_date, value
#A, 2023-02-01 8:00am, 1000
#A, 2023-02-01 18:00am, 900
#A, 2023-02-01 19:00am, 1200
#A, 2023-02-03 12:00am, 1300

The result I need is:
#A, 2023-02-01, 1200
#A, 2023-02-02, 1200
#A, 2023-02-03, 1300

So, first I need to group by day and find the last value of the day (with the max(execution_date)).

#A, 2023-02-01, 1200
#A, 2023-02-03, 1300

Then I need to expand to all days between the min and max date and fill empty values with the previous value.

#A, 2023-02-01, 1200
#A, 2023-02-02, 1200
#A, 2023-02-03, 1300

Do you have any idea how I could handle this? I started with a group by ID and day(execution_date) with a max(execution_date) aggregation but I don’t know how to merge this list with the raw data from the API call.

Thank you all for your help

One approach is to group by a bucket (on the date day) and aggregate by max (on the value). … skim down to the word bucket …

You might need to alter the DB to make it feasible.

I’ve a working example here

but what I am trying to do is group by a text field and find the max value in the grouped text field. (so I add a proxy bucket number for the text field for me to bucket on)

… Bubble doesn’t have a “having” operator.

Hey, thanks for you reply.

The group by day and max(value) will not work because the last value is not always the maximum value of the day (see me example).

I tried group by day and max(execution_date) to catch the execution_date of the last value of the day. Now I need to “merge” this list with the original data with ID = ID_grouped AND execution_date = max_execution date from the grouped list.

So easy to perform with SQL that’s frustrating… I’m stuck.

ok, then max on the date then :slight_smile: