How to get the first two entries of each day of the week in MySql 5.5.34

Question

I have a MySQL table with columns TENANT_NAME, MAX_CALLS and TIME_STAMP and no primary key , upon request we insert data every hour and the same name can be repeated.

Now I want to get the group names I need to add and the sum call and get the top 2 entries for each day of the week.

For example: 22:49 Insert data

TENANT_NAME,MAX_CALLS,TIME_STAMP
RS1, 20, 2022-12-07 22:49:17
RS2, 10, 2022-12-07 22:49:17
RS3, 2, 2022-12-07 22:49:17

Next hour 23:49

RS1, 15, 2022-12-07 23:49:17
RS2, 0, 2022-12-07 23:49:17
RS3, 20, 2022-12-07 23:49:17

In this way, I have 1 year of data

Now I want to summarize the name group of 2 records for a week every day

Like this

RS1, 35, MON
RS3, 22, MON... so on
RS4, 40, SUN
RS2, 35, SUN

I tried this query and I was able to group by name and total calls and show DAYNAME but I want the top 2 records for each day of the week.

select a.TENANT_NAME,SUM(a.MAX_CALLS),DAYNAME(a.TIME_STAMP) from TENANT_LIC_DISTRIBUTION AS a group by a.TENANT_NAME,day(a.TIME_STAMP ) order by a.MAX_CALLS,a.TIME_STAMP;

RS1, 35, MON
RS3, 22, MON
RS2, 10, MON

RS3, 30, TUE
RS2, 20, TUE
RS1, 10, TUE....so on
RS1, 20, SUN
RS2, 10, SUN
RS3, 1, SUN

I want to get it like this

RS1, 35, MON
RS3, 22, MON

RS3, 30, TUE
RS2, 20, TUE.... so on

RS1, 20, SUN
RS2, 10, SUN

Please help me

Thank you

Answer 1

Try using Window functions Append row numbers around the aggregate query and then limit by row number. This is one way.

WITH rank_tenant
AS (
    SELECT TENANT_NAME, 
    DAY, 
    CALLS, 
    row_number() OVER (
            PARTITION BY TENANT_NAME 
            ORDER BY CALLS DESC
            ) AS row_num
    FROM (select 
          TENANT_NAME,
          DAYNAME(TIME_STAMP) as DAY,
          SUM(MAX_CALLS) as CALLS
          from TENANT_LIC_DISTRIBUTION
          group by TENANT_NAME, DAY) as t1
    )
SELECT TENANT_NAME,
    DAY,
    CALLS
FROM rank_tenant
WHERE row_num <= 2;