How to find the distribution of players on each individual player's latest rank in SQL

Question

I have a table whose attributes are the following;

user_id	date	start_rank	end_rank
xggghgh	2019-02-02	01	25

I want as my output the following table;

user_id	latest_rank	No_of_players_on_the_latest_rank

I thought that perhaps I'll be able to get the desired output using cte. So, I was able to make a cte using the following code;

WITH cte AS (
    SELECT
        user_id,  
        MAX(date) OVER (PARTITION BY user_id ORDER BY user_id) AS max
    FROM
        table
)

The reason for taking the max(date) is that the latest rank will be on the latest date.

But I don't understand how do I approach the remainder of the problem. Perhaps my approach will not lead me to the output that I desire.

How do I get there?

Answer 1

i use MySQL as redms, as you don't mentioned which you are using Further u assume, that the latest can be determined by the date column, which you said is actually a timestamp

This approach determines first the latest entrys for every user, and in the following select it selects those determined by their row number and in a sub select it count the distinct user_ids, with out the user that has the endpoints

WIth CTE AS(
SELECT `user_id`, `end_rank`, 
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY `date` DESC) rn
FROM table1
)
SELECT `user_id`, `end_rank`
, (SELECT COUNT(DISTINCT user_id) FROM table1 WHERE `end_rank` = c1.`end_rank` AND `user_id` <> c1.`user_id`) No_of_players_on_the_latest_rank
FROM CTE c1 WHERE rn = 1

user_id | end_rank | No_of_players_on_the_latest_rank
:------ | -------: | -------------------------------:
xggghab |       29 |                                1
xggghgh |       25 |                                0

db<>fiddle here