SQL Max count for each group

I’m having trouble calculating a max count for each value in a column (Year_month). The business question is: for each Year_month, which Week_day has the highest number of records.

Trying to list the Week_day for each Year_month that has the highest record count. I thought I’d create a view to aggregate (count) and then to get the max from these counts for every Year_month, but I have not been able to achieve this. I have had a look at many ‘similar’ posts and tried their code, but none has produced the right answer.

What the Data looks like:

SQL So Far:

CREATE OR REPLACE TEMPORARY VIEW Year_month_agg
AS
SELECT
  Year_month,
  Week_day,
  Count (Week_day) wd_count
FROM 
    temp_view
GROUP BY
    Year_month,
    Week_day

SELECT 
    Year_month,
    Week_day,
    MAX (wd_count) 
FROM 
    Year_month_agg
GROUP BY 
    Year_month,
    Week_day
ORDER BY
    Year_month asc

What I am getting currently

What I’d like to end up with is something like this:

Any help would be most appreciated. Thank you.

Answer

After determining the number of records for each Year_month,Week_day, you may use the ROW_NUMBER function to determine the order by the number of records in each Year_month partition group. We can then filter based on the order to retrieve the top record.

WITH week_day_counts AS (
    SELECT
        Year_month,
        Week_day,
        COUNT(Week_day) wd_count
    FROM 
        temp_view
    GROUP BY
        Year_month,
        Week_day
),
week_days_ranked AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY Year_month ORDER BY wd_count desc) as wd_rank,
        *
    FROM 
        week_day_counts
)
SELECT
    Year_month,
    Week_day,
    wd_count
FROM
    week_days_ranked 
WHERE
    wd_rank=1
ORDER BY
    Year_month asc