User rank based on cumulative rows UPDATE:
Full table schema and data: https://www.db-fiddle.com/f/jT3Mfwd1BJ8GrsBxxPDC8M/1

Table structure (some fields are hidden):

+---------+---------+------------+-------------+-------+-------+
| user_id | kind_id | country_id | province_id | value | score |
+---------+---------+------------+-------------+-------+-------+
|       1 |     201 |          1 |           1 |     1 |     5 |
|       1 |     102 |          1 |           1 |    31 |   155 |
|       1 |     201 |          1 |           1 |     6 |    30 |
|       1 |     109 |          1 |           1 |    33 |   165 |
|       2 |     202 |          1 |           4 |    76 |   380 |
|       2 |     201 |          1 |           4 |     5 |    25 |
|       2 |     209 |          1 |           4 |     9 |    45 |
|       2 |     302 |          1 |           4 |     8 |    40 |
|       2 |     201 |          1 |           4 |    14 |    70 |
|       3 |     201 |          1 |           9 |     7 |    35 |
|       3 |     201 |          1 |           9 |     9 |    45 |
|       3 |     201 |          1 |           9 |     2 |    10 |
|       3 |     201 |          1 |           9 |     5 |    25 |
|       3 |     201 |          1 |           9 |     7 |    35 |
|     ... |     ... |        ... |         ... |   ... |   ... |
+---------+---------+------------+-------------+-------+-------+


Rank is calculated based on sum of score of user's activities.
I saw many questions here and on Stackoverflow but my problem is more complex.

I need 3 rank values per user: global, country (for users in same country), province (same as country). For example a rank value for user 1 may be like this {"global": 9, "country": 3, "province": 1}. I do not need rank of all users but for one individual user.

I tried a dozen queries but can't calculate this three with one query. or, at least, three clean and efficient queries.

I will cache the result for some time, but still efficiency is important since the activities table contains many records and will grow as time passes.

How can I do this?


  Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6
I don't think I understand your question, but you may benefit from a rollup. MySQL have there own version of grouping sets that look like:

SELECT country_id
     , province_id
     , user_id
     , SUM(score) AS total_score 
FROM activities 
GROUP BY country_id, province_id, user_id
WITH ROLLUP;


null in user_id column means an aggregation, dito för province_id. I.e.

1   null    null    64390


is the total for country_id = 1