home / russian-ads

russian-ads

Custom SQL query returning 83 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 129 ['1073c', 'b1201'] See 129 ads matching ["1073c","b1201"]
e6200 placements:News Feed on desktop computers 127 ['1073c', 'e6200'] See 127 ads matching ["1073c","e6200"]
313af interests:African-American Civil Rights Movement (1954—68) 87 ['1073c', '313af'] See 87 ads matching ["1073c","313af"]
ebe26 interests:African-American history 80 ['1073c', 'ebe26'] See 80 ads matching ["1073c","ebe26"]
8e7b3 interests:Jr. 72 ['1073c', '8e7b3'] See 72 ads matching ["1073c","8e7b3"]
a0f55 interests:Malcolm X 72 ['1073c', 'a0f55'] See 72 ads matching ["1073c","a0f55"]
d6ade interests:Martin Luther King 72 ['1073c', 'd6ade'] See 72 ads matching ["1073c","d6ade"]
cc5ed location_living_in:United States 71 ['1073c', 'cc5ed'] See 71 ads matching ["1073c","cc5ed"]
371f0 age:18 - 65+ 70 ['1073c', '371f0'] See 70 ads matching ["1073c","371f0"]
ec3ac location:United States 52 ['1073c', 'ec3ac'] See 52 ads matching ["1073c","ec3ac"]
59d75 language:English (US) 49 ['1073c', '59d75'] See 49 ads matching ["1073c","59d75"]
5e075 language:English (UK) 49 ['1073c', '5e075'] See 49 ads matching ["1073c","5e075"]
4801f age:16 - 65+ 45 ['1073c', '4801f'] See 45 ads matching ["1073c","4801f"]
aabe0 interests:Black Consciousness Movement 34 ['1073c', 'aabe0'] See 34 ads matching ["1073c","aabe0"]
96bbd interests:Pan-Africanism 32 ['1073c', '96bbd'] See 32 ads matching ["1073c","96bbd"]
17183 interests:Black nationalism 24 ['1073c', '17183'] See 24 ads matching ["1073c","17183"]
d4179 interests:African-American culture 21 ['1073c', 'd4179'] See 21 ads matching ["1073c","d4179"]
cbcce interests:Anti-discrimination 20 ['1073c', 'cbcce'] See 20 ads matching ["1073c","cbcce"]
87fac interests:Stop Police Brutality 17 ['1073c', '87fac'] See 17 ads matching ["1073c","87fac"]
3e5f6 interests:Martin Luther King III 16 ['1073c', '3e5f6'] See 16 ads matching ["1073c","3e5f6"]
16102 interests:Human rights 14 ['1073c', '16102'] See 14 ads matching ["1073c","16102"]
550f6 interests:Police misconduct 14 ['1073c', '550f6'] See 14 ads matching ["1073c","550f6"]
e9db4 interests:Justice 14 ['1073c', 'e9db4'] See 14 ads matching ["1073c","e9db4"]
2dfbd interests:African-Amerioan Civil Rights Movement (1954—68) 12 ['1073c', '2dfbd'] See 12 ads matching ["1073c","2dfbd"]
4e7f1 interests:African-Amerioan history 11 ['1073c', '4e7f1'] See 11 ads matching ["1073c","4e7f1"]
343d8 interests:African- American culture 10 ['1073c', '343d8'] See 10 ads matching ["1073c","343d8"]
235b1 interests:Pan Africanist Congress of Azania 9 ['1073c', '235b1'] See 9 ads matching ["1073c","235b1"]
2a6ea interests:Violence prevention 9 ['1073c', '2a6ea'] See 9 ads matching ["1073c","2a6ea"]
9d04d interests:African National Congress 9 ['1073c', '9d04d'] See 9 ads matching ["1073c","9d04d"]
c3780 interests:Black is beautiful 9 ['1073c', 'c3780'] See 9 ads matching ["1073c","c3780"]
e0460 interests:African—American Civil Rights Movement (1954—68) 7 ['1073c', 'e0460'] See 7 ads matching ["1073c","e0460"]
e38cb interests:Martin Luther King ”I 7 ['1073c', 'e38cb'] See 7 ads matching ["1073c","e38cb"]
f9cc2 interests:African- American history 7 ['1073c', 'f9cc2'] See 7 ads matching ["1073c","f9cc2"]
0d4bc location:united_states:Washington 6 ['1073c', '0d4bc'] See 6 ads matching ["1073c","0d4bc"]
384b9 location:united_states:Georgia 6 ['1073c', '384b9'] See 6 ads matching ["1073c","384b9"]
49b74 location:united_states:Florida 6 ['1073c', '49b74'] See 6 ads matching ["1073c","49b74"]
57006 location:united_states:New York 6 ['1073c', '57006'] See 6 ads matching ["1073c","57006"]
73122 interests:Freckle 6 ['1073c', '73122'] See 6 ads matching ["1073c","73122"]
8657c location:united_states:Arizona 6 ['1073c', '8657c'] See 6 ads matching ["1073c","8657c"]
8a526 interests:Visual perception 6 ['1073c', '8a526'] See 6 ads matching ["1073c","8a526"]
9e156 age:13 - 40 6 ['1073c', '9e156'] See 6 ads matching ["1073c","9e156"]
9eadd location:united_states:Texas 6 ['1073c', '9eadd'] See 6 ads matching ["1073c","9eadd"]
aa920 location:united_states:Mississippi 6 ['1073c', 'aa920'] See 6 ads matching ["1073c","aa920"]
b733d location:united_states:New Mexico 6 ['1073c', 'b733d'] See 6 ads matching ["1073c","b733d"]
bfd83 location:united_states:Oklahoma 6 ['1073c', 'bfd83'] See 6 ads matching ["1073c","bfd83"]
bffb8 location:united_states:California 6 ['1073c', 'bffb8'] See 6 ads matching ["1073c","bffb8"]
e35f3 interests:Baltimore 6 ['1073c', 'e35f3'] See 6 ads matching ["1073c","e35f3"]
f38f6 interests:Color 6 ['1073c', 'f38f6'] See 6 ads matching ["1073c","f38f6"]
f6566 location:united_states:Alabama 6 ['1073c', 'f6566'] See 6 ads matching ["1073c","f6566"]
1c31d age:18 - 54 5 ['1073c', '1c31d'] See 5 ads matching ["1073c","1c31d"]
1f895 interests:Culture 5 ['1073c', '1f895'] See 5 ads matching ["1073c","1f895"]
8da48 interests:Nelson Mandela 5 ['1073c', '8da48'] See 5 ads matching ["1073c","8da48"]
a5df2 interests:Slavery in the United States 5 ['1073c', 'a5df2'] See 5 ads matching ["1073c","a5df2"]
bca17 interests:Pan-Afrioanism 5 ['1073c', 'bca17'] See 5 ads matching ["1073c","bca17"]
c4200 interests:African—American culture 5 ['1073c', 'c4200'] See 5 ads matching ["1073c","c4200"]
d582c interests:Equal opportunity 5 ['1073c', 'd582c'] See 5 ads matching ["1073c","d582c"]
f6590 interests:Muhammad Ali 5 ['1073c', 'f6590'] See 5 ads matching ["1073c","f6590"]
02db8 interests:African— American history 4 ['1073c', '02db8'] See 4 ads matching ["1073c","02db8"]
6e219 interests:African—American history 4 ['1073c', '6e219'] See 4 ads matching ["1073c","6e219"]
8de7c interests:Angela Davis 4 ['1073c', '8de7c'] See 4 ads matching ["1073c","8de7c"]
9e07c interests:Martin Luther King I” 4 ['1073c', '9e07c'] See 4 ads matching ["1073c","9e07c"]
afc04 interests:Pan—Africanism 4 ['1073c', 'afc04'] See 4 ads matching ["1073c","afc04"]
40c27 interests:Police Brutality is a Crime 3 ['1073c', '40c27'] See 3 ads matching ["1073c","40c27"]
5c628 interests:African culture 3 ['1073c', '5c628'] See 3 ads matching ["1073c","5c628"]
878ce interests:Nigerians 3 ['1073c', '878ce'] See 3 ads matching ["1073c","878ce"]
a06cb interests:Police brutality in the United States 3 ['1073c', 'a06cb'] See 3 ads matching ["1073c","a06cb"]
ade9f interests:End Slavery Now 3 ['1073c', 'ade9f'] See 3 ads matching ["1073c","ade9f"]
c3b9b age:16 — 65+ 3 ['1073c', 'c3b9b'] See 3 ads matching ["1073c","c3b9b"]
d4e6c interests:Melanin 3 ['1073c', 'd4e6c'] See 3 ads matching ["1073c","d4e6c"]
e17ba interests:Malcolm X Memorial Foundation 3 ['1073c', 'e17ba'] See 3 ads matching ["1073c","e17ba"]
e5284 interests:Malcolm X Grassroots Movement 3 ['1073c', 'e5284'] See 3 ads matching ["1073c","e5284"]
4accc placements:on pages: News Feed on desktop computers 2 ['1073c', '4accc'] See 2 ads matching ["1073c","4accc"]
51b28 interests:Black Women Are Beautiful 2 ['1073c', '51b28'] See 2 ads matching ["1073c","51b28"]
a340a placements:Instream Video on desktop computers 2 ['1073c', 'a340a'] See 2 ads matching ["1073c","a340a"]
d003e placements:Instream Video on mobile devices 2 ['1073c', 'd003e'] See 2 ads matching ["1073c","d003e"]
fd7d6 interests:Pan-Africahism 2 ['1073c', 'fd7d6'] See 2 ads matching ["1073c","fd7d6"]
2cd39 interests:Anti- discrimination 1 ['1073c', '2cd39'] See 1 ads matching ["1073c","2cd39"]
32c58 interests:Anti-disorimination 1 ['1073c', '32c58'] See 1 ads matching ["1073c","32c58"]
37b6b interests:HuffPost Black Voices 1 ['1073c', '37b6b'] See 1 ads matching ["1073c","37b6b"]
40ff5 behaviors:African American (US) 1 ['1073c', '40ff5'] See 1 ads matching ["1073c","40ff5"]
5d3ab interests:TV talkshows 1 ['1073c', '5d3ab'] See 1 ads matching ["1073c","5d3ab"]
d461c interests:T- shirt 1 ['1073c', 'd461c'] See 1 ads matching ["1073c","d461c"]
e6034 interests:African— American culture 1 ['1073c', 'e6034'] See 1 ads matching ["1073c","e6034"]
Powered by Datasette · Query took 100.268ms