Determine which year-month has the highest and lowest value [duplicate]

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; 

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 

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;