CODEWITHSUNDEEP
sqlsubquery
aRad
aRad

Reputation: 109

SQL- calculate ratio and get max ratio with corresponding user and date details

I have a table with user, date and a col each for messages sent and messages received:

enter image description here

I want to get the max of messages_sent/messages_recieved by date and user for that ratio. So this is the output I expect:

Andrew Lean 10/2/2020 10

Andrew Harp 10/1/2020 6

This is my query:

SELECT
ds.date, ds.user_name, max(ds.ratio) from
(select a.user_name, a.date, a.message_sent/ a.message_received as ratio
from messages a
group by a.user_name, a.date) ds
group by ds.date

But the output I get is:

Andrew Lean 10/2/2020 10

Jalinn Kim 10/1/2020 6

In the above output 6 is the correct max ratio for the date grouped but the user is wrong. What am I doing wrong?

Upvotes: 0

Views: 824

Answers (2)

trillion
trillion

Reputation: 1401

Here, I am just calculating the ratio for each column in the first CTE.

In the second part, I am getting the maximum results of the ratio calculated in the first part on date level. This means I am assuming each user will have one row for each date.

The max() function on date level will ensure that we always get the highest ratio on date level.

There could be ties, between the ratios for that we can use ROW_NUMBER' OR RANK()` to set a rank for each row based on the criteria that we would like to pass in case of ties and then filter on the rank generated.

with data as (
select
date,
user_id,
messages_sent / messages_recieved as ratio
from [table name]
)
select 
date,
max(ratio) as higest_ratio_per_date
from data
group by 1,2

Upvotes: 1

Jon Armstrong
Jon Armstrong

Reputation: 4694

With a recent version of most databases, you could do something like this.

This assumes, as in your data, there's one row per user per day. If you have more rows per user per day, you'll need to provide a little more detail about how to combine them or ignore some rows. You could want to SUM them. It's tough to know.

WITH cte AS (
        select a.user_name, a.date
             , a.message_sent / a.message_received AS ratio
             , ROW_NUMBER() OVER (PARTITION BY a.date ORDER BY a.message_sent / a.message_received DESC) as rn
          from messages a
     )
SELECT t.user_name, t.date, t.ratio
  FROM cte AS t
 WHERE t.rn = 1
;

Note: There's no attempt to handle ties, where more than one user has the same ratio. We could use RANK (or other methods) for that, if your database supports it.

Upvotes: 1

Related Questions