Unknown column due to second-level subquery nesting

Question

I want to retrieve a user's rank based on how many points the given user has compared to other users (simply counting users with more points than the given user).

However, with all the queries I have tried, I always end up with Column not found: users.id. From what I can read there is a limit from referencing correlated parent columns more than one level up.

Can I refactor my query, or do I really need to use SET @rownum := 0 style of queries?

SELECT 
    `users`.*, 
    (
        SELECT COUNT(*) + 1 
        FROM (
            SELECT SUM(`amount`) AS `all_points` 
            FROM `points` 
            WHERE `type` = ? 
            GROUP BY `user_id` 
            HAVING `all_points` > (
                SELECT SUM(`amount`) 
                FROM `points` 
                WHERE `type` = ? and `user_id` = `users`.`id`
            )
        ) `points_sub`
    ) as `rank` 
FROM `users` 
WHERE `users`.`id` = ? 
LIMIT 1

Answer 1

You can move your sub clause one level up, Remove having filter and use where filter

SELECT 
    `users`.*, 
    (
        SELECT COUNT(*) + 1 
        FROM (
            SELECT user_id,SUM(`amount`) AS `all_points` 
            FROM `points` 
            WHERE `type` = ? 
            GROUP BY `user_id` 
        ) `points_sub`
        WHERE `all_points` > 
                SELECT SUM(`amount`) 
                FROM `points` 
                WHERE `type` = ? and `user_id` = `users`.`id`

    ) as `rank` 
FROM `users` 
WHERE `users`.`id` = ?
LIMIT 1