home / russian-ads

russian-ads

Custom SQL query returning 62 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 196 ['ebe26', 'b1201'] See 196 ads matching ["ebe26","b1201"]
e6200 placements:News Feed on desktop computers 191 ['ebe26', 'e6200'] See 191 ads matching ["ebe26","e6200"]
313af interests:African-American Civil Rights Movement (1954—68) 178 ['ebe26', '313af'] See 178 ads matching ["ebe26","313af"]
8e7b3 interests:Jr. 163 ['ebe26', '8e7b3'] See 163 ads matching ["ebe26","8e7b3"]
d6ade interests:Martin Luther King 163 ['ebe26', 'd6ade'] See 163 ads matching ["ebe26","d6ade"]
a0f55 interests:Malcolm X 160 ['ebe26', 'a0f55'] See 160 ads matching ["ebe26","a0f55"]
371f0 age:18 - 65+ 150 ['ebe26', '371f0'] See 150 ads matching ["ebe26","371f0"]
cc5ed location_living_in:United States 137 ['ebe26', 'cc5ed'] See 137 ads matching ["ebe26","cc5ed"]
1073c interests:Black (Color) 80 ['ebe26', '1073c'] See 80 ads matching ["ebe26","1073c"]
ec3ac location:United States 44 ['ebe26', 'ec3ac'] See 44 ads matching ["ebe26","ec3ac"]
d4179 interests:African-American culture 33 ['ebe26', 'd4179'] See 33 ads matching ["ebe26","d4179"]
aabe0 interests:Black Consciousness Movement 31 ['ebe26', 'aabe0'] See 31 ads matching ["ebe26","aabe0"]
59d75 language:English (US) 26 ['ebe26', '59d75'] See 26 ads matching ["ebe26","59d75"]
5e075 language:English (UK) 26 ['ebe26', '5e075'] See 26 ads matching ["ebe26","5e075"]
4801f age:16 - 65+ 25 ['ebe26', '4801f'] See 25 ads matching ["ebe26","4801f"]
96bbd interests:Pan-Africanism 20 ['ebe26', '96bbd'] See 20 ads matching ["ebe26","96bbd"]
3502d placements:Instagram Feed 15 ['ebe26', '3502d'] See 15 ads matching ["ebe26","3502d"]
61c1a age:18 - 50 14 ['ebe26', '61c1a'] See 14 ads matching ["ebe26","61c1a"]
04629 interest_expansion:On 12 ['ebe26', '04629'] See 12 ads matching ["ebe26","04629"]
902e5 placements:Right column on desktop computers 12 ['ebe26', '902e5'] See 12 ads matching ["ebe26","902e5"]
cdb88 excluded_connections:Exclude people who like BM 12 ['ebe26', 'cdb88'] See 12 ads matching ["ebe26","cdb88"]
17183 interests:Black nationalism 11 ['ebe26', '17183'] See 11 ads matching ["ebe26","17183"]
27176 interests:Black History Month 9 ['ebe26', '27176'] See 9 ads matching ["ebe26","27176"]
40c27 interests:Police Brutality is a Crime 8 ['ebe26', '40c27'] See 8 ads matching ["ebe26","40c27"]
87fac interests:Stop Police Brutality 8 ['ebe26', '87fac'] See 8 ads matching ["ebe26","87fac"]
a06cb interests:Police brutality in the United States 8 ['ebe26', 'a06cb'] See 8 ads matching ["ebe26","a06cb"]
c4200 interests:African—American culture 8 ['ebe26', 'c4200'] See 8 ads matching ["ebe26","c4200"]
2dfbd interests:African-Amerioan Civil Rights Movement (1954—68) 7 ['ebe26', '2dfbd'] See 7 ads matching ["ebe26","2dfbd"]
5ad28 interests:Black Business Works 7 ['ebe26', '5ad28'] See 7 ads matching ["ebe26","5ad28"]
cbcce interests:Anti-discrimination 7 ['ebe26', 'cbcce'] See 7 ads matching ["ebe26","cbcce"]
e38cb interests:Martin Luther King ”I 7 ['ebe26', 'e38cb'] See 7 ads matching ["ebe26","e38cb"]
1742f location_living_in:united_states:Detroit (+35 mi) Michigan 5 ['ebe26', '1742f'] See 5 ads matching ["ebe26","1742f"]
2f957 interests:Union of Huffington Post Writers and Bloggers 5 ['ebe26', '2f957'] See 5 ads matching ["ebe26","2f957"]
4accc placements:on pages: News Feed on desktop computers 5 ['ebe26', '4accc'] See 5 ads matching ["ebe26","4accc"]
74353 interests:BIackNews.com 5 ['ebe26', '74353'] See 5 ads matching ["ebe26","74353"]
a340a placements:Instream Video on desktop computers 5 ['ebe26', 'a340a'] See 5 ads matching ["ebe26","a340a"]
d003e placements:Instream Video on mobile devices 5 ['ebe26', 'd003e'] See 5 ads matching ["ebe26","d003e"]
d1b15 interests:BLACK BUSINESS GLOBAL 5 ['ebe26', 'd1b15'] See 5 ads matching ["ebe26","d1b15"]
e0460 interests:African—American Civil Rights Movement (1954—68) 5 ['ebe26', 'e0460'] See 5 ads matching ["ebe26","e0460"]
fbbdb interests:Black Business Builders Club 5 ['ebe26', 'fbbdb'] See 5 ads matching ["ebe26","fbbdb"]
3bf90 interests:2016 4 ['ebe26', '3bf90'] See 4 ads matching ["ebe26","3bf90"]
591c8 interests:Malcolm X or United States presidential election 4 ['ebe26', '591c8'] See 4 ads matching ["ebe26","591c8"]
7b150 age:18 — 65+ 4 ['ebe26', '7b150'] See 4 ads matching ["ebe26","7b150"]
9e07c interests:Martin Luther King I” 4 ['ebe26', '9e07c'] See 4 ads matching ["ebe26","9e07c"]
c3780 interests:Black is beautiful 4 ['ebe26', 'c3780'] See 4 ads matching ["ebe26","c3780"]
3fe3b location:united_states:Los Angeles (+40 km) California 3 ['ebe26', '3fe3b'] See 3 ads matching ["ebe26","3fe3b"]
550f6 interests:Police misconduct 3 ['ebe26', '550f6'] See 3 ads matching ["ebe26","550f6"]
afc04 interests:Pan—Africanism 3 ['ebe26', 'afc04'] See 3 ads matching ["ebe26","afc04"]
189ac location_living_in:united_states:Charlotte (+25 mi) North Carolina 2 ['ebe26', '189ac'] See 2 ads matching ["ebe26","189ac"]
94840 interests:Black Owned Business Network 2 ['ebe26', '94840'] See 2 ads matching ["ebe26","94840"]
b08d4 location:united_states:New York (+40 km) New York 2 ['ebe26', 'b08d4'] See 2 ads matching ["ebe26","b08d4"]
fd7d6 interests:Pan-Africahism 2 ['ebe26', 'fd7d6'] See 2 ads matching ["ebe26","fd7d6"]
30168 interests:African-American music 1 ['ebe26', '30168'] See 1 ads matching ["ebe26","30168"]
32c9f age:18 — 50 1 ['ebe26', '32c9f'] See 1 ads matching ["ebe26","32c9f"]
63865 age:18 - 5O 1 ['ebe26', '63865'] See 1 ads matching ["ebe26","63865"]
9dc02 location:united_states:Baltimore (+25 mi) Maryland 1 ['ebe26', '9dc02'] See 1 ads matching ["ebe26","9dc02"]
b3ed1 interests:Martin Luther 1 ['ebe26', 'b3ed1'] See 1 ads matching ["ebe26","b3ed1"]
b69b4 location_living_in:united_states:Detroit (+50 mi) Michigan 1 ['ebe26', 'b69b4'] See 1 ads matching ["ebe26","b69b4"]
bafbc interests:Black Panther Party 1 ['ebe26', 'bafbc'] See 1 ads matching ["ebe26","bafbc"]
bca17 interests:Pan-Afrioanism 1 ['ebe26', 'bca17'] See 1 ads matching ["ebe26","bca17"]
c3b9b age:16 — 65+ 1 ['ebe26', 'c3b9b'] See 1 ads matching ["ebe26","c3b9b"]
c6e92 location_living_in:united_states:Los Angeles (+50 mi) California 1 ['ebe26', 'c6e92'] See 1 ads matching ["ebe26","c6e92"]
Powered by Datasette · Query took 25.714ms