CODEWITHSUNDEEP
sqlgoogle-bigquery
Dimo
Dimo

Reputation: 116

BigQuery - Lookup sum for previous month

I need to lookup the sum of clicks for the query for the previous month, for example if my data looks like:

date query clicks
2020-02-01 test 1
2020-02-01 test 2
2020-02-02 test 3
2020-02-02 test 5
2020-03-01 test 5
2020-03-01 test 6
2020-03-02 test 5
2020-03-02 test 6

Im looking to develop a query that will look somthing like:

mmYY query clicks clicks_prev_month
Jan-2020 test 11 -
Feb-2020 test 22 11

ive tied a few window funtions via a sun query to group by month/year/query but i cant seem to get it to work.

Upvotes: 1

Views: 662

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use aggregation with window functions:

select date_trunc(date, month) as yyyymm, query, sum(clicks) as clicks,
       lag(sum(clicks)) over (partition by query order by min(date)) as clicks_prev
from t
group by yyyymm, query;

Upvotes: 2

Related Questions