SQL Bigquery tidbits: Count with join
Situation: You have a table with the ID of the customer and the name. In another table you have the ID of the customers and their offers. You want to see the number of offers each client has got. And by client, you want the names, not the ID.
Example tables could look like this:
with table1 as (
select 1 as t1_id, 'Arnold' as name
union all
select 2, 'Bernie'
union all
select 3, 'Claire'
),
table2 as (
select 1 as t2_id, 'X' as offer
union all
select 1, 'Y'
union all
select 1, 'Z'
union all
select 2, 'Z'
union all
select 2, 'S'
union all
select 3, 'S'
)
And the solution is a simple count, with a join and group by:
select name ,count(offer) as nr_offers
from table1 A
left join table2 B
on A.t1_id = B.t2_id
group by 1
