Database structure

Expected Output

I tried to solve this query by

select * from (select u.user_id,u.email_id,u.password,u.first_name,u.last_name,u.city,u.gender,
 u.phone_no from user_details u inner join ride_users r on u.user_id=r.user_id 
 group by u.user_id,u.email_id,u.password,u.first_name,u.last_name,u.city,u.gender,u.phone_no
 order by count(r.ride_id) desc) where rownum<4;

but it didn't match the test case.

Can anybody help me?

CodePudding user response：

Well, you need to find the USER_IDs of the top 3 ride getters. And each ride has its own RIDE_ID, so you have something to count. You want to get the count of rides for each user, and that will be something like:

select USER_ID, count(RIDE_ID) as cnt
from CARPOOLING.RIDE_USERS
group by USER_ID
order by count(RIDE_ID) desc
limit 3

and that will give you the top 3 users (assuming that there are no ties, and you haven't said what to do if there are, or what your database engine is, or given sample data or any of the things that are listed in this very helpful post: Why should I provide a Minimal Reproducible Example for a very simple SQL query?. Since I don't have any directions on what to do with edge cases, I'm just going to address the simplest general case, which is just taking the top 3 users).

Now, I see your desired output consists of rows from CARPOOLING.USER_DETAILS. So either you use IN and put your first query inside the parentheses, like this:

select *
from CARPOOLING.USER_DETAILS
where USER_ID in (
  select USER_ID, count(RIDE_ID) as cnt
  from CARPOOLING.RIDE_USERS
  group by USER_ID
  order by count(RIDE_ID) desc
  limit 3
)

except IN needs to have only one field returned from the subquery, or it will give you an operand error, so omit the count in the select in the subquery, and just order by the count:

select *
from CARPOOLING.USER_DETAILS
where USER_ID in (
  select USER_ID
  from CARPOOLING.RIDE_USERS
  group by USER_ID
  order by count(RIDE_ID) desc
  limit 3
)

or you can use a join instead of a subquery and only specify the records from USER_DETAILS:

select u.*
from CARPOOLING.RIDE_USERS r
join CARPOOLING.USER_DETAILS u on (r.USER_ID = u.USER_ID)
group by r.USER_ID
order by count(r.RIDE_ID) desc
limit 3

Now, I haven't gone to the trouble to generate any sample data to test these against, so I'm really just whiteboarding. These queries aren't tested, but those are the general ideas. If you edit your question to include some sample data, perhaps even including a fiddle, and define what to do in cases where there are ties, I'll go back and edit my answer to address a real implementation.

CodePudding user response：

The other answer is really well spelled out, and much more elegant. This solution uses the rank function, spelled out as much as I could think of, and is a bit clunker. Just posting it to show there are always multiple ways to go about solving a problem. There is a right answer if you include "query optimization." But that is a bit down the road from answering a question like this.

SELECT
    SRC.USER_ID,
    SRC.EMAIL_ID,
    SRC.PASSWORD,
    SRC.FIRST_NAME,
    SRC.LAST_NAME,
    SRC.CITY,
    SRC.GENDER,
    SRC.PHONE_NO
FROM(
    -- Subquery to get rank
    SELECT
        u.user_id,
        u.email_id,
        u.password,
        u.first_name,
        u.last_name,
        u.city,
        u.gender,
        u.phone_no,
        DENSE_RANK()
            OVER(
                PARTITION BY
                    U.USER_ID
                ORDER BY
                    COUNT_OF_RIDES DESC
            )
                AS "COUNT_OF_RIDES_RNK"
    FROM
            USER_DETAILS U
        JOIN(
            --Subquery to get count of rides by user_id
            SELECT
                R.USER_ID,
                COUNT(R.RIDE_ID)
                    AS "COUNT_OF_RIDES"
            FROM
                RIDER_USERS R
            GROUP BY
                R.USER_ID
            
        ) R
            ON
                R.USER_ID = U.USER_ID
) SRC
WHERE
    SRC.COUNT_OF_RIDES_RNK >= 3
ORDER BY
    SRC.COUNT_OF_RIDES_RNK
;