Identify the top 2 preferred users in descending order who offered rides

Question

the users in ride_users table are also the ride provider in RIDE table with column ride_provider_id. I have to identify the top two users who have offered maximum ride as ride provider. I have tried this but didn't get desired results:

select distinct  r1.user_id, u.first_name||' '||u.last_name as user_name
from user_details u
inner join ride_users r1 on u.user_id=r1.user_id
inner join ride r2 on r1.ride_id =r2.ride_id
where r1.user_id = r2.ride_provider_id
order by r1.user_id desc;

Answer 1

the inner inline table will give the ride count, which you can use to order by and limit on.

SELECT u.*
FROM user_details u
INNER JOIN (SELECT r.ride_provider_id, count(*) rides
            FROM ride r
            GROUP BY r.ride_provider_id
) mr on mr.ride_provider_id = u.user_id
ORDER BY mr.rides DESC
LIMIT 2