SQL for generating a basic rankings report in Data Studio with data from your Nozzle workspace

gistfile1.txt

--Top Ranking URLs Report for Nozzle.io
--Data Studio Template can be found here: https://datastudio.google.com/u/1/reporting/359d4414-0cd6-4da1-8df5-2c6908e0ddec/page/pyxcB

WITH
-- find the latest versioned keyword data
-- this can also be used to pin a query to an older version, good for static reports
latest_keyword_source_versions AS (
 SELECT keyword_source_id, MAX(keyword_source_version_id) AS keyword_source_version_id
 FROM nozzledata.nozzle_nozzleofficial.keywords
 WHERE keyword_source_id=930701976723823
 GROUP BY keyword_source_id
),

-- filter keyword groups first to avoid accidentally aggregating
-- on multiple rows based on keyword groupings
filtered_keywords AS (
 SELECT
 keyword_id,
 ANY_VALUE(phrase_id) AS phrase_id,
 ANY_VALUE(phrase) AS phrase,
 ANY_VALUE(locale_id) AS locale_id,
 ANY_VALUE(device) AS device,
 ANY_VALUE(engine) AS engine,
 ANY_VALUE(language) AS language,
 ANY_VALUE(location_id) AS location_id,
 ANY_VALUE(location_type) AS location_type,
 ANY_VALUE(location) AS location,
 ANY_VALUE(country) AS country,
 ANY_VALUE(ad_words_criteria_id) AS ad_words_criteria_id,
 ANY_VALUE(keywords.groups) AS keyword_groups,
 FROM nozzledata.nozzle_nozzleofficial.keywords
 JOIN latest_keyword_source_versions USING (keyword_source_id, keyword_source_version_id)
 GROUP BY keyword_id
),

-- filter rankings by keyword ids + search level fields
latest_filtered_rankings AS (
 SELECT AS VALUE
 ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
 FROM nozzledata.nozzle_nozzleofficial.rankings t
 JOIN filtered_keywords USING (keyword_id)
 WHERE requested >= '2021-08-01' AND requested <= '2021-12-31'
 GROUP BY ranking_id
),

-- apply any result level filters here, set group by, and get all metrics necessary for later calculations
latest_filtered_rankings_results AS (
 SELECT
 keyword_id,
 requested,
 
 keyword_metrics.adwords_search_volume AS search_volume,

 FIRST_VALUE(result.url.url) OVER (PARTITION BY keyword_id, requested ORDER BY rank, item_rank) AS ranking_url,
 result.url.url_id,
 result.rank,
 result.item_rank,

 result.paid_adjusted_rank,
 result.nozzle_metrics.click_through_rate,
 result.nozzle_metrics.estimated_traffic,
 result.nozzle_metrics.ppc_value,
 result.measurements.pixels_from_top,
 result.measurements.pixel_height * result.measurements.pixel_width AS result_pixels_total,
 result.measurements.percentage_of_viewport,
 result.measurements.percentage_of_dom,
 FROM latest_filtered_rankings
 JOIN filtered_keywords USING (keyword_id)
 JOIN UNNEST(results) AS result
 
 WHERE (result.paid IS NULL OR result.paid.is_paid=FALSE)
 AND (result.url.domain='nozzle.io')
),

-- this doesn't fill null on days where there was no keyword data
distinct_keyword_ids_per_requested AS (
 SELECT
 keyword_id,
 requested,
 FROM latest_filtered_rankings_results
 GROUP BY keyword_id, requested
),

distinct_group_bys AS (
 SELECT
 keyword_id,
 FROM latest_filtered_rankings_results
 GROUP BY keyword_id
),



-- for accurate calculations, we have to fill all requested periods for all keywords
fill_nulls AS (
 SELECT
 keyword_id,
 requested,
 
 FROM distinct_keyword_ids_per_requested
 
 
),

