-- To get the top 6 regions with high sales (Top-N query) can be obtained
-- as shown below using the syntax 
-- FETCH FIRST 6 ROWS ONLY. 

SELECT
  ROWNUM AS Rank, q_sum.div_id, q_sum.Region, q_sum.Sales, q_sum.regions
FROM
  (SELECT div_id, Region, sales, COUNT(*) OVER() regions
   FROM 
     (SELECT 1 Div_Id, 'North East'    region, 52550 sales FROM DUAL UNION 
      SELECT 1 Div_Id, 'North East'    region, 32970 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North West'    region, 21175 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North Central' region, 20399 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East'    region, 18155 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East'    region, 25400 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South West'    region, 19179 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South Central' region, 37399 sales FROM DUAL)
   ORDER BY Sales DESC
  ) q_sum
FETCH FIRST 6 ROWS ONLY;

SELECT div_id, 
     RANK() OVER(PARTITION BY div_id ORDER BY sales DESC) div_rank,
     region, sales, COUNT(*) OVER() regions
FROM 
     (SELECT 1 Div_Id, 'North East-1'    region, 52550 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North East-2'    region, 32970 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North West-1'    region, 21175 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North West-2'    region, 22175 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North Central'   region, 20399 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East-1'    region, 18155 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East-2'    region, 20399 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South West'      region, 19179 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South Central'   region, 37399 sales FROM DUAL UNION
      SELECT 3 Div_Id, 'Central'         region, 20399 sales FROM DUAL
     )
ORDER BY Sales DESC
;

-- Complete data from above query
-- Output =>


SELECT div_id, 
     RANK() OVER(PARTITION BY div_id ORDER BY sales DESC) div_rank,
     region, sales, COUNT(*) OVER() regions
FROM 
     (SELECT 1 Div_Id, 'North East-1'    region, 52550 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North East-2'    region, 32970 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North West-1'    region, 21175 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North West-2'    region, 22175 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North Central'   region, 20399 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East-1'    region, 18155 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East-2'    region, 20399 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South West'      region, 19179 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South Central'   region, 37399 sales FROM DUAL UNION
      SELECT 3 Div_Id, 'Central'         region, 20399 sales FROM DUAL
     )
ORDER BY Sales DESC
FETCH FIRST 6 ROWS WITH TIES;

-- First 6 rows with ties results in 8 rows since two others have same value (20399) 
-- Output =>


SELECT div_id, 
     RANK() OVER(PARTITION BY div_id ORDER BY sales DESC) div_rank,
     region, sales, COUNT(*) OVER() regions
FROM 
     (SELECT 1 Div_Id, 'North East-1'    region, 52550 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North East-2'    region, 32970 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North West-1'    region, 21175 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North West-2'    region, 22175 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North Central'   region, 20399 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East-1'    region, 18155 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East-2'    region, 20399 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South West'      region, 19179 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South Central'   region, 37399 sales FROM DUAL UNION
      SELECT 3 Div_Id, 'Central'         region, 20399 sales FROM DUAL
     )
ORDER BY Sales DESC
FETCH FIRST 25 PERCENT ROWS ONLY;

-- The top 25% gets rounded to 30% and top three rows are output
-- Output =>