By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t (
user_id INT,
item_id INT,
profit_date DATETIME,
profit INT
);
INSERT INTO t 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);
Records: 12 Duplicates: 0 Warnings: 0
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;
| user_id | item_id | max_profit | avg_profit | latest_profit |
|---|---|---|---|---|
| 1 | 10 | 30 | 20.0000 | 20 |
| 1 | 15 | 20 | 15.0000 | 20 |
| 2 | 10 | 30 | 20.0000 | 20 |
| 2 | 15 | 10 | 9.0000 | 7 |