Finding the quarter value in data values using standard sql bigquery

Question

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

could you explain the formula you have used to derive the values 14 for first quarter and 51 for third quarter — George Joseph, Jul 06 '20 at 06:15
What do YOU mean by "standard SQL"? Please tag with the database you are usingn. — Gordon Linoff, Jul 06 '20 at 12:13

Gordon Linoff · Accepted Answer · 2020-07-07T11:29:21.617

1

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.

edited Jul 07 '20 at 11:29

answered Jul 06 '20 at 12:14

Gordon Linoff

1,242,037
58
646
786

@nsw . . . I don't see a table name in your query. That is just a placeholder for your table. – Gordon Linoff Jul 07 '20 at 02:05
any idea why i received Expected end of input but got keyword FROM for the 2nd FROM ? – nsw Jul 07 '20 at 02:18
@nsw . . . No. Your question doesn't even have a database tag. You can set up a db<>fiddle, if you like, but this answers your question. – Gordon Linoff Jul 07 '20 at 11:27

Finding the quarter value in data values using standard sql bigquery

1 Answers1