How to get table with only rows of maximum value in a column partitioned by another column?

Question

I'm trying to get the maximum value of calories consumed per day. I can't figure out how to get a query result which I imagine to just be 1 row for each date, with a column containing the value for the total calories in each day.

The value with red asterisk next to it is the value I want for each day.

with meals_in_day as

(
select *, 
row_number() over (partition by meal_date order by meal_name ) as meals_through_day,
sum(calories) over (partition by meal_date order by meal_name ) as incremental_calories
from calories_data
)

select *
from meals_in_day;

I got so far as summing the calories partitioned by meals each day, but I'm stuck from here. Hoping someone is able to help me reach my desired query result.

Thanks in advance!

Answer 1

To get the maximum value (which would be the total for the day), you can use a subquery that selects the maximum incremental_calories for each day.

WITH meals_in_day AS (
    SELECT
        meal_date,
        SUM(calories) OVER (PARTITION BY meal_date ORDER BY meal_name) AS incremental_calories
    FROM calories_data
)
SELECT
    meal_date,
    MAX(incremental_calories) AS total_calories
FROM meals_in_day
GROUP BY meal_date;

Answer 2

This can be done with one select like this:

    SELECT
        meal_date,
        MAX(SUM(calories)) OVER (PARTITION BY meal_date) AS total_calories
    FROM calories_data
group by meal_date

Hope it helps.