Mysql query for getting monthwise n th highest spending customer

I am using sakila.payment table. Columns: payment_id, customer_id, staff_id, rental_id, amount, payment_date, update_date

I am using this query to get customers spending the highest amount for each month. How can I get the Nth highest spending customer for each month?

select customer_id,`month`,max(total_amount) from
(SELECT customer_id,count(customer_id) as `count`,month(payment_date) as `month`,sum(amount) as total_amount 
FROM sakila.payment
group by month(payment_date),customer_id 
order by `month` asc, `total_amount` desc)t
group by `month`

Answer

Try the following, if you are using MySQL 8.0 then it will work with row_number()

select 
    customer_id,
    month,
    total_amount
from
(
    select 
        customer_id,
        month,
        total_amount,
        row_number() over (partition by month order by total_amount desc) as rnk
    from
    (
        select 
            customer_id,
            count(customer_id) as `count`,
            month(payment_date) as `month`,
            sum(amount) as total_amount,
        from sakila.payment
        group by 
            month(payment_date),
            customer_id 
    ) cal
) mnt

where rnk = 1