I have two tables: person_demographics, person_social_profile, that are linked by column person_id
p_d represents unique persons, p_s_p represents their social network accounts
p_d will only have one entry per person_id, but p_s_p will have many rows per same person_id
I need to get a count of how many people in the db live in germany, extracted from 'country' column in p_d where there also exists a social account in p_s_p from facebook, AND there also exists a social account for twitter.
I have so far
select person_id from person_demographics pd
where pd.country like '%Germany%' or pd.country = 'DE'
for selecting the set of person_id's for users living in Germany, and
select * from person_social_profiles psp where psp.person_id <is in previous results> and (psp.source = 'facebook' or psp.source = 'twitter')
I then have the idea of doing groupbykey on person_id and counting the groups with > 1 entries, to get the count of unique users that live in germany and have both facebook and twitter, but am having trouble chaining it all together in one query. Any suggestions would be much appreciated, thanks.
I would suggest two levels of aggregation:
select count(*)
from (select pd.person_id
from person_demographics pd join
person_social_profiles psp
on psp.person_id = pd.pser_id
where (pd.country like '%Germany%' or pd.country = 'DE') and
psp.source in ('facebook', 'twitter')
group by pd.person_id
having count(distinct psp.source) = 2
) pd;