'Bigquery SQL Get the number of installs after every 20000 impressions

I am trying to perform a BigQuery SQL query where for a given "cr" I can get the sum of installs after every 20000 impressions after 58 days. Each row has a "count_install" and "count_impressions" and I am summing over each row. This query below is giving me the total impressions and total installs over 58 days for each "cr"

 SELECT cr_id, 
     SUM(count_imp) as total_imp,
     SUM(count_install) as total_installs  
FROM Table
WHERE utc_date > DATE_SUB(CURRENT_DATE(), INTERVAL 58 DAY)
AND cr_format = "ii"
GROUP BY 1

Which gives me

cr_id | total impressions | total installs
  a   |     44560         |    92
  b   |     25560         |    55

However I would like to know the total number of installs made after every 20000 impressions. Such that the result would look something similar to this below

cr_id | total impressions | total installs
   a  |     20000         |     50        
   a  |     20000         |     34
   a  |     4560          |     8
   b  |     20000         |     44
   b  |     5560          |    11

Can anyone help me figure out the right query to reach the result above?

sqlgoogle-bigquery


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions