How to create quartiler or percentile

[yshepherd]

Hi,

I am trying to replicate the below sql statement in my LookerML file, but returned an error of

SQL compilation error:

is not a valid group by expression

Original SQL statement

select 
  *,
  ntile(4) over (order by size) AS user_quartile,
  ntile(100) over (order by size) AS user_percentile
from size_calculation
order by size desc

LookerML

  dimension: user_quartile {
    type: number
    hidden: no
    sql: ntile(4) over (order by ${TABLE}.size);;
  }

  dimension: user_percentile {
    type: number
    hidden: no
    sql: ntile(10000) over (order by ${TABLE}.size) ;;
  }

The goal is to visualise size range in each quartile/percentile and use it as a filter for my charts.