Search code examples
sqlgoogle-bigqueryquartile

Finding the quarter value in data values using standard sql bigquery

I would like to have Q1 and Q3 for the frequency column using standard sql.

Table name : table.frequency

Sample data:

image here

What I did is:

SELECT (ROUND(COUNT(frequency) *0.25)) AS first_quarter,
(ROUND(COUNT(frequency) *0.75)) AS third_quarter
FROM table

And the results are not as I expected:

First quarter = 30577.0 Third quarter = 91730.0

Expected outcome is the 1st and 3rd quarter value for frequency column. Example : First quarter = 14 Third quarter = 51

Solution

  • There are multiple approaches, but a simple one uses ntile():

    select max(case when tile = 1 then frequency end) as q1,
       max(case when tile = 2 then frequency end) as q2,
       max(case when tile = 3 then frequency end) as q3       
from (select t.*, ntile(4) over (order by frequency) as tile
      from t
     ) t;

    There are definitely other approaches, such as percentile() or percentile_cont(). But this is a simple method using standard SQL.

    Here is a db<>fiddle.