sqlhivepercentilespark-window-functionpercentile-cont

I want ntile(3) within ntile(3) as in subdivision within division by ntile()

I want to create a ntile(3) within an ntile(3). I have the following table:

Customer Total_amt Digital_amt
1  100 45
2 200 150
3 150 23
4 300 100
5 350 350
6 112 10
7 312 15
8 260 160
9 232 150
10 190 132

I want to have 3 divisions of total_amt like low, med and high and within those divisions I further want to have 3 divisions of digital_amt like count of Customers having: 1)low amt low digital activity 2)low amt med digital activity 3)low amt high digital activity 4)med amt low digital activity 5)med amt med digital activity 6) med amt high digital activity .....similarly 3 sub divisions for high amt category

I tried select digital_amt, ntile(3) over(order by digital_amt) as division_digital from Customer where digital_amt<=(select percentile_disc(0.33) within group (order by Total_amt) over() as p_pv from Customer)

it showed error plus it would be very cumbersome to write 3 tables for sub-divisions of low, med and high amt and then have separate tables again to count them.

Can someone tell me some efficient way to do it?

Solution

  • Have you tried nesting the first NTILE in a subquery, and then using the result of that as a partition in your next NTILE? Like,

    SELECT 
  Customer, 
  Total_amt, 
  Digital_amt, 
  TOTAL_NTILE, 
  NTILE(3) OVER(
    PARTITION BY TOTAL_NTILE 
    ORDER BY 
      Digital_Amt
  ) AS DIGITAL_NTILE 
FROM 
  (
    SELECT 
      Customer, 
      Total_amt, 
      Digital_amt, 
      NTILE(3) OVER(
        ORDER BY 
          Total_amt
      ) AS TOTAL_NTILE 
    FROM 
      table
  ) rs