im fetching some data from different tables, merging everything into one table and when i try to insert the data into the main prod table i get an error saying the INSERT statement is incorrect.
<code>WITH all_domains AS (
SELECT 'dom1.com' AS domain UNION ALL
SELECT 'dom2.com' AS domain UNION ALL
SELECT 'dom3.com' AS domain UNION ALL
SELECT 'dom4.com' AS domain UNION ALL
SELECT 'dom5.com' AS domain
),
-- grabbing the latest version of each serp in case of reparse
latest_filtered_rankings AS (
SELECT AS VALUE
ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
FROM dataset.table_1 t
WHERE DATE(requested) BETWEEN '2023-06-01' AND CURRENT_DATE()
GROUP BY keyword_id, requested
),
latest_filtered_rankings_results AS (
SELECT
-- keyword metrics
keyword_id,
requested,
phrase,
device,
location_type,
location,
keyword_groups,
keyword_metrics.adwords_search_volume AS search_volume,
-- result primary key
result.pack_rank AS pack_rank,
result.item_rank AS item_rank,
-- result data
result.title.text as title,
result.url.domain as domain,
result.url.url as url,
-- ranking data
result.page AS page,
result.rank AS ranking_position,
result.paid_adjusted_rank AS ranking_position_incl_paid,
-- calculate organic rank
IF(result.organic.is_organic=TRUE AND result.featured_snippet.is_featured_snippet IS NULL,
DENSE_RANK() OVER (PARTITION BY keyword_id, requested, result.organic.is_organic=TRUE OR result.featured_snippet.is_featured_snippet=TRUE ORDER BY result.rank),
NULL
) AS ranking_position_classic,
-- visibility (recursively checked in Chrome for CSS properties like display:none, opacity, visibility, etc)
result.measurements.is_visible AS is_visible,
-- calculated metrics
result.click_through_rate AS ctr,
result.estimated_traffic AS estimated_traffic,
-- SERP feature or organic
result.featured_snippet.is_featured_snippet,
result.organic.is_organic,
CASE
WHEN result.rank IS NULL THEN null
WHEN result.rank BETWEEN 1 AND 1 THEN '1'
WHEN result.rank BETWEEN 2 AND 3 THEN '2-3'
WHEN result.rank BETWEEN 4 AND 10 THEN '4-10'
WHEN result.rank BETWEEN 11 AND 20 THEN '11-20'
WHEN result.rank BETWEEN 21 AND 50 THEN '21-50'
ELSE 'above 50'
END AS ranking_group
FROM latest_filtered_rankings
JOIN UNNEST(results) AS result
JOIN dataset.table_2 USING (keyword_id)
QUALIFY result.url.domain IN ('dom1.com','dom2.com', 'dom3.com', 'dom4.com', 'dom5.com')
),
distinct_rankings AS (
SELECT
-- keyword metrics
keyword_id,
requested,
domain,
ANY_VALUE(phrase) AS phrase,
ANY_VALUE(device) AS device,
ANY_VALUE(location_type) AS location_type,
ANY_VALUE(location) AS location,
ANY_VALUE(country) AS country,
ANY_VALUE(keyword_metrics.adwords_search_volume) AS search_volume,
ANY_VALUE(keyword_groups) AS keyword_groups,
FROM latest_filtered_rankings lfr
JOIN dataset.table_2 USING (keyword_id)
CROSS JOIN all_domains
GROUP BY keyword_id, requested, all_domains.domain
),
metrics AS (
-- join tables
SELECT
dr.keyword_id,
dr.requested,
dr.phrase,
dr.device,
dr.location_type,
dr.location,
dr.country,
dr.search_volume,
dr.keyword_groups,
lfrr.pack_rank,
lfrr.item_rank,
lfrr.title,
dr.domain,
lfrr.url,
lfrr.page,
lfrr.ranking_position,
lfrr.ranking_position_incl_paid,
lfrr.ranking_position_classic,
lfrr.ranking_group,
lfrr.is_visible,
lfrr.ctr,
lfrr.estimated_traffic,
lfrr.is_featured_snippet,
lfrr.is_organic,
--flag the top organic position (incl. featured snippets)
ROW_NUMBER() OVER (PARTITION BY keyword_id, requested,dr.domain ORDER BY CASE WHEN lfrr.ranking_position IS NOT NULL THEN lfrr.ranking_position ELSE 1e6 END) = 1 AS is_top_ranking
FROM distinct_rankings dr
LEFT JOIN latest_filtered_rankings_results lfrr USING (keyword_id, requested, domain)
),
INSERT INTO `final.table` (
keyword_id,
requested,
phrase,
device,
location_type,
location,
country,
search_volume,
keyword_groups,
pack_rank,
item_rank,
title,
domain,
url,
page,
ranking_position,
ranking_position_incl_paid,
ranking_position_classic,
ranking_group,
is_visible,
ctr,
estimated_traffic,
is_featured_snippet,
is_organic,
is_top_ranking
)
(SELECT
keyword_id,
requested,
phrase,
device,
location_type,
location,
country,
search_volume,
keyword_groups,
pack_rank,
item_rank,
title,
domain,
url,
page,
ranking_position,
ranking_position_incl_paid,
ranking_position_classic,
ranking_group,
is_visible,
ctr,
estimated_traffic,
is_featured_snippet,
is_organic,
is_top_ranking
FROM metrics
WHERE 1 = 1
AND EXTRACT(YEAR FROM requested) = 2024
AND EXTRACT(MONTH FROM requested) = 1)
</code>
<code>WITH all_domains AS (
SELECT 'dom1.com' AS domain UNION ALL
SELECT 'dom2.com' AS domain UNION ALL
SELECT 'dom3.com' AS domain UNION ALL
SELECT 'dom4.com' AS domain UNION ALL
SELECT 'dom5.com' AS domain
),
-- grabbing the latest version of each serp in case of reparse
latest_filtered_rankings AS (
SELECT AS VALUE
ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
FROM dataset.table_1 t
WHERE DATE(requested) BETWEEN '2023-06-01' AND CURRENT_DATE()
GROUP BY keyword_id, requested
),
latest_filtered_rankings_results AS (
SELECT
-- keyword metrics
keyword_id,
requested,
phrase,
device,
location_type,
location,
keyword_groups,
keyword_metrics.adwords_search_volume AS search_volume,
-- result primary key
result.pack_rank AS pack_rank,
result.item_rank AS item_rank,
-- result data
result.title.text as title,
result.url.domain as domain,
result.url.url as url,
-- ranking data
result.page AS page,
result.rank AS ranking_position,
result.paid_adjusted_rank AS ranking_position_incl_paid,
-- calculate organic rank
IF(result.organic.is_organic=TRUE AND result.featured_snippet.is_featured_snippet IS NULL,
DENSE_RANK() OVER (PARTITION BY keyword_id, requested, result.organic.is_organic=TRUE OR result.featured_snippet.is_featured_snippet=TRUE ORDER BY result.rank),
NULL
) AS ranking_position_classic,
-- visibility (recursively checked in Chrome for CSS properties like display:none, opacity, visibility, etc)
result.measurements.is_visible AS is_visible,
-- calculated metrics
result.click_through_rate AS ctr,
result.estimated_traffic AS estimated_traffic,
-- SERP feature or organic
result.featured_snippet.is_featured_snippet,
result.organic.is_organic,
CASE
WHEN result.rank IS NULL THEN null
WHEN result.rank BETWEEN 1 AND 1 THEN '1'
WHEN result.rank BETWEEN 2 AND 3 THEN '2-3'
WHEN result.rank BETWEEN 4 AND 10 THEN '4-10'
WHEN result.rank BETWEEN 11 AND 20 THEN '11-20'
WHEN result.rank BETWEEN 21 AND 50 THEN '21-50'
ELSE 'above 50'
END AS ranking_group
FROM latest_filtered_rankings
JOIN UNNEST(results) AS result
JOIN dataset.table_2 USING (keyword_id)
QUALIFY result.url.domain IN ('dom1.com','dom2.com', 'dom3.com', 'dom4.com', 'dom5.com')
),
distinct_rankings AS (
SELECT
-- keyword metrics
keyword_id,
requested,
domain,
ANY_VALUE(phrase) AS phrase,
ANY_VALUE(device) AS device,
ANY_VALUE(location_type) AS location_type,
ANY_VALUE(location) AS location,
ANY_VALUE(country) AS country,
ANY_VALUE(keyword_metrics.adwords_search_volume) AS search_volume,
ANY_VALUE(keyword_groups) AS keyword_groups,
FROM latest_filtered_rankings lfr
JOIN dataset.table_2 USING (keyword_id)
CROSS JOIN all_domains
GROUP BY keyword_id, requested, all_domains.domain
),
metrics AS (
-- join tables
SELECT
dr.keyword_id,
dr.requested,
dr.phrase,
dr.device,
dr.location_type,
dr.location,
dr.country,
dr.search_volume,
dr.keyword_groups,
lfrr.pack_rank,
lfrr.item_rank,
lfrr.title,
dr.domain,
lfrr.url,
lfrr.page,
lfrr.ranking_position,
lfrr.ranking_position_incl_paid,
lfrr.ranking_position_classic,
lfrr.ranking_group,
lfrr.is_visible,
lfrr.ctr,
lfrr.estimated_traffic,
lfrr.is_featured_snippet,
lfrr.is_organic,
--flag the top organic position (incl. featured snippets)
ROW_NUMBER() OVER (PARTITION BY keyword_id, requested,dr.domain ORDER BY CASE WHEN lfrr.ranking_position IS NOT NULL THEN lfrr.ranking_position ELSE 1e6 END) = 1 AS is_top_ranking
FROM distinct_rankings dr
LEFT JOIN latest_filtered_rankings_results lfrr USING (keyword_id, requested, domain)
),
INSERT INTO `final.table` (
keyword_id,
requested,
phrase,
device,
location_type,
location,
country,
search_volume,
keyword_groups,
pack_rank,
item_rank,
title,
domain,
url,
page,
ranking_position,
ranking_position_incl_paid,
ranking_position_classic,
ranking_group,
is_visible,
ctr,
estimated_traffic,
is_featured_snippet,
is_organic,
is_top_ranking
)
(SELECT
keyword_id,
requested,
phrase,
device,
location_type,
location,
country,
search_volume,
keyword_groups,
pack_rank,
item_rank,
title,
domain,
url,
page,
ranking_position,
ranking_position_incl_paid,
ranking_position_classic,
ranking_group,
is_visible,
ctr,
estimated_traffic,
is_featured_snippet,
is_organic,
is_top_ranking
FROM metrics
WHERE 1 = 1
AND EXTRACT(YEAR FROM requested) = 2024
AND EXTRACT(MONTH FROM requested) = 1)
</code>
WITH all_domains AS (
SELECT 'dom1.com' AS domain UNION ALL
SELECT 'dom2.com' AS domain UNION ALL
SELECT 'dom3.com' AS domain UNION ALL
SELECT 'dom4.com' AS domain UNION ALL
SELECT 'dom5.com' AS domain
),
-- grabbing the latest version of each serp in case of reparse
latest_filtered_rankings AS (
SELECT AS VALUE
ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
FROM dataset.table_1 t
WHERE DATE(requested) BETWEEN '2023-06-01' AND CURRENT_DATE()
GROUP BY keyword_id, requested
),
latest_filtered_rankings_results AS (
SELECT
-- keyword metrics
keyword_id,
requested,
phrase,
device,
location_type,
location,
keyword_groups,
keyword_metrics.adwords_search_volume AS search_volume,
-- result primary key
result.pack_rank AS pack_rank,
result.item_rank AS item_rank,
-- result data
result.title.text as title,
result.url.domain as domain,
result.url.url as url,
-- ranking data
result.page AS page,
result.rank AS ranking_position,
result.paid_adjusted_rank AS ranking_position_incl_paid,
-- calculate organic rank
IF(result.organic.is_organic=TRUE AND result.featured_snippet.is_featured_snippet IS NULL,
DENSE_RANK() OVER (PARTITION BY keyword_id, requested, result.organic.is_organic=TRUE OR result.featured_snippet.is_featured_snippet=TRUE ORDER BY result.rank),
NULL
) AS ranking_position_classic,
-- visibility (recursively checked in Chrome for CSS properties like display:none, opacity, visibility, etc)
result.measurements.is_visible AS is_visible,
-- calculated metrics
result.click_through_rate AS ctr,
result.estimated_traffic AS estimated_traffic,
-- SERP feature or organic
result.featured_snippet.is_featured_snippet,
result.organic.is_organic,
CASE
WHEN result.rank IS NULL THEN null
WHEN result.rank BETWEEN 1 AND 1 THEN '1'
WHEN result.rank BETWEEN 2 AND 3 THEN '2-3'
WHEN result.rank BETWEEN 4 AND 10 THEN '4-10'
WHEN result.rank BETWEEN 11 AND 20 THEN '11-20'
WHEN result.rank BETWEEN 21 AND 50 THEN '21-50'
ELSE 'above 50'
END AS ranking_group
FROM latest_filtered_rankings
JOIN UNNEST(results) AS result
JOIN dataset.table_2 USING (keyword_id)
QUALIFY result.url.domain IN ('dom1.com','dom2.com', 'dom3.com', 'dom4.com', 'dom5.com')
),
distinct_rankings AS (
SELECT
-- keyword metrics
keyword_id,
requested,
domain,
ANY_VALUE(phrase) AS phrase,
ANY_VALUE(device) AS device,
ANY_VALUE(location_type) AS location_type,
ANY_VALUE(location) AS location,
ANY_VALUE(country) AS country,
ANY_VALUE(keyword_metrics.adwords_search_volume) AS search_volume,
ANY_VALUE(keyword_groups) AS keyword_groups,
FROM latest_filtered_rankings lfr
JOIN dataset.table_2 USING (keyword_id)
CROSS JOIN all_domains
GROUP BY keyword_id, requested, all_domains.domain
),
metrics AS (
-- join tables
SELECT
dr.keyword_id,
dr.requested,
dr.phrase,
dr.device,
dr.location_type,
dr.location,
dr.country,
dr.search_volume,
dr.keyword_groups,
lfrr.pack_rank,
lfrr.item_rank,
lfrr.title,
dr.domain,
lfrr.url,
lfrr.page,
lfrr.ranking_position,
lfrr.ranking_position_incl_paid,
lfrr.ranking_position_classic,
lfrr.ranking_group,
lfrr.is_visible,
lfrr.ctr,
lfrr.estimated_traffic,
lfrr.is_featured_snippet,
lfrr.is_organic,
--flag the top organic position (incl. featured snippets)
ROW_NUMBER() OVER (PARTITION BY keyword_id, requested,dr.domain ORDER BY CASE WHEN lfrr.ranking_position IS NOT NULL THEN lfrr.ranking_position ELSE 1e6 END) = 1 AS is_top_ranking
FROM distinct_rankings dr
LEFT JOIN latest_filtered_rankings_results lfrr USING (keyword_id, requested, domain)
),
INSERT INTO `final.table` (
keyword_id,
requested,
phrase,
device,
location_type,
location,
country,
search_volume,
keyword_groups,
pack_rank,
item_rank,
title,
domain,
url,
page,
ranking_position,
ranking_position_incl_paid,
ranking_position_classic,
ranking_group,
is_visible,
ctr,
estimated_traffic,
is_featured_snippet,
is_organic,
is_top_ranking
)
(SELECT
keyword_id,
requested,
phrase,
device,
location_type,
location,
country,
search_volume,
keyword_groups,
pack_rank,
item_rank,
title,
domain,
url,
page,
ranking_position,
ranking_position_incl_paid,
ranking_position_classic,
ranking_group,
is_visible,
ctr,
estimated_traffic,
is_featured_snippet,
is_organic,
is_top_ranking
FROM metrics
WHERE 1 = 1
AND EXTRACT(YEAR FROM requested) = 2024
AND EXTRACT(MONTH FROM requested) = 1)
Can anyone help please?
Errors i get: Syntax error: Expected keyword AS but got keyword INTO at [163:8] (if add a comma before INSERT INTO)
Syntax error: Unexpected keyword INSERT at [163:1] (if i dont add a comma)