SELECT prod_id, ch_id, COUNT(*) counts,
 MIN(sales) min_sales, MAX(sales) max_sales
FROM 
 (SELECT 11 prod_id, 1 ch_id, 4100 sales FROM DUAL UNION
  SELECT 11 prod_id, 1 ch_id, 3170 sales FROM DUAL UNION
  SELECT 11 prod_id, 1 ch_id, 3790 sales FROM DUAL UNION
  SELECT 11 prod_id, 2 ch_id, 2790 sales FROM DUAL UNION
  SELECT 12 prod_id, 1 ch_id, 5150 sales FROM DUAL UNION
  SELECT 12 prod_id, 2 ch_id, 4720 sales FROM DUAL UNION
  SELECT 14 prod_id, 2 ch_id, 2190 sales FROM DUAL UNION
  SELECT 14 prod_id, 2 ch_id, 7110 sales FROM DUAL UNION
  SELECT 15 prod_id, 2 ch_id, 1910 sales FROM DUAL UNION
  SELECT 15 prod_id, 3 ch_id, 6830 sales FROM DUAL)
GROUP BY prod_id, ch_id
ORDER BY prod_id, ch_id;



-- The output above shows the data analysis using Group By

SELECT prod_id, ch_id, MIN(sales) min_sales,
    NTH_VALUE(MIN(sales), 1)
    OVER (PARTITION BY prod_id
          ORDER BY ch_id
          ROWS BETWEEN 
          UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) nv
FROM 
 (SELECT 11 prod_id, 1 ch_id, 4100 sales FROM DUAL UNION
  SELECT 11 prod_id, 1 ch_id, 3170 sales FROM DUAL UNION
  SELECT 11 prod_id, 1 ch_id, 3790 sales FROM DUAL UNION
  SELECT 11 prod_id, 2 ch_id, 2790 sales FROM DUAL UNION
  SELECT 12 prod_id, 1 ch_id, 5150 sales FROM DUAL UNION
  SELECT 12 prod_id, 2 ch_id, 4720 sales FROM DUAL UNION
  SELECT 14 prod_id, 2 ch_id, 2190 sales FROM DUAL UNION
  SELECT 14 prod_id, 2 ch_id, 7110 sales FROM DUAL UNION
  SELECT 15 prod_id, 2 ch_id, 1910 sales FROM DUAL UNION
  SELECT 15 prod_id, 3 ch_id, 6830 sales FROM DUAL)
GROUP BY prod_id, ch_id;



-- The output above shows the data analysis of
-- NTH_VALUE(MIN(sales), 1)
-- The Nth_Value function picks the MIN value from the 
-- data in the query table for channel_id of 1.
-- For product_id 14, 15 the same computation is
-- performed even if channel_id of 1 does not
-- exist due ORDER BY ch_id

SELECT prod_id, ch_id, MIN(sales) min_sales,
    NTH_VALUE(MIN(sales), 2)
    OVER (PARTITION BY prod_id
          ORDER BY ch_id
          ROWS BETWEEN 
          UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) nv
FROM 
 (SELECT 11 prod_id, 1 ch_id, 4100 sales FROM DUAL UNION
  SELECT 11 prod_id, 1 ch_id, 3170 sales FROM DUAL UNION
  SELECT 11 prod_id, 1 ch_id, 3790 sales FROM DUAL UNION
  SELECT 11 prod_id, 2 ch_id, 2790 sales FROM DUAL UNION
  SELECT 12 prod_id, 1 ch_id, 5150 sales FROM DUAL UNION
  SELECT 12 prod_id, 2 ch_id, 4720 sales FROM DUAL UNION
  SELECT 14 prod_id, 2 ch_id, 2190 sales FROM DUAL UNION
  SELECT 14 prod_id, 2 ch_id, 7110 sales FROM DUAL UNION
  SELECT 15 prod_id, 2 ch_id, 1910 sales FROM DUAL UNION
  SELECT 15 prod_id, 3 ch_id, 6830 sales FROM DUAL)
GROUP BY prod_id, ch_id;



-- The output above shows the data analysis of
-- NTH_VALUE(MIN(sales), 2)
-- The Nth_Value function picks the MIN value from the 
-- data in the query table for channel_id of 2.
-- For product_id 14, there is only single channel_id
-- and hence the output is NULL value.
-- For product_id 15, the computation value is
-- MIN value of channel_id 3 due to the 
-- ORDER BY ch_id