home / russian-ads

russian-ads

Custom SQL query returning 63 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
b1201 placements:News Feed on mobile devices 108 ['41b26', 'b1201'] See 108 ads matching ["41b26","b1201"]
59d75 language:English (US) 107 ['41b26', '59d75'] See 107 ads matching ["41b26","59d75"]
d6347 people_who_match:interests:Black nationalism 107 ['41b26', 'd6347'] See 107 ads matching ["41b26","d6347"]
e128e people_who_match:interests:Black (Color) 107 ['41b26', 'e128e'] See 107 ads matching ["41b26","e128e"]
e6200 placements:News Feed on desktop computers 107 ['41b26', 'e6200'] See 107 ads matching ["41b26","e6200"]
ec3ac location:United States 107 ['41b26', 'ec3ac'] See 107 ads matching ["41b26","ec3ac"]
6a38c people_who_match:interests:Stop Police Brutality 106 ['41b26', '6a38c'] See 106 ads matching ["41b26","6a38c"]
5e075 language:English (UK) 105 ['41b26', '5e075'] See 105 ads matching ["41b26","5e075"]
9a8c6 people_who_match:interests:Police misconduct 105 ['41b26', '9a8c6'] See 105 ads matching ["41b26","9a8c6"]
2db78 people_who_match:interests:Martin Luther King III 103 ['41b26', '2db78'] See 103 ads matching ["41b26","2db78"]
abb72 people_who_match:interests:African-American history 99 ['41b26', 'abb72'] See 99 ads matching ["41b26","abb72"]
4801f age:16 - 65+ 94 ['41b26', '4801f'] See 94 ads matching ["41b26","4801f"]
ebf60 people_who_match:interests:Pan-Africanism 92 ['41b26', 'ebf60'] See 92 ads matching ["41b26","ebf60"]
a1307 people_who_match:interests:African-American Civil Rights Movement (1954—68) 59 ['41b26', 'a1307'] See 59 ads matching ["41b26","a1307"]
a2d21 people_who_match:interests:African-American culture 58 ['41b26', 'a2d21'] See 58 ads matching ["41b26","a2d21"]
1d2ab people_who_match:interests:African—American Civil Rights Movement (1954—68) 48 ['41b26', '1d2ab'] See 48 ads matching ["41b26","1d2ab"]
91693 people_who_match:interests:African—American culture 47 ['41b26', '91693'] See 47 ads matching ["41b26","91693"]
13ab6 people_who_match:interests:Pan—Africanism 12 ['41b26', '13ab6'] See 12 ads matching ["41b26","13ab6"]
c3b9b age:16 — 65+ 11 ['41b26', 'c3b9b'] See 11 ads matching ["41b26","c3b9b"]
4225c people_who_match:interests:African—American history 6 ['41b26', '4225c'] See 6 ads matching ["41b26","4225c"]
18a64 people_who_match:interests:Black Power 3 ['41b26', '18a64'] See 3 ads matching ["41b26","18a64"]
27369 people_who_match:interests:Malcolm X 3 ['41b26', '27369'] See 3 ads matching ["41b26","27369"]
2ce65 people_who_match:interests:Black is beautiful 2 ['41b26', '2ce65'] See 2 ads matching ["41b26","2ce65"]
371f0 age:18 - 65+ 2 ['41b26', '371f0'] See 2 ads matching ["41b26","371f0"]
3fbed people_who_match:interests:African-Amerioan history 2 ['41b26', '3fbed'] See 2 ads matching ["41b26","3fbed"]
487f2 people_who_match:interests:African-Amerioan Civil Rights Movement (1954—68) 2 ['41b26', '487f2'] See 2 ads matching ["41b26","487f2"]
862ed people_who_match:interests:Jr. 2 ['41b26', '862ed'] See 2 ads matching ["41b26","862ed"]
8a7ee people_who_match:interests:Martin Luther King 2 ['41b26', '8a7ee'] See 2 ads matching ["41b26","8a7ee"]
a5ab5 people_who_match:interests:African-Amerioan culture 2 ['41b26', 'a5ab5'] See 2 ads matching ["41b26","a5ab5"]
b322d people_who_match:interests:Pan-Afrioanism 2 ['41b26', 'b322d'] See 2 ads matching ["41b26","b322d"]
df1f0 people_who_match:interests:African- American culture 2 ['41b26', 'df1f0'] See 2 ads matching ["41b26","df1f0"]
086cd people_who_match:interests:Martin Luther King I” 1 ['41b26', '086cd'] See 1 ads matching ["41b26","086cd"]
0deb1 people_who_match:interests:Police brutality in the United States 1 ['41b26', '0deb1'] See 1 ads matching ["41b26","0deb1"]
15228 and_must_also_match:interests:Pan-African colours 1 ['41b26', '15228'] See 1 ads matching ["41b26","15228"]
1e076 and_must_also_match:interests:Kemetism 1 ['41b26', '1e076'] See 1 ads matching ["41b26","1e076"]
23e78 and_must_also_match:interests:Kwame Nkrumah 1 ['41b26', '23e78'] See 1 ads matching ["41b26","23e78"]
23f8e and_must_also_match:interests:Afrocentrism 1 ['41b26', '23f8e'] See 1 ads matching ["41b26","23f8e"]
3502d placements:Instagram Feed 1 ['41b26', '3502d'] See 1 ads matching ["41b26","3502d"]
3c576 people_who_match:interests:Martin Luther King lll 1 ['41b26', '3c576'] See 1 ads matching ["41b26","3c576"]
3da9b excluded_connections:Exclude people who like Pan-African roots MOVE 1 ['41b26', '3da9b'] See 1 ads matching ["41b26","3da9b"]
3f884 and_must_also_match:interests:South Africa 1 ['41b26', '3f884'] See 1 ads matching ["41b26","3f884"]
42bf9 people_who_match:interests:Refugees of the Syrian Civil War 1 ['41b26', '42bf9'] See 1 ads matching ["41b26","42bf9"]
4698a age:16 - 40 1 ['41b26', '4698a'] See 1 ads matching ["41b26","4698a"]
49bb7 and_must_also_match:interests:Hotep 1 ['41b26', '49bb7'] See 1 ads matching ["41b26","49bb7"]
4accc placements:on pages: News Feed on desktop computers 1 ['41b26', '4accc'] See 1 ads matching ["41b26","4accc"]
4e97e location_living_in:united_states:Atlanta (+25 mi) Georgia 1 ['41b26', '4e97e'] See 1 ads matching ["41b26","4e97e"]
54d8d and_must_also_match:interests:Nelson Mandela 1 ['41b26', '54d8d'] See 1 ads matching ["41b26","54d8d"]
60170 placements:Suggested Video on desktop computers 1 ['41b26', '60170'] See 1 ads matching ["41b26","60170"]
64dc9 and_must_also_match:interests:Marcus Garvey 1 ['41b26', '64dc9'] See 1 ads matching ["41b26","64dc9"]
66f82 people_who_match:interests:African American 1 ['41b26', '66f82'] See 1 ads matching ["41b26","66f82"]
6a05f people_who_match:interests:New Black Panther Party 1 ['41b26', '6a05f'] See 1 ads matching ["41b26","6a05f"]
6f328 people_who_match:interests:Afrocentrism 1 ['41b26', '6f328'] See 1 ads matching ["41b26","6f328"]
7a9b5 placements:Suggested Video on mobile devices 1 ['41b26', '7a9b5'] See 1 ads matching ["41b26","7a9b5"]
7b150 age:18 — 65+ 1 ['41b26', '7b150'] See 1 ads matching ["41b26","7b150"]
7c38f people_who_match:interests:Black Panther Party 1 ['41b26', '7c38f'] See 1 ads matching ["41b26","7c38f"]
809af people_who_match:interests:Black History Month 1 ['41b26', '809af'] See 1 ads matching ["41b26","809af"]
902e5 placements:Right column on desktop computers 1 ['41b26', '902e5'] See 1 ads matching ["41b26","902e5"]
b5c9f and_must_also_match:interests:Nigeria 1 ['41b26', 'b5c9f'] See 1 ads matching ["41b26","b5c9f"]
c4c8c people_who_match:interests:Syria-News 1 ['41b26', 'c4c8c'] See 1 ads matching ["41b26","c4c8c"]
cc5ed location_living_in:United States 1 ['41b26', 'cc5ed'] See 1 ads matching ["41b26","cc5ed"]
f431c people_who_match:interests:African— American history 1 ['41b26', 'f431c'] See 1 ads matching ["41b26","f431c"]
f6c6f people_who_match:interests:Syria Charity 1 ['41b26', 'f6c6f'] See 1 ads matching ["41b26","f6c6f"]
f788a people_who_match:interests:Black history 1 ['41b26', 'f788a'] See 1 ads matching ["41b26","f788a"]
Powered by Datasette · Query took 62.042ms