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

Question

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?

Answer 1

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