SELECT q_data.idx, q_data.div_cd, q_data.prod_cd, q_data.Cust_Cd,
COUNT(*) OVER(PARTITION BY q_data.div_cd, q_data.prod_cd, q_data.cust_cd
ORDER BY q_data.div_cd, q_data.prod_cd, q_data.cust_cd) Cnt,
FIRST_VALUE(q_data.prod_cd) OVER (ORDER BY q_data.div_cd
ROWS UNBOUNDED PRECEDING) AS fv_order_,
FIRST_VALUE(q_data.prod_cd) OVER (PARTITION BY q_data.div_cd) AS fv_div_,
FIRST_VALUE(q_data.prod_cd) OVER (PARTITION BY q_data.cust_cd) AS fv_cust,
LAST_VALUE(q_data.prod_cd) OVER (ORDER BY q_data.div_cd
ROWS UNBOUNDED PRECEDING) AS lv_order_,
LAST_VALUE(q_data.prod_cd) OVER (PARTITION BY q_data.div_cd) AS lv_div_,
LAST_VALUE(q_data.prod_cd) OVER (PARTITION BY q_data.cust_cd) AS lv_cust
FROM (SELECT 1 idx, 1001 div_cd, NULL prod_cd, 101 cust_cd FROM DUAL UNION
SELECT 2 idx, 1001 div_cd, 5001 prod_cd, 101 cust_cd FROM DUAL UNION
SELECT 3 idx, 1001 div_cd, 5002 prod_cd, 101 cust_cd FROM DUAL UNION
SELECT 4 idx, 1002 div_cd, NULL prod_cd, 101 cust_cd FROM DUAL UNION
SELECT 5 idx, 1002 div_cd, NULL prod_cd, 101 cust_cd FROM DUAL UNION
SELECT 6 idx, 1003 div_cd, NULL prod_cd, 101 cust_cd FROM DUAL UNION
SELECT 7 idx, 1004 div_cd, NULL prod_cd, 102 cust_cd FROM DUAL UNION
SELECT 8 idx, 1004 div_cd, 5004 prod_cd, 102 cust_cd FROM DUAL) q_data;
//Complete -> Output