'SQL count recurring values per week

I've asked the same question for pandas: link

And now I'm struggling to do the same thing with Big Query SQL. This is what I'm trying to achieve:

I have a Table containing dates and ids that are grouped by weeks

items_per_week:

date         id    
2022-02-07   1    
             3    
             5    
             4
2022-02-14   2
             1
             3
2022-02-21   9
             10
             1
... 
...
2022-05-16 ....

I want to count for each week how much of the id's are repeating from previous week

For example the desired output for the Table would be:

date        count
2022-02-07  0
2022-02-14  2     # because id 1 and 3 are present in previous week
2022-02-21  1     # because id 1 is present in previous week 
...

I tried grouping the id and counting for each id how many are repeating for each date but it didn't work out as planned.

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