home / russian-ads

russian-ads

Custom SQL query returning 47 rows

select
    target_id,
    targets.name,
    count(*) as n,
    json_object(
        "href", "/russian-ads/faceted-targets?targets=" || urllib_quote_plus(
            json_insert(:targets, '$[' || json_array_length(:targets) || ']', target_id)
        ),
        "label", json_insert(:targets, '$[' || json_array_length(:targets) || ']', target_id)
    ) as apply_this_facet,
    json_object(
        "href", "/russian-ads/display_ads?_targets_json=" || urllib_quote_plus(
            json_insert(:targets, '$[' || json_array_length(:targets) || ']', target_id)
        ),
        "label", "See " || count(*) || " ads matching " || json_insert(:targets, '$[' || json_array_length(:targets) || ']', target_id)
    ) as browse_these_ads
from ad_targets
join targets on ad_targets.target_id = targets.id
where
    json_array_length(:targets) == 0 or
    ad_id in (
        select ad_id
        from "ad_targets"
        where "ad_targets".target_id in (select value from json_each(:targets))
        group by "ad_targets".ad_id
        having count(distinct "ad_targets".target_id) = json_array_length(:targets)
    )
    and target_id not in (select value from json_each(:targets))
group by
    target_id order by n desc

Query parameters

target_idnamenapply_this_facetbrowse_these_ads
59d75 language:English (US) 111 ['6a38c', '59d75'] See 111 ads matching ["6a38c","59d75"]
b1201 placements:News Feed on mobile devices 111 ['6a38c', 'b1201'] See 111 ads matching ["6a38c","b1201"]
ec3ac location:United States 111 ['6a38c', 'ec3ac'] See 111 ads matching ["6a38c","ec3ac"]
5e075 language:English (UK) 110 ['6a38c', '5e075'] See 110 ads matching ["6a38c","5e075"]
41b26 people_who_match:interests:Black Consciousness Movement 106 ['6a38c', '41b26'] See 106 ads matching ["6a38c","41b26"]
9a8c6 people_who_match:interests:Police misconduct 106 ['6a38c', '9a8c6'] See 106 ads matching ["6a38c","9a8c6"]
e128e people_who_match:interests:Black (Color) 106 ['6a38c', 'e128e'] See 106 ads matching ["6a38c","e128e"]
d6347 people_who_match:interests:Black nationalism 105 ['6a38c', 'd6347'] See 105 ads matching ["6a38c","d6347"]
e6200 placements:News Feed on desktop computers 105 ['6a38c', 'e6200'] See 105 ads matching ["6a38c","e6200"]
2db78 people_who_match:interests:Martin Luther King III 103 ['6a38c', '2db78'] See 103 ads matching ["6a38c","2db78"]
abb72 people_who_match:interests:African-American history 98 ['6a38c', 'abb72'] See 98 ads matching ["6a38c","abb72"]
4801f age:16 - 65+ 94 ['6a38c', '4801f'] See 94 ads matching ["6a38c","4801f"]
ebf60 people_who_match:interests:Pan-Africanism 92 ['6a38c', 'ebf60'] See 92 ads matching ["6a38c","ebf60"]
a1307 people_who_match:interests:African-American Civil Rights Movement (1954—68) 57 ['6a38c', 'a1307'] See 57 ads matching ["6a38c","a1307"]
a2d21 people_who_match:interests:African-American culture 57 ['6a38c', 'a2d21'] See 57 ads matching ["6a38c","a2d21"]
1d2ab people_who_match:interests:African—American Civil Rights Movement (1954—68) 47 ['6a38c', '1d2ab'] See 47 ads matching ["6a38c","1d2ab"]
91693 people_who_match:interests:African—American culture 47 ['6a38c', '91693'] See 47 ads matching ["6a38c","91693"]
13ab6 people_who_match:interests:Pan—Africanism 11 ['6a38c', '13ab6'] See 11 ads matching ["6a38c","13ab6"]
c3b9b age:16 — 65+ 11 ['6a38c', 'c3b9b'] See 11 ads matching ["6a38c","c3b9b"]
4225c people_who_match:interests:African—American history 5 ['6a38c', '4225c'] See 5 ads matching ["6a38c","4225c"]
6b827 excluded_connections:Exclude people who like Don't Shoot 5 ['6a38c', '6b827'] See 5 ads matching ["6a38c","6b827"]
7ee91 people_who_match:interests:Cop Block 5 ['6a38c', '7ee91'] See 5 ads matching ["6a38c","7ee91"]
b825d age:14 - 54 5 ['6a38c', 'b825d'] See 5 ads matching ["6a38c","b825d"]
0deb1 people_who_match:interests:Police brutality in the United States 2 ['6a38c', '0deb1'] See 2 ads matching ["6a38c","0deb1"]
3502d placements:Instagram Feed 2 ['6a38c', '3502d'] See 2 ads matching ["6a38c","3502d"]
3fbed people_who_match:interests:African-Amerioan history 2 ['6a38c', '3fbed'] See 2 ads matching ["6a38c","3fbed"]
487f2 people_who_match:interests:African-Amerioan Civil Rights Movement (1954—68) 2 ['6a38c', '487f2'] See 2 ads matching ["6a38c","487f2"]
a5ab5 people_who_match:interests:African-Amerioan culture 2 ['6a38c', 'a5ab5'] See 2 ads matching ["6a38c","a5ab5"]
b322d people_who_match:interests:Pan-Afrioanism 2 ['6a38c', 'b322d'] See 2 ads matching ["6a38c","b322d"]
cc5ed location_living_in:United States 2 ['6a38c', 'cc5ed'] See 2 ads matching ["6a38c","cc5ed"]
086cd people_who_match:interests:Martin Luther King I” 1 ['6a38c', '086cd'] See 1 ads matching ["6a38c","086cd"]
30a44 people_who_match:interests:Media bias 1 ['6a38c', '30a44'] See 1 ads matching ["6a38c","30a44"]
3c576 people_who_match:interests:Martin Luther King lll 1 ['6a38c', '3c576'] See 1 ads matching ["6a38c","3c576"]
42bf9 people_who_match:interests:Refugees of the Syrian Civil War 1 ['6a38c', '42bf9'] See 1 ads matching ["6a38c","42bf9"]
4698a age:16 - 40 1 ['6a38c', '4698a'] See 1 ads matching ["6a38c","4698a"]
4accc placements:on pages: News Feed on desktop computers 1 ['6a38c', '4accc'] See 1 ads matching ["6a38c","4accc"]
60170 placements:Suggested Video on desktop computers 1 ['6a38c', '60170'] See 1 ads matching ["6a38c","60170"]
66f82 people_who_match:interests:African American 1 ['6a38c', '66f82'] See 1 ads matching ["6a38c","66f82"]
7a9b5 placements:Suggested Video on mobile devices 1 ['6a38c', '7a9b5'] See 1 ads matching ["6a38c","7a9b5"]
9e156 age:13 - 40 1 ['6a38c', '9e156'] See 1 ads matching ["6a38c","9e156"]
9fe47 people_who_match:interests:Police Brutality is a Crime 1 ['6a38c', '9fe47'] See 1 ads matching ["6a38c","9fe47"]
c4c8c people_who_match:interests:Syria-News 1 ['6a38c', 'c4c8c'] See 1 ads matching ["6a38c","c4c8c"]
d1e7b age:13 - 65+ 1 ['6a38c', 'd1e7b'] See 1 ads matching ["6a38c","d1e7b"]
db71d people_who_match:interests:Say It Loud — I'm Black and I'm Proud 1 ['6a38c', 'db71d'] See 1 ads matching ["6a38c","db71d"]
e1f0e people_who_match:interests:National Resistance Movement 1 ['6a38c', 'e1f0e'] See 1 ads matching ["6a38c","e1f0e"]
e4519 people_who_match:interests:Anonymous (group) 1 ['6a38c', 'e4519'] See 1 ads matching ["6a38c","e4519"]
f6c6f people_who_match:interests:Syria Charity 1 ['6a38c', 'f6c6f'] See 1 ads matching ["6a38c","f6c6f"]
Powered by Datasette · Query took 113.671ms