Calculating LQ, UQ and IQR in SQL for grouped data

Question

I'm struggling to work out how I would get the upper/lower quartiles and IQR for grouped data in SQL. Say I have some data similar to below:

 ID  Data
 1    21
 1    37
 1    86
 1     1
 1    34
 1    77
 2     5
 2    15
 2    29
 3    12
 3    76
 3    54
 3    10

I would like to be able to get the LQ, UQ and IQR from the Data column for each ID, as below:

   ID     LQ    UQ    IQR
    1  24.25  67.0  42.75
    2  10.00  22.0  12.00
    3  11.50  59.5  48.00

So far everything i've found doesn't look at the grouping bit. I know PERCENTILE_COUNT can be used to find which quartile each value falls in to, but I'm struggling to put it all together

Answer 1

Indeed, you can use PERCENTILE_CONT to get this information. Then you do a simple grouping

SELECT
  ID,
  LQ,
  UQ,
  IQR = UQ - LQ
FROM (
    SELECT
      ID,
      LQ = PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Data) OVER (PARTITION BY ID),
      UQ = PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Data) OVER (PARTITION BY ID)
    FROM YourTable t
) t
GROUP BY ID, LQ, UQ;

db<>fiddle