SELECT TOP PERCENT, VaR, Expected Shortfall in MySQL

Question

I would like to achieve SELECT TOP PERCENT in MySQL.

I used Victor Sorokin's idea in Select TOP X (or bottom) percent for numeric values in MySQL, and got the following query:

SELECT x.log AS Login, 
       AVG(x.PROFIT) AS 'Expected Shortfall', 
       MAX(x.PROFIT) AS '40%VaR'
  FROM
  (SELECT t.PROFIT, 
          @counter := @counter +1 AS counter, 
          t.LOGIN AS log 
     FROM (SELECT @counter:=0) initvar, trades AS t
     WHERE t.LOGIN IN (100,101)
     ORDER BY t.PROFIT) AS x
  WHERE x.counter <= (40/100 * @counter)
GROUP BY x.log

Which return the following result:

Login	Expected Shortfall	40%VaR
101	-85	-70

This works when I change WHERE t.LOGIN IN (100,101) to a single value like WHERE t.LOGIN=100. Whereby it will return me values for each login as following:

Login	Expected Shortfall	40%VaR
100	-4.5	-4

Login	Expected Shortfall	40%VaR
101	-95	-90

I'm not really sure what is happening and I was wondering if there is a way to use the query for multiple accounts or there is a better way to solve the issue? Was thinking of a LOOP statement?

I'm currently using MySQL version 5.7.34. Please do not hesitate to let me know if any clarification is needed. Any ideas would be much appreciated!

Edit: To replicate the issue:

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)

The expected output is just like the outputs you would get if you ran the query for 100 and 101 separately:

Expected Output

LOGIN	ES	40%VAR
100	-4.5	-4
101	-95	-90

Expected Output

Answer 1

The reason why the end result was not according to the single value queries was caused by the @row_number assignment. Taking the base query (the subquery) to run alone will return the following results:

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

As you can see, the counter value that was generated using @row_number is giving a running number for all of the data in the table regardless of it's log value. The result below shows the differences with query that using a single log value:

PROFIT	counter	log
-5	1	100
-4	2	100
-3	3	100
-2	4	100
-1	5	100

Here you can see that if using log=100, you'll get a counter (@row_number) generated from 1-5 as opposed to it being generated from 7-11 in the combined log IN (100,101). This is why WHERE x.counter <= (40/100*v.ctr) in the final query only take log=101 because it's the only one matches the condition. What you're looking for is a counter value separated by log. On MySQL 8.0+ (or MariaDB 10.2+) that support window function, this can be done by using ROW_NUMBER(). However, since OP is using an older version, I found a way to emulate the functionality of ROW_NUMBER() accordingly.

This is the final query generated:

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;

And here is the demo fiddle (inclusive of ROW_NUMBER()) on MySQL 8.0+ query.