MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE trades (
TICKET int(11) PRIMARY KEY,
LOGIN int(11),
PROFIT double);

INSERT INTO trades (TICKET,LOGIN,PROFIT)
VALUES
(1,100,-5),
(2,100,-4),
(3,100,-3),
(4,100,-2),
(5,100,-1),
(6,101,-100),
(7,101,-90),
(8,101,-80),
(9,101,-70),
(10,101,-60),
(11,101,-50),
(12,101,500);

select * from trades


TICKET
LOGIN
PROFIT


1
100
-5

2
100
-4

3
100
-3

4
100
-2

5
100
-1

6
101
-100

7
101
-90

8
101
-80

9
101
-70

10
101
-60

11
101
-50

12
101
500


SELECT x.log AS Login,
        AVG(x.PROFIT) AS 'Expected Shortfall', 
       MAX(x.PROFIT) AS '40%VaR'
  FROM
  (SELECT t.PROFIT, 
          ROW_NUMBER() OVER (PARTITION BY login ORDER BY profit) AS counter, 
          t.LOGIN AS log 
     FROM trades AS t
     WHERE t.LOGIN IN (100,101)
     ORDER BY t.PROFIT) AS x 
  JOIN (SELECT Login,COUNT(*) ctr FROM trades GROUP BY login) AS v
  ON x.log=v.login
  WHERE x.counter <= (40/100*v.ctr)
  GROUP BY x.log
  ORDER BY x.log;


Login
Expected Shortfall
40%VaR


100
-4.5
-4

101
-95
-90


SELECT x.log AS Login,
        AVG(x.PROFIT) AS 'Expected Shortfall', 
       MAX(x.PROFIT) AS '40%VaR'
  FROM
  (SELECT t.PROFIT,
    @row_number:=CASE
        WHEN @id = LOGIN THEN @row_number + 1
          ELSE 1 END AS counter,
    @id:=LOGIN ID, t.LOGIN AS log 
FROM trades t 
CROSS JOIN (SELECT @id:=0,@row_number:=0) as n
ORDER BY LOGIN) AS x 
  JOIN (SELECT Login,COUNT(*) ctr FROM trades GROUP BY login) AS v
  ON x.log=v.login
  WHERE x.counter <= (40/100*v.ctr)
  GROUP BY x.log
  ORDER BY x.log;


Login
Expected Shortfall
40%VaR


100
-4.5
-4

101
-95
-90

TICKET	LOGIN	PROFIT
1	100	-5
2	100	-4
3	100	-3
4	100	-2
5	100	-1
6	101	-100
7	101	-90
8	101	-80
9	101	-70
10	101	-60
11	101	-50
12	101	500