Determine which year-month has the highest and lowest value

Question

Here's my first query to shows the number of customers added per year-month

select count(name) AS CUSTOMER, 
       extract(year from create_date) as yr, 
       extract(month from create_date) as mon
from x
group by extract(year from create_date), 
         extract(month from create_date)
order by yr desc, mon desc;

CUSTOMER	YR	MON
3	2019	07
4	2015	02
100	2014	09
3	2014	04

I tried the query

SELECT MAX(count(*))
FROM x
GROUP BY create_date;

in the results I have;

MAX(COUNT(*))
100

need to see the year and month in the result.

How to do this?

Answer 1

The way I understood the question, you'd use rank analytic function in a subquery (or a CTE) and fetch rows whose count is either minimum or maximum. Something like this:

with temp as
  (select to_char(create_date, 'yyyymm') yyyy_mm,
          count(*) cnt,
          --
          rank() over (order by count(*)  asc) rnk_min,
          rank() over (order by count(*) desc) rnk_max
   from x
   group by to_char(create_date, 'yyyymm')
  )
select yyyy_mm,
       cnt
from temp
where rnk_min = 1
   or rnk_max = 1;

Answer 2

You can use two levels of aggregation and put the results all in one row using keep (which implements a "first" aggregation function):

select max(num_customers) as max_num_customers,
       max(yyyymm) keep (dense_rank first order by num_customers desc) as max_yyyymm,
       min(num_customers) as max_num_customers,
       max(yyyymm) keep (dense_rank first order by num_customers asc) as in_yyyymm,
from (select to_char(create_date, 'YYYY-MM') as yyyymm,
             count(*) AS num_customers
      from x
      group by to_char(create_date, 'YYYY-MM'
     ) ym

Answer 3

From Oracle 12, you can use FETCH FIRST ROW ONLY to get the row with the highest number of customers (and, in the case of ties, the latest date):

SELECT count(name) AS CUSTOMER,
       extract(year from create_date) as yr, 
       extract(month from create_date) as mon
FROM   x
GROUP BY
       extract(year from create_date), 
       extract(month from create_date)
ORDER BY
       customer DESC,
       yr DESC,
       mon DESC
FETCH FIRST ROW ONLY;

If you want to include ties for the highest number of customers then:

SELECT count(name) AS CUSTOMER,
       extract(year from create_date) as yr, 
       extract(month from create_date) as mon
FROM   x
GROUP BY
       extract(year from create_date), 
       extract(month from create_date)
ORDER BY
       customer DESC
FETCH FIRST ROW WITH TIES;