-- calculate min metrics per keyword_id, group_by, and requested
per_serp_metrics AS (
 SELECT
 requested,
 keyword_id,
 ANY_VALUE(ranking_url) AS ranking_url,

 ANY_VALUE(search_volume) AS search_volume,

 MIN(rank) AS top_rank_ignore_nonranking,
 IFNULL(MIN(rank), 101) AS top_rank,

 MIN(paid_adjusted_rank) AS top_paid_adjusted_rank_ignore_nonranking,
 IFNULL(MIN(paid_adjusted_rank), 101) AS top_paid_adjusted_rank,

 MIN(pixels_from_top) AS top_pixels_from_top_ignore_nonranking,
 IFNULL(MIN(pixels_from_top), 30000) AS top_pixels_from_top,

 SUM(click_through_rate) AS click_through_rate_ignore_nonranking,
 IFNULL(SUM(click_through_rate), 0) AS click_through_rate,

 SUM(percentage_of_viewport) AS percentage_of_viewport_ignore_nonranking,
 IFNULL(SUM(percentage_of_viewport), 0) AS percentage_of_viewport,

 SUM(percentage_of_dom) AS percentage_of_dom_ignore_nonranking,
 IFNULL(SUM(percentage_of_dom), 0) AS percentage_of_dom,


 HLL_COUNT.INIT(url_id, 24) AS unique_urls__count__total,
 HLL_COUNT.INIT(IF(rank > 0 AND rank <= 3, url_id, NULL), 24) AS unique_urls__count__rank_group_1_3,
 HLL_COUNT.INIT(IF(rank = 1, url_id, NULL), 24) AS unique_urls__count__rank_group_1,
 HLL_COUNT.INIT(IF(rank = 2, url_id, NULL), 24) AS unique_urls__count__rank_group_2,
 HLL_COUNT.INIT(IF(rank = 3, url_id, NULL), 24) AS unique_urls__count__rank_group_3,
 HLL_COUNT.INIT(IF(rank > 3 AND rank <= 6, url_id, NULL), 24) AS unique_urls__count__rank_group_4_6,
 HLL_COUNT.INIT(IF(rank > 6 AND rank <= 10, url_id, NULL), 24) AS unique_urls__count__rank_group_7_10,
 HLL_COUNT.INIT(IF(rank > 11 AND rank <= 15, url_id, NULL), 24) AS unique_urls__count__rank_group_11_15,
 HLL_COUNT.INIT(IF(rank > 16 AND rank <= 20, url_id, NULL), 24) AS unique_urls__count__rank_group_16_20,
 HLL_COUNT.INIT(IF(rank > 21 AND rank <= 50, url_id, NULL), 24) AS unique_urls__count__rank_group_21_50,
 HLL_COUNT.INIT(IF(rank > 51, url_id, NULL), 24) AS unique_urls__count__rank_group_51,


 HLL_COUNT.INIT(IF(rank IS NOT NULL, keyword_id, NULL), 24) AS unique_keywords__count__total,
 HLL_COUNT.INIT(IF(rank > 0 AND rank <= 3, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_1_3,
 HLL_COUNT.INIT(IF(rank = 1, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_1,
 HLL_COUNT.INIT(IF(rank = 2, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_2,
 HLL_COUNT.INIT(IF(rank = 3, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_3,
 HLL_COUNT.INIT(IF(rank > 3 AND rank <= 6, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_4_6,
 HLL_COUNT.INIT(IF(rank > 6 AND rank <= 10, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_7_10,
 HLL_COUNT.INIT(IF(rank > 11 AND rank <= 15, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_11_15,
 HLL_COUNT.INIT(IF(rank > 16 AND rank <= 20, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_16_20,
 HLL_COUNT.INIT(IF(rank > 21 AND rank <= 50, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_21_50,
 HLL_COUNT.INIT(IF(rank > 51, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_51,


 COUNTIF(rank IS NOT NULL) AS unique_results__count__total,
 SUM(IF(rank > 0 AND rank <= 3, 1, 0)) AS unique_results__count__rank_group_1_3,
 SUM(IF(rank = 1, 1, 0)) AS unique_results__count__rank_group_1,
 SUM(IF(rank = 2, 1, 0)) AS unique_results__count__rank_group_2,
 SUM(IF(rank = 3, 1, 0)) AS unique_results__count__rank_group_3,
 SUM(IF(rank > 3 AND rank <= 6, 1, 0)) AS unique_results__count__rank_group_4_6,
 SUM(IF(rank > 6 AND rank <= 10, 1, 0)) AS unique_results__count__rank_group_7_10,
 SUM(IF(rank > 11 AND rank <= 15, 1, 0)) AS unique_results__count__rank_group_11_15,
 SUM(IF(rank > 16 AND rank <= 20, 1, 0)) AS unique_results__count__rank_group_16_20,
 SUM(IF(rank > 21 AND rank <= 50, 1, 0)) AS unique_results__count__rank_group_21_50,
 SUM(IF(rank > 51, 1, 0)) AS unique_results__count__rank_group_51,


 SUM(estimated_traffic) AS estimated_traffic__sum__total,
 SUM(IF(rank > 0 AND rank <= 3, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_1_3,
 SUM(IF(rank = 1, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_1,
 SUM(IF(rank = 2, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_2,
 SUM(IF(rank = 3, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_3,
 SUM(IF(rank > 3 AND rank <= 6, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_4_6,
 SUM(IF(rank > 6 AND rank <= 10, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_7_10,
 SUM(IF(rank > 11 AND rank <= 15, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_11_15,
 SUM(IF(rank > 16 AND rank <= 20, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_16_20,
 SUM(IF(rank > 21 AND rank <= 50, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_21_50,
 SUM(IF(rank > 51, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_51,


 SUM(ppc_value) AS ppc_value__sum__total,
 SUM(IF(rank > 0 AND rank <= 3, ppc_value, 0)) AS ppc_value__sum__rank_group_1_3,
 SUM(IF(rank = 1, ppc_value, 0)) AS ppc_value__sum__rank_group_1,
 SUM(IF(rank = 2, ppc_value, 0)) AS ppc_value__sum__rank_group_2,
 SUM(IF(rank = 3, ppc_value, 0)) AS ppc_value__sum__rank_group_3,
 SUM(IF(rank > 3 AND rank <= 6, ppc_value, 0)) AS ppc_value__sum__rank_group_4_6,
 SUM(IF(rank > 6 AND rank <= 10, ppc_value, 0)) AS ppc_value__sum__rank_group_7_10,
 SUM(IF(rank > 11 AND rank <= 15, ppc_value, 0)) AS ppc_value__sum__rank_group_11_15,
 SUM(IF(rank > 16 AND rank <= 20, ppc_value, 0)) AS ppc_value__sum__rank_group_16_20,
 SUM(IF(rank > 21 AND rank <= 50, ppc_value, 0)) AS ppc_value__sum__rank_group_21_50,
 SUM(IF(rank > 51, ppc_value, 0)) AS ppc_value__sum__rank_group_51,


 FROM latest_filtered_rankings_results
 RIGHT JOIN fill_nulls n USING (keyword_id, requested)
 GROUP BY keyword_id, requested
),

-- get the average of all the minimum values
per_serp_metrics_aggregation AS (
 SELECT
 requested,
 keyword_id,
 ANY_VALUE(ranking_url) AS ranking_url,

 ANY_VALUE(search_volume) AS search_volume,


 APPROX_QUANTILES(top_rank_ignore_nonranking, 4) AS top_rank_ignore_nonranking__quantiles,
 AVG(top_rank_ignore_nonranking) AS top_rank_ignore_nonranking__avg__total,
 APPROX_QUANTILES(top_rank, 4) AS top_rank__quantiles,
 AVG(top_rank) AS top_rank__avg__total,

 APPROX_QUANTILES(top_paid_adjusted_rank_ignore_nonranking, 4) AS top_paid_adjusted_rank_ignore_nonranking__quantiles,
 AVG(top_paid_adjusted_rank_ignore_nonranking) AS top_paid_adjusted_rank_ignore_nonranking__avg__total,
 APPROX_QUANTILES(top_paid_adjusted_rank, 4) AS top_paid_adjusted_rank__quantiles,
 AVG(top_paid_adjusted_rank) AS top_paid_adjusted_rank__avg__total,

 APPROX_QUANTILES(top_pixels_from_top_ignore_nonranking, 4) AS top_pixels_from_top_ignore_nonranking__quantiles,
 AVG(top_pixels_from_top_ignore_nonranking) AS top_pixels_from_top_ignore_nonranking__avg__total,
 APPROX_QUANTILES(top_pixels_from_top, 4) AS top_pixels_from_top__quantiles,
 AVG(top_pixels_from_top) AS top_pixels_from_top__avg__total,

 APPROX_QUANTILES(click_through_rate_ignore_nonranking, 4) AS click_through_rate_ignore_nonranking__quantiles,
 AVG(click_through_rate_ignore_nonranking) AS click_through_rate_ignore_nonranking__avg__total,
 APPROX_QUANTILES(click_through_rate, 4) AS click_through_rate__quantiles,
 AVG(click_through_rate) AS click_through_rate__avg__total,

 APPROX_QUANTILES(percentage_of_viewport_ignore_nonranking, 4) AS percentage_of_viewport_ignore_nonranking__quantiles,
 AVG(percentage_of_viewport_ignore_nonranking) AS percentage_of_viewport_ignore_nonranking__avg__total,
 APPROX_QUANTILES(percentage_of_viewport, 4) AS percentage_of_viewport__quantiles,
 AVG(percentage_of_viewport) AS percentage_of_viewport__avg__total,

 APPROX_QUANTILES(percentage_of_dom_ignore_nonranking, 4) AS percentage_of_dom_ignore_nonranking__quantiles,
 AVG(percentage_of_dom_ignore_nonranking) AS percentage_of_dom_ignore_nonranking__avg__total,
 APPROX_QUANTILES(percentage_of_dom, 4) AS percentage_of_dom__quantiles,
 AVG(percentage_of_dom) AS percentage_of_dom__avg__total,


 HLL_COUNT.MERGE(unique_urls__count__total) AS unique_urls__count__total,
 HLL_COUNT.MERGE(unique_urls__count__rank_group_1_3) AS unique_urls__count__rank_group_1_3,
 HLL_COUNT.MERGE(unique_urls__count__rank_group_1) AS unique_urls__count__rank_group_1,
 HLL_COUNT.MERGE(unique_urls__count__rank_group_2) AS unique_urls__count__rank_group_2,
 HLL_COUNT.MERGE(unique_urls__count__rank_group_3) AS unique_urls__count__rank_group_3,
 HLL_COUNT.MERGE(unique_urls__count__rank_group_4_6) AS unique_urls__count__rank_group_4_6,
 HLL_COUNT.MERGE(unique_urls__count__rank_group_7_10) AS unique_urls__count__rank_group_7_10,
 HLL_COUNT.MERGE(unique_urls__count__rank_group_11_15) AS unique_urls__count__rank_group_11_15,
 HLL_COUNT.MERGE(unique_urls__count__rank_group_16_20) AS unique_urls__count__rank_group_16_20,
 HLL_COUNT.MERGE(unique_urls__count__rank_group_21_50) AS unique_urls__count__rank_group_21_50,
 HLL_COUNT.MERGE(unique_urls__count__rank_group_51) AS unique_urls__count__rank_group_51,

 HLL_COUNT.MERGE(unique_keywords__count__total) AS unique_keywords__count__total,
 HLL_COUNT.MERGE(unique_keywords__count__rank_group_1_3) AS unique_keywords__count__rank_group_1_3,
 HLL_COUNT.MERGE(unique_keywords__count__rank_group_1) AS unique_keywords__count__rank_group_1,
 HLL_COUNT.MERGE(unique_keywords__count__rank_group_2) AS unique_keywords__count__rank_group_2,
 HLL_COUNT.MERGE(unique_keywords__count__rank_group_3) AS unique_keywords__count__rank_group_3,
 HLL_COUNT.MERGE(unique_keywords__count__rank_group_4_6) AS unique_keywords__count__rank_group_4_6,
 HLL_COUNT.MERGE(unique_keywords__count__rank_group_7_10) AS unique_keywords__count__rank_group_7_10,
 HLL_COUNT.MERGE(unique_keywords__count__rank_group_11_15) AS unique_keywords__count__rank_group_11_15,
 HLL_COUNT.MERGE(unique_keywords__count__rank_group_16_20) AS unique_keywords__count__rank_group_16_20,
 HLL_COUNT.MERGE(unique_keywords__count__rank_group_21_50) AS unique_keywords__count__rank_group_21_50,
 HLL_COUNT.MERGE(unique_keywords__count__rank_group_51) AS unique_keywords__count__rank_group_51,

 SUM(unique_results__count__total) AS unique_results__count__total,
 SUM(unique_results__count__rank_group_1_3) AS unique_results__count__rank_group_1_3,
 SUM(unique_results__count__rank_group_1) AS unique_results__count__rank_group_1,
 SUM(unique_results__count__rank_group_2) AS unique_results__count__rank_group_2,
 SUM(unique_results__count__rank_group_3) AS unique_results__count__rank_group_3,
 SUM(unique_results__count__rank_group_4_6) AS unique_results__count__rank_group_4_6,
 SUM(unique_results__count__rank_group_7_10) AS unique_results__count__rank_group_7_10,
 SUM(unique_results__count__rank_group_11_15) AS unique_results__count__rank_group_11_15,
 SUM(unique_results__count__rank_group_16_20) AS unique_results__count__rank_group_16_20,
 SUM(unique_results__count__rank_group_21_50) AS unique_results__count__rank_group_21_50,
 SUM(unique_results__count__rank_group_51) AS unique_results__count__rank_group_51,

 SUM(estimated_traffic__sum__total) AS estimated_traffic__sum__total,
 SUM(estimated_traffic__sum__rank_group_1_3) AS estimated_traffic__sum__rank_group_1_3,
 SUM(estimated_traffic__sum__rank_group_1) AS estimated_traffic__sum__rank_group_1,
 SUM(estimated_traffic__sum__rank_group_2) AS estimated_traffic__sum__rank_group_2,
 SUM(estimated_traffic__sum__rank_group_3) AS estimated_traffic__sum__rank_group_3,
 SUM(estimated_traffic__sum__rank_group_4_6) AS estimated_traffic__sum__rank_group_4_6,
 SUM(estimated_traffic__sum__rank_group_7_10) AS estimated_traffic__sum__rank_group_7_10,
 SUM(estimated_traffic__sum__rank_group_11_15) AS estimated_traffic__sum__rank_group_11_15,
 SUM(estimated_traffic__sum__rank_group_16_20) AS estimated_traffic__sum__rank_group_16_20,
 SUM(estimated_traffic__sum__rank_group_21_50) AS estimated_traffic__sum__rank_group_21_50,
 SUM(estimated_traffic__sum__rank_group_51) AS estimated_traffic__sum__rank_group_51,

 SUM(ppc_value__sum__total) AS ppc_value__sum__total,
 SUM(ppc_value__sum__rank_group_1_3) AS ppc_value__sum__rank_group_1_3,
 SUM(ppc_value__sum__rank_group_1) AS ppc_value__sum__rank_group_1,
 SUM(ppc_value__sum__rank_group_2) AS ppc_value__sum__rank_group_2,
 SUM(ppc_value__sum__rank_group_3) AS ppc_value__sum__rank_group_3,
 SUM(ppc_value__sum__rank_group_4_6) AS ppc_value__sum__rank_group_4_6,
 SUM(ppc_value__sum__rank_group_7_10) AS ppc_value__sum__rank_group_7_10,
 SUM(ppc_value__sum__rank_group_11_15) AS ppc_value__sum__rank_group_11_15,
 SUM(ppc_value__sum__rank_group_16_20) AS ppc_value__sum__rank_group_16_20,
 SUM(ppc_value__sum__rank_group_21_50) AS ppc_value__sum__rank_group_21_50,
 SUM(ppc_value__sum__rank_group_51) AS ppc_value__sum__rank_group_51,

 FROM per_serp_metrics
 GROUP BY requested, keyword_id
),



group_by_totals AS (
 SELECT
 requested,
 keyword_id,

 phrase_id,
 phrase,
 locale_id,
 device,
 engine,
 language,
 location_id,
 location_type,
 location,
 country,
 ad_words_criteria_id,
 keyword_groups,
 
 ranking_url,

 search_volume,
 
top_rank__avg__total AS top_rank__avg__total__value,
top_rank__avg__total - FIRST_VALUE(top_rank__avg__total) OVER (change) AS top_rank__avg__total__change,

 
top_pixels_from_top__avg__total AS top_pixels_from_top__avg__total__value,
top_pixels_from_top__avg__total - FIRST_VALUE(top_pixels_from_top__avg__total) OVER (change) AS top_pixels_from_top__avg__total__change,

 
click_through_rate__avg__total AS click_through_rate__avg__total__value,
click_through_rate__avg__total - FIRST_VALUE(click_through_rate__avg__total) OVER (change) AS click_through_rate__avg__total__change,

 
percentage_of_viewport__avg__total AS percentage_of_viewport__avg__total__value,
percentage_of_viewport__avg__total - FIRST_VALUE(percentage_of_viewport__avg__total) OVER (change) AS percentage_of_viewport__avg__total__change,

 
percentage_of_dom__avg__total AS percentage_of_dom__avg__total__value,
percentage_of_dom__avg__total - FIRST_VALUE(percentage_of_dom__avg__total) OVER (change) AS percentage_of_dom__avg__total__change,

 
estimated_traffic__sum__total AS estimated_traffic__sum__total__value,
estimated_traffic__sum__total - FIRST_VALUE(estimated_traffic__sum__total) OVER (change) AS estimated_traffic__sum__total__change,
estimated_traffic__sum__rank_group_1 AS estimated_traffic__sum__rank_group_1__value,
estimated_traffic__sum__rank_group_1 - FIRST_VALUE(estimated_traffic__sum__rank_group_1) OVER (change) AS estimated_traffic__sum__rank_group_1__change,
estimated_traffic__sum__rank_group_2 AS estimated_traffic__sum__rank_group_2__value,
estimated_traffic__sum__rank_group_2 - FIRST_VALUE(estimated_traffic__sum__rank_group_2) OVER (change) AS estimated_traffic__sum__rank_group_2__change,
estimated_traffic__sum__rank_group_3 AS estimated_traffic__sum__rank_group_3__value,
estimated_traffic__sum__rank_group_3 - FIRST_VALUE(estimated_traffic__sum__rank_group_3) OVER (change) AS estimated_traffic__sum__rank_group_3__change,
estimated_traffic__sum__rank_group_4_6 AS estimated_traffic__sum__rank_group_4_6__value,
estimated_traffic__sum__rank_group_4_6 - FIRST_VALUE(estimated_traffic__sum__rank_group_4_6) OVER (change) AS estimated_traffic__sum__rank_group_4_6__change,
estimated_traffic__sum__rank_group_7_10 AS estimated_traffic__sum__rank_group_7_10__value,
estimated_traffic__sum__rank_group_7_10 - FIRST_VALUE(estimated_traffic__sum__rank_group_7_10) OVER (change) AS estimated_traffic__sum__rank_group_7_10__change,
estimated_traffic__sum__rank_group_11_15 AS estimated_traffic__sum__rank_group_11_15__value,
estimated_traffic__sum__rank_group_11_15 - FIRST_VALUE(estimated_traffic__sum__rank_group_11_15) OVER (change) AS estimated_traffic__sum__rank_group_11_15__change,
estimated_traffic__sum__rank_group_16_20 AS estimated_traffic__sum__rank_group_16_20__value,
estimated_traffic__sum__rank_group_16_20 - FIRST_VALUE(estimated_traffic__sum__rank_group_16_20) OVER (change) AS estimated_traffic__sum__rank_group_16_20__change,
estimated_traffic__sum__rank_group_21_50 AS estimated_traffic__sum__rank_group_21_50__value,
estimated_traffic__sum__rank_group_21_50 - FIRST_VALUE(estimated_traffic__sum__rank_group_21_50) OVER (change) AS estimated_traffic__sum__rank_group_21_50__change,
estimated_traffic__sum__rank_group_51 AS estimated_traffic__sum__rank_group_51__value,
estimated_traffic__sum__rank_group_51 - FIRST_VALUE(estimated_traffic__sum__rank_group_51) OVER (change) AS estimated_traffic__sum__rank_group_51__change,

 
ppc_value__sum__total AS ppc_value__sum__total__value,
ppc_value__sum__total - FIRST_VALUE(ppc_value__sum__total) OVER (change) AS ppc_value__sum__total__change,
ppc_value__sum__rank_group_1 AS ppc_value__sum__rank_group_1__value,
ppc_value__sum__rank_group_1 - FIRST_VALUE(ppc_value__sum__rank_group_1) OVER (change) AS ppc_value__sum__rank_group_1__change,
ppc_value__sum__rank_group_2 AS ppc_value__sum__rank_group_2__value,
ppc_value__sum__rank_group_2 - FIRST_VALUE(ppc_value__sum__rank_group_2) OVER (change) AS ppc_value__sum__rank_group_2__change,
ppc_value__sum__rank_group_3 AS ppc_value__sum__rank_group_3__value,
ppc_value__sum__rank_group_3 - FIRST_VALUE(ppc_value__sum__rank_group_3) OVER (change) AS ppc_value__sum__rank_group_3__change,
ppc_value__sum__rank_group_4_6 AS ppc_value__sum__rank_group_4_6__value,
ppc_value__sum__rank_group_4_6 - FIRST_VALUE(ppc_value__sum__rank_group_4_6) OVER (change) AS ppc_value__sum__rank_group_4_6__change,
ppc_value__sum__rank_group_7_10 AS ppc_value__sum__rank_group_7_10__value,
ppc_value__sum__rank_group_7_10 - FIRST_VALUE(ppc_value__sum__rank_group_7_10) OVER (change) AS ppc_value__sum__rank_group_7_10__change,
ppc_value__sum__rank_group_11_15 AS ppc_value__sum__rank_group_11_15__value,
ppc_value__sum__rank_group_11_15 - FIRST_VALUE(ppc_value__sum__rank_group_11_15) OVER (change) AS ppc_value__sum__rank_group_11_15__change,
ppc_value__sum__rank_group_16_20 AS ppc_value__sum__rank_group_16_20__value,
ppc_value__sum__rank_group_16_20 - FIRST_VALUE(ppc_value__sum__rank_group_16_20) OVER (change) AS ppc_value__sum__rank_group_16_20__change,
ppc_value__sum__rank_group_21_50 AS ppc_value__sum__rank_group_21_50__value,
ppc_value__sum__rank_group_21_50 - FIRST_VALUE(ppc_value__sum__rank_group_21_50) OVER (change) AS ppc_value__sum__rank_group_21_50__change,
ppc_value__sum__rank_group_51 AS ppc_value__sum__rank_group_51__value,
ppc_value__sum__rank_group_51 - FIRST_VALUE(ppc_value__sum__rank_group_51) OVER (change) AS ppc_value__sum__rank_group_51__change,

 
unique_results__count__total AS unique_results__count__total__value,
unique_results__count__total - FIRST_VALUE(unique_results__count__total) OVER (change) AS unique_results__count__total__change,
unique_results__count__rank_group_1 AS unique_results__count__rank_group_1__value,
unique_results__count__rank_group_1 - FIRST_VALUE(unique_results__count__rank_group_1) OVER (change) AS unique_results__count__rank_group_1__change,
unique_results__count__rank_group_2 AS unique_results__count__rank_group_2__value,
unique_results__count__rank_group_2 - FIRST_VALUE(unique_results__count__rank_group_2) OVER (change) AS unique_results__count__rank_group_2__change,
unique_results__count__rank_group_3 AS unique_results__count__rank_group_3__value,
unique_results__count__rank_group_3 - FIRST_VALUE(unique_results__count__rank_group_3) OVER (change) AS unique_results__count__rank_group_3__change,
unique_results__count__rank_group_4_6 AS unique_results__count__rank_group_4_6__value,
unique_results__count__rank_group_4_6 - FIRST_VALUE(unique_results__count__rank_group_4_6) OVER (change) AS unique_results__count__rank_group_4_6__change,
unique_results__count__rank_group_7_10 AS unique_results__count__rank_group_7_10__value,
unique_results__count__rank_group_7_10 - FIRST_VALUE(unique_results__count__rank_group_7_10) OVER (change) AS unique_results__count__rank_group_7_10__change,
unique_results__count__rank_group_11_15 AS unique_results__count__rank_group_11_15__value,
unique_results__count__rank_group_11_15 - FIRST_VALUE(unique_results__count__rank_group_11_15) OVER (change) AS unique_results__count__rank_group_11_15__change,
unique_results__count__rank_group_16_20 AS unique_results__count__rank_group_16_20__value,
unique_results__count__rank_group_16_20 - FIRST_VALUE(unique_results__count__rank_group_16_20) OVER (change) AS unique_results__count__rank_group_16_20__change,
unique_results__count__rank_group_21_50 AS unique_results__count__rank_group_21_50__value,
unique_results__count__rank_group_21_50 - FIRST_VALUE(unique_results__count__rank_group_21_50) OVER (change) AS unique_results__count__rank_group_21_50__change,
unique_results__count__rank_group_51 AS unique_results__count__rank_group_51__value,
unique_results__count__rank_group_51 - FIRST_VALUE(unique_results__count__rank_group_51) OVER (change) AS unique_results__count__rank_group_51__change,

 
unique_keywords__count__total AS unique_keywords__count__total__value,
unique_keywords__count__total - FIRST_VALUE(unique_keywords__count__total) OVER (change) AS unique_keywords__count__total__change,
unique_keywords__count__rank_group_1 AS unique_keywords__count__rank_group_1__value,
unique_keywords__count__rank_group_1 - FIRST_VALUE(unique_keywords__count__rank_group_1) OVER (change) AS unique_keywords__count__rank_group_1__change,
unique_keywords__count__rank_group_2 AS unique_keywords__count__rank_group_2__value,
unique_keywords__count__rank_group_2 - FIRST_VALUE(unique_keywords__count__rank_group_2) OVER (change) AS unique_keywords__count__rank_group_2__change,
unique_keywords__count__rank_group_3 AS unique_keywords__count__rank_group_3__value,
unique_keywords__count__rank_group_3 - FIRST_VALUE(unique_keywords__count__rank_group_3) OVER (change) AS unique_keywords__count__rank_group_3__change,
unique_keywords__count__rank_group_4_6 AS unique_keywords__count__rank_group_4_6__value,
unique_keywords__count__rank_group_4_6 - FIRST_VALUE(unique_keywords__count__rank_group_4_6) OVER (change) AS unique_keywords__count__rank_group_4_6__change,
unique_keywords__count__rank_group_7_10 AS unique_keywords__count__rank_group_7_10__value,
unique_keywords__count__rank_group_7_10 - FIRST_VALUE(unique_keywords__count__rank_group_7_10) OVER (change) AS unique_keywords__count__rank_group_7_10__change,
unique_keywords__count__rank_group_11_15 AS unique_keywords__count__rank_group_11_15__value,
unique_keywords__count__rank_group_11_15 - FIRST_VALUE(unique_keywords__count__rank_group_11_15) OVER (change) AS unique_keywords__count__rank_group_11_15__change,
unique_keywords__count__rank_group_16_20 AS unique_keywords__count__rank_group_16_20__value,
unique_keywords__count__rank_group_16_20 - FIRST_VALUE(unique_keywords__count__rank_group_16_20) OVER (change) AS unique_keywords__count__rank_group_16_20__change,
unique_keywords__count__rank_group_21_50 AS unique_keywords__count__rank_group_21_50__value,
unique_keywords__count__rank_group_21_50 - FIRST_VALUE(unique_keywords__count__rank_group_21_50) OVER (change) AS unique_keywords__count__rank_group_21_50__change,
unique_keywords__count__rank_group_51 AS unique_keywords__count__rank_group_51__value,
unique_keywords__count__rank_group_51 - FIRST_VALUE(unique_keywords__count__rank_group_51) OVER (change) AS unique_keywords__count__rank_group_51__change,

 
unique_urls__count__total AS unique_urls__count__total__value,
unique_urls__count__total - FIRST_VALUE(unique_urls__count__total) OVER (change) AS unique_urls__count__total__change,
unique_urls__count__rank_group_1 AS unique_urls__count__rank_group_1__value,
unique_urls__count__rank_group_1 - FIRST_VALUE(unique_urls__count__rank_group_1) OVER (change) AS unique_urls__count__rank_group_1__change,
unique_urls__count__rank_group_2 AS unique_urls__count__rank_group_2__value,
unique_urls__count__rank_group_2 - FIRST_VALUE(unique_urls__count__rank_group_2) OVER (change) AS unique_urls__count__rank_group_2__change,
unique_urls__count__rank_group_3 AS unique_urls__count__rank_group_3__value,
unique_urls__count__rank_group_3 - FIRST_VALUE(unique_urls__count__rank_group_3) OVER (change) AS unique_urls__count__rank_group_3__change,
unique_urls__count__rank_group_4_6 AS unique_urls__count__rank_group_4_6__value,
unique_urls__count__rank_group_4_6 - FIRST_VALUE(unique_urls__count__rank_group_4_6) OVER (change) AS unique_urls__count__rank_group_4_6__change,
unique_urls__count__rank_group_7_10 AS unique_urls__count__rank_group_7_10__value,
unique_urls__count__rank_group_7_10 - FIRST_VALUE(unique_urls__count__rank_group_7_10) OVER (change) AS unique_urls__count__rank_group_7_10__change,
unique_urls__count__rank_group_11_15 AS unique_urls__count__rank_group_11_15__value,
unique_urls__count__rank_group_11_15 - FIRST_VALUE(unique_urls__count__rank_group_11_15) OVER (change) AS unique_urls__count__rank_group_11_15__change,
unique_urls__count__rank_group_16_20 AS unique_urls__count__rank_group_16_20__value,
unique_urls__count__rank_group_16_20 - FIRST_VALUE(unique_urls__count__rank_group_16_20) OVER (change) AS unique_urls__count__rank_group_16_20__change,
unique_urls__count__rank_group_21_50 AS unique_urls__count__rank_group_21_50__value,
unique_urls__count__rank_group_21_50 - FIRST_VALUE(unique_urls__count__rank_group_21_50) OVER (change) AS unique_urls__count__rank_group_21_50__change,
unique_urls__count__rank_group_51 AS unique_urls__count__rank_group_51__value,
unique_urls__count__rank_group_51 - FIRST_VALUE(unique_urls__count__rank_group_51) OVER (change) AS unique_urls__count__rank_group_51__change,



 FROM per_serp_metrics_aggregation
 JOIN filtered_keywords USING(keyword_id)
 WINDOW change AS (PARTITION BY keyword_id ORDER BY requested)
)


SELECT * FROM group_by_totals