Bigquery lookup prev month

Question

I need a way to retune a row if the query is not found from the next month when it was found. For example, my Query:

    SELECT
    query,
    date_trunc(report_date, month) as report_month,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    SUM(clicks) / SUM(impressions) AS ctr,
    ROUND(AVG(position),0) AS position,
    ROUND(lag(AVG(position)) over (partition by query order by min(report_date)),0) as position_prev,
    (SUM(clicks) * SUM(impressions))  * (AVG(position) / 1000) AS visibility,
    lag((SUM(clicks) * SUM(impressions))  * (AVG(position) / 1000)) over (partition by query order by min(report_date)) as visibility_prev

FROM
  `table`

WHERE 
    search_type = "web" and
    search_appearance is null and 
    country ="aus" and query = "24 hour electrician fremantle"

GROUP BY
    report_month, query

ORDER BY report_month

which returns

but it would be nice to return another row for June 21 to show status are nill and last month stats as other rows.

Is this possible in BQ without having to create a script?

example rows below:

Answer 1

To not having to change your original query, put it on a with statement and run the following over it. Use the report_month between date(<begin>) and date(<end>)to choose all months you want to return.

-- Your query on a with statement
with table_ as (
    SELECT
    query,
    date_trunc(report_date, month) as report_month,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    SUM(clicks) / SUM(impressions) AS ctr,
    ROUND(AVG(position),0) AS position,
    ROUND(lag(AVG(position)) over (partition by query order by min(report_date)),0) as position_prev,
    (SUM(clicks) * SUM(impressions))  * (AVG(position) / 1000) AS visibility,
    lag((SUM(clicks) * SUM(impressions))  * (AVG(position) / 1000)) over (partition by query order by min(report_date)) as visibility_prev

FROM
  `table`

WHERE 
    search_type = "web" and
    search_appearance is null and 
    country ="aus" and query = "24 hour electrician fremantle"

GROUP BY
    report_month, query

ORDER BY report_month
)
----------------------------------
select 
    if(t0.query is null, "24 hour electrician fremantle", t0.query) as query,
    t1.report_month, 
    t0.clicks, 
    t0.impressions,
    t0.ctr, 
    t0.position,
    (select position from table_ where report_month=date_sub(t1.report_month, interval 1 month)) position_prev,
    t0.visibility,
    (select visibility from table_ where report_month=date_sub(t1.report_month, interval 1 month)) visibility_prev 
from 
    table_ t0
full join 
    (select * from unnest(GENERATE_DATE_ARRAY("2021-01-01", "2021-12-01", INTERVAL 1 MONTH)) AS report_month
    where
    -- Use the following to choose all months you want, to perform to entire year set date("2021-01-01") and date("2021-12-01")
    report_month between date("2021-04-01") and date("2021-06-01")) t1
on
    t0.report_month = t1.report_month
order by 
    report_month

Results:

results with report_month between date("2021-01-01") and date("2021-10-01"):