DNC list queries

1. How many users are on the DNC list

select count(*) from users where is_on_dnc_list = true

7,961,044

2. How many users are on the DNC list that at least one of their follower records are sms unsubscribed

select count(distinct(users.id)) from users 
inner join followers on followers.user_id = users.id
where followers.is_on_dnc_list = true and followers.is_sms_unsubscribed = true

816,707

3. How many users are on the DNC list that received a least one marketing text message in the last 90 days

select count(distinct users.id) as user_count
from users
inner join text_messages on text_messages.user_id = users.id
where users.is_on_dnc_list = true
and text_messages.created_at >= current_date - interval '90 days'

1,540,445

4. A list of restaurants with the number of followers on the DNC list

select 
    r.id as restaurant_id,
    r.slug as restaurant_slug,
    sum(case when f.is_on_dnc_list = true then 1 else 0 end) as dnc_followers,
    round((sum(case when f.is_on_dnc_list = true then 1 else 0 end) * 100.0) / nullif(count(f.id), 0),2) as dnc_percentage_out_of_followers,
    sum(case when f.is_sms_unsubscribed = true then 1 else 0 end) as followers_with_is_sms_unsubscribed,
    round((sum(case when f.is_sms_unsubscribed = true then 1 else 0 end) * 100.0) / nullif(count(f.id), 0),2) as sms_unsubscribed_percentage_out_of_followers,
    count(f.id) as total_followers
from 
    restaurants r
left join 
    followers f on f.restaurant_id = r.id
where 
    r.status = 0
group by 
    r.id, r.slug 
order by 
    dnc_percentage_out_of_followers desc, total_followers desc
    

Example export

"restaurant_id","restaurant_slug","followers_with_is_on_dnc_list","dnc_percentage_out_of_followers", "followers_with_is_sms_unsubscribed", "sms_unsubscribed_percentage_out_of_followers
1,abcdefg,157813,35.27,5938,1.33,447398
2,hijklmn,155385,37.81,11728,2.85,410948

5. Imported vs Organic followers

Total number of our followers on DNC list that were imported vs total number not imported (organically followed)

# Imported
select count(distinct(user_id)) from followers where is_imported is true and is_on_dnc_list = true
3,115,109

# Organic
select count(distinct(user_id)) from followers where is_imported is false and is_on_dnc_list = true
5,710,721