Non-repeated values in Big Query-CodePudding

I am fairly new to SQL, so this might be an easy solution for most, but I am having an issue with joins in Big Query. I have two tables:

TABLE A

id  name    purchases
1   alex    2
2   jane    7
3   peter   8
4   mario   1
5   luigi   6

TABLE B

id  name    visited
1   alex    jan
2   jane    jan
2   jane    feb
3   peter   jan
3   peter   feb 
3   peter   mar
4   mario   feb
5   luigi   mar

I want my end result to have unique number of purchases per name/id, so the following:

TABLE C

id  name    visited   purchases
1   alex    jan       2
2   jane    jan       7
2   jane    feb       0
3   peter   jan       8
3   peter   feb       0
3   peter   mar       0
4   mario   feb       1
5   luigi   mar       6

However, no matter what joins I perform, I end up with number of purchases per user matched every time, like the following:

id  name    visited  purchases
1   alex    jan      2
2   jane    jan      7
2   jane    feb      7
3   peter   jan      8
3   peter   feb      8
3   peter   mar      8
4   mario   feb      1
5   luigi   mar      6

What would be the query to have Table C from Tables A and B? Thank you.

CodePudding user response：

One method is using row_number()

select b.*, coalesce(a.purchases, 0) purchases
from (
   select *, row_number() over(partition by id order by visited) rn
   from b ) b
left join a on a.id = b.id and b.rn=1

You may wish to decode visited to an ordinal depending on ordering requirements, for example .. order by case visited when 'jan' then 1 when .. end ..