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

Question

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

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?

Answer 1

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.