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 201 ['313af', 'b1201'] See 201 ads matching ["313af","b1201"]
e6200 placements:News Feed on desktop computers 198 ['313af', 'e6200'] See 198 ads matching ["313af","e6200"]
ebe26 interests:African-American history 178 ['313af', 'ebe26'] See 178 ads matching ["313af","ebe26"]
8e7b3 interests:Jr. 165 ['313af', '8e7b3'] See 165 ads matching ["313af","8e7b3"]
d6ade interests:Martin Luther King 165 ['313af', 'd6ade'] See 165 ads matching ["313af","d6ade"]
a0f55 interests:Malcolm X 163 ['313af', 'a0f55'] See 163 ads matching ["313af","a0f55"]
371f0 age:18 - 65+ 150 ['313af', '371f0'] See 150 ads matching ["313af","371f0"]
cc5ed location_living_in:United States 145 ['313af', 'cc5ed'] See 145 ads matching ["313af","cc5ed"]
1073c interests:Black (Color) 87 ['313af', '1073c'] See 87 ads matching ["313af","1073c"]
ec3ac location:United States 47 ['313af', 'ec3ac'] See 47 ads matching ["313af","ec3ac"]
aabe0 interests:Black Consciousness Movement 44 ['313af', 'aabe0'] See 44 ads matching ["313af","aabe0"]
d4179 interests:African-American culture 42 ['313af', 'd4179'] See 42 ads matching ["313af","d4179"]
59d75 language:English (US) 32 ['313af', '59d75'] See 32 ads matching ["313af","59d75"]
5e075 language:English (UK) 32 ['313af', '5e075'] See 32 ads matching ["313af","5e075"]
4801f age:16 - 65+ 30 ['313af', '4801f'] See 30 ads matching ["313af","4801f"]
96bbd interests:Pan-Africanism 26 ['313af', '96bbd'] See 26 ads matching ["313af","96bbd"]
17183 interests:Black nationalism 18 ['313af', '17183'] See 18 ads matching ["313af","17183"]
87fac interests:Stop Police Brutality 14 ['313af', '87fac'] See 14 ads matching ["313af","87fac"]
04629 interest_expansion:On 13 ['313af', '04629'] See 13 ads matching ["313af","04629"]
3502d placements:Instagram Feed 13 ['313af', '3502d'] See 13 ads matching ["313af","3502d"]
61c1a age:18 - 50 13 ['313af', '61c1a'] See 13 ads matching ["313af","61c1a"]
902e5 placements:Right column on desktop computers 12 ['313af', '902e5'] See 12 ads matching ["313af","902e5"]
cdb88 excluded_connections:Exclude people who like BM 12 ['313af', 'cdb88'] See 12 ads matching ["313af","cdb88"]
f9cc2 interests:African- American history 11 ['313af', 'f9cc2'] See 11 ads matching ["313af","f9cc2"]
2f957 interests:Union of Huffington Post Writers and Bloggers 9 ['313af', '2f957'] See 9 ads matching ["313af","2f957"]
343d8 interests:African- American culture 9 ['313af', '343d8'] See 9 ads matching ["313af","343d8"]
550f6 interests:Police misconduct 9 ['313af', '550f6'] See 9 ads matching ["313af","550f6"]
3bf90 interests:2016 7 ['313af', '3bf90'] See 7 ads matching ["313af","3bf90"]
3e5f6 interests:Martin Luther King III 7 ['313af', '3e5f6'] See 7 ads matching ["313af","3e5f6"]
40c27 interests:Police Brutality is a Crime 7 ['313af', '40c27'] See 7 ads matching ["313af","40c27"]
6e219 interests:African—American history 7 ['313af', '6e219'] See 7 ads matching ["313af","6e219"]
a06cb interests:Police brutality in the United States 7 ['313af', 'a06cb'] See 7 ads matching ["313af","a06cb"]
cbcce interests:Anti-discrimination 7 ['313af', 'cbcce'] See 7 ads matching ["313af","cbcce"]
e38cb interests:Martin Luther King ”I 7 ['313af', 'e38cb'] See 7 ads matching ["313af","e38cb"]
1742f location_living_in:united_states:Detroit (+35 mi) Michigan 5 ['313af', '1742f'] See 5 ads matching ["313af","1742f"]
5ad28 interests:Black Business Works 5 ['313af', '5ad28'] See 5 ads matching ["313af","5ad28"]
7b150 age:18 — 65+ 5 ['313af', '7b150'] See 5 ads matching ["313af","7b150"]
81536 interests:United States presidential election 4 ['313af', '81536'] See 4 ads matching ["313af","81536"]
9e07c interests:Martin Luther King I” 4 ['313af', '9e07c'] See 4 ads matching ["313af","9e07c"]
afc04 interests:Pan—Africanism 4 ['313af', 'afc04'] See 4 ads matching ["313af","afc04"]
c3780 interests:Black is beautiful 4 ['313af', 'c3780'] See 4 ads matching ["313af","c3780"]
ebede interests:Elections in the United States 4 ['313af', 'ebede'] See 4 ads matching ["313af","ebede"]
27176 interests:Black History Month 3 ['313af', '27176'] See 3 ads matching ["313af","27176"]
2cd39 interests:Anti- discrimination 3 ['313af', '2cd39'] See 3 ads matching ["313af","2cd39"]
4accc placements:on pages: News Feed on desktop computers 3 ['313af', '4accc'] See 3 ads matching ["313af","4accc"]
591c8 interests:Malcolm X or United States presidential election 3 ['313af', '591c8'] See 3 ads matching ["313af","591c8"]
7ceb1 interests:Cop Block 3 ['313af', '7ceb1'] See 3 ads matching ["313af","7ceb1"]
a340a placements:Instream Video on desktop computers 3 ['313af', 'a340a'] See 3 ads matching ["313af","a340a"]
c4200 interests:African—American culture 3 ['313af', 'c4200'] See 3 ads matching ["313af","c4200"]
d003e placements:Instream Video on mobile devices 3 ['313af', 'd003e'] See 3 ads matching ["313af","d003e"]
d1b15 interests:BLACK BUSINESS GLOBAL 3 ['313af', 'd1b15'] See 3 ads matching ["313af","d1b15"]
fbbdb interests:Black Business Builders Club 3 ['313af', 'fbbdb'] See 3 ads matching ["313af","fbbdb"]
189ac location_living_in:united_states:Charlotte (+25 mi) North Carolina 2 ['313af', '189ac'] See 2 ads matching ["313af","189ac"]
4e7f1 interests:African-Amerioan history 2 ['313af', '4e7f1'] See 2 ads matching ["313af","4e7f1"]
94840 interests:Black Owned Business Network 2 ['313af', '94840'] See 2 ads matching ["313af","94840"]
c3b9b age:16 — 65+ 2 ['313af', 'c3b9b'] See 2 ads matching ["313af","c3b9b"]
e6034 interests:African— American culture 2 ['313af', 'e6034'] See 2 ads matching ["313af","e6034"]
fd7d6 interests:Pan-Africahism 2 ['313af', 'fd7d6'] See 2 ads matching ["313af","fd7d6"]
32c9f age:18 — 50 1 ['313af', '32c9f'] See 1 ads matching ["313af","32c9f"]
b3ed1 interests:Martin Luther 1 ['313af', 'b3ed1'] See 1 ads matching ["313af","b3ed1"]
b69b4 location_living_in:united_states:Detroit (+50 mi) Michigan 1 ['313af', 'b69b4'] See 1 ads matching ["313af","b69b4"]
bafbc interests:Black Panther Party 1 ['313af', 'bafbc'] See 1 ads matching ["313af","bafbc"]
c6e92 location_living_in:united_states:Los Angeles (+50 mi) California 1 ['313af', 'c6e92'] See 1 ads matching ["313af","c6e92"]
Powered by Datasette · Query took 27.532ms