weekly max count record in oracle

table test has column id,created_date,sal and data like below:

ID	create_date	sal
1	20-JAN-2021	5000
2	20-JAN-2021	6000
3	19 -JAN- 2021	4000

we have data like this for each date.

I pick the each day record count using below query:

select count(*) ,create_date from test
group by create_date desc
order by 2 desc;

I want output for max record count for a weekly basis:

count(*)	create_date
500	20 – JAN-2021
600	10 – JAN – 2021
300	1 – JAN – 2021

Please suggest.

like this.

Please

Answer

You can use trunc date to iso week and use two level grouping as follows:

Select max(cnt) as max_cnt, 
       trunc(create_date,'IW') as week_start_date
  From
    (select count(*) as cnt, 
            create_date
       from test
      Group by create_date )
group by trunc(create_date,'IW') 
order by 2 desc;

If you want the date also then you need to use analytical function as dollows:

Select * from
     (select count(*) as cnt, 
            create_date,
            Row_number() over (partition by trunc(create_date,'IW') 
                               order by count(*) desc) as rn
       from test
      Group by create_date) t
Where rn = 1