How to find maximum, latest and average profit in sql-CodePudding

I have a table where i have user_id, item_id , profit_date and profit as a column. what i want is avg profit, maximum profit and latest profit for each user and item. i am able to find max and avg but stuck in finding latest profit because for this i have to use date column. latest profit here means profit on latest date.

i have tried using aggregation but stuck in latest part. either i have to find this latest profit separately and then again join to this table where max and avg profit is found , and joining should be like 1 to 1.

-- Table script

CREATE TABLE IF NOT EXISTS sample (
    `user_id` INT,
    `item_id` INT,
    `profit_date` DATETIME,
    `profit` INT
);
INSERT INTO sample VALUES
    (1,10,'2022-01-01 00:00:00',10),
    (1,10,'2022-01-02 00:00:00',30),
    (1,10,'2022-01-03 00:00:00',20),
    (1,15,'2022-01-04 00:00:00',10),
    (1,15,'2022-01-05 00:00:00',15),
    (1,15,'2022-01-06 00:00:00',20),
    (2,10,'2022-01-07 00:00:00',10),
    (2,10,'2022-01-08 00:00:00',30),
    (2,10,'2022-01-09 00:00:00',20),
    (2,15,'2022-01-10 00:00:00',10),
    (2,15,'2022-01-11 00:00:00',10),
    (2,15,'2022-01-12 00:00:00',7);

--sample input table

user_id	item_id	profit_date	profit
1	10	01-01-2022	10
1	10	02-01-2022	30
1	10	03-01-2022	20
1	15	04-01-2022	10
1	15	05-01-2022	15
1	15	06-01-2022	20
2	10	07-01-2022	10
2	10	08-01-2022	30
2	10	09-01-2022	20
2	15	10-01-2022	10
2	15	11-01-2022	10
2	15	12-01-2022	7

-- expected output

user_id	item_id	max_profit	avg_profit	latest_profit
1	10	30	20	20
1	15	20	15	20
2	10	30	20	20
2	15	10	9	7

CodePudding user response：

You can combine aggregation with a windowed expression to find the most recent value:

with lp as (
  select *,
    First_Value(profit) over(partition by user_id, item_id order by profit_date desc) lp
  from t
)
select user_id, item_id, 
  Max(profit) max_profit,
  Avg(profit) avg_profit,
  Max(lp) latest_profit
from lp
group by user_id, item_id;

See Demo Fiddle