home / russian-ads

russian-ads

Custom SQL query returning 68 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
8e7b3 interests:Jr. 222 ['d6ade', '8e7b3'] See 222 ads matching ["d6ade","8e7b3"]
b1201 placements:News Feed on mobile devices 222 ['d6ade', 'b1201'] See 222 ads matching ["d6ade","b1201"]
e6200 placements:News Feed on desktop computers 217 ['d6ade', 'e6200'] See 217 ads matching ["d6ade","e6200"]
a0f55 interests:Malcolm X 216 ['d6ade', 'a0f55'] See 216 ads matching ["d6ade","a0f55"]
371f0 age:18 - 65+ 191 ['d6ade', '371f0'] See 191 ads matching ["d6ade","371f0"]
cc5ed location_living_in:United States 184 ['d6ade', 'cc5ed'] See 184 ads matching ["d6ade","cc5ed"]
313af interests:African-American Civil Rights Movement (1954—68) 165 ['d6ade', '313af'] See 165 ads matching ["d6ade","313af"]
ebe26 interests:African-American history 163 ['d6ade', 'ebe26'] See 163 ads matching ["d6ade","ebe26"]
1073c interests:Black (Color) 72 ['d6ade', '1073c'] See 72 ads matching ["d6ade","1073c"]
2dfbd interests:African-Amerioan Civil Rights Movement (1954—68) 35 ['d6ade', '2dfbd'] See 35 ads matching ["d6ade","2dfbd"]
d4179 interests:African-American culture 35 ['d6ade', 'd4179'] See 35 ads matching ["d6ade","d4179"]
aabe0 interests:Black Consciousness Movement 32 ['d6ade', 'aabe0'] See 32 ads matching ["d6ade","aabe0"]
4e7f1 interests:African-Amerioan history 31 ['d6ade', '4e7f1'] See 31 ads matching ["d6ade","4e7f1"]
ec3ac location:United States 28 ['d6ade', 'ec3ac'] See 28 ads matching ["d6ade","ec3ac"]
04629 interest_expansion:On 19 ['d6ade', '04629'] See 19 ads matching ["d6ade","04629"]
3502d placements:Instagram Feed 19 ['d6ade', '3502d'] See 19 ads matching ["d6ade","3502d"]
61c1a age:18 - 50 18 ['d6ade', '61c1a'] See 18 ads matching ["d6ade","61c1a"]
cbcce interests:Anti-discrimination 16 ['d6ade', 'cbcce'] See 16 ads matching ["d6ade","cbcce"]
8da48 interests:Nelson Mandela 12 ['d6ade', '8da48'] See 12 ads matching ["d6ade","8da48"]
902e5 placements:Right column on desktop computers 12 ['d6ade', '902e5'] See 12 ads matching ["d6ade","902e5"]
cdb88 excluded_connections:Exclude people who like BM 12 ['d6ade', 'cdb88'] See 12 ads matching ["d6ade","cdb88"]
2f957 interests:Union of Huffington Post Writers and Bloggers 11 ['d6ade', '2f957'] See 11 ads matching ["d6ade","2f957"]
3bf90 interests:2016 10 ['d6ade', '3bf90'] See 10 ads matching ["d6ade","3bf90"]
40c27 interests:Police Brutality is a Crime 10 ['d6ade', '40c27'] See 10 ads matching ["d6ade","40c27"]
87fac interests:Stop Police Brutality 10 ['d6ade', '87fac'] See 10 ads matching ["d6ade","87fac"]
a06cb interests:Police brutality in the United States 10 ['d6ade', 'a06cb'] See 10 ads matching ["d6ade","a06cb"]
16102 interests:Human rights 9 ['d6ade', '16102'] See 9 ads matching ["d6ade","16102"]
235b1 interests:Pan Africanist Congress of Azania 9 ['d6ade', '235b1'] See 9 ads matching ["d6ade","235b1"]
2a6ea interests:Violence prevention 9 ['d6ade', '2a6ea'] See 9 ads matching ["d6ade","2a6ea"]
5ad28 interests:Black Business Works 9 ['d6ade', '5ad28'] See 9 ads matching ["d6ade","5ad28"]
9d04d interests:African National Congress 9 ['d6ade', '9d04d'] See 9 ads matching ["d6ade","9d04d"]
e9db4 interests:Justice 9 ['d6ade', 'e9db4'] See 9 ads matching ["d6ade","e9db4"]
6e219 interests:African—American history 8 ['d6ade', '6e219'] See 8 ads matching ["d6ade","6e219"]
6dfca interests:| Have a Dream 7 ['d6ade', '6dfca'] See 7 ads matching ["d6ade","6dfca"]
872c3 interests:Medgar Evers 7 ['d6ade', '872c3'] See 7 ads matching ["d6ade","872c3"]
d1b15 interests:BLACK BUSINESS GLOBAL 7 ['d6ade', 'd1b15'] See 7 ads matching ["d6ade","d1b15"]
f6644 interests:Mahatma Gandhi 7 ['d6ade', 'f6644'] See 7 ads matching ["d6ade","f6644"]
fbbdb interests:Black Business Builders Club 7 ['d6ade', 'fbbdb'] See 7 ads matching ["d6ade","fbbdb"]
591c8 interests:Malcolm X or United States presidential election 6 ['d6ade', '591c8'] See 6 ads matching ["d6ade","591c8"]
c1ffd interests:African-Amerioan culture 6 ['d6ade', 'c1ffd'] See 6 ads matching ["d6ade","c1ffd"]
e0460 interests:African—American Civil Rights Movement (1954—68) 6 ['d6ade', 'e0460'] See 6 ads matching ["d6ade","e0460"]
1742f location_living_in:united_states:Detroit (+35 mi) Michigan 5 ['d6ade', '1742f'] See 5 ads matching ["d6ade","1742f"]
1c31d age:18 - 54 5 ['d6ade', '1c31d'] See 5 ads matching ["d6ade","1c31d"]
4accc placements:on pages: News Feed on desktop computers 5 ['d6ade', '4accc'] See 5 ads matching ["d6ade","4accc"]
7b150 age:18 — 65+ 5 ['d6ade', '7b150'] See 5 ads matching ["d6ade","7b150"]
a340a placements:Instream Video on desktop computers 5 ['d6ade', 'a340a'] See 5 ads matching ["d6ade","a340a"]
c4200 interests:African—American culture 5 ['d6ade', 'c4200'] See 5 ads matching ["d6ade","c4200"]
d003e placements:Instream Video on mobile devices 5 ['d6ade', 'd003e'] See 5 ads matching ["d6ade","d003e"]
f6590 interests:Muhammad Ali 5 ['d6ade', 'f6590'] See 5 ads matching ["d6ade","f6590"]
81536 interests:United States presidential election 4 ['d6ade', '81536'] See 4 ads matching ["d6ade","81536"]
ebede interests:Elections in the United States 4 ['d6ade', 'ebede'] See 4 ads matching ["d6ade","ebede"]
f9cc2 interests:African- American history 4 ['d6ade', 'f9cc2'] See 4 ads matching ["d6ade","f9cc2"]
27176 interests:Black History Month 3 ['d6ade', '27176'] See 3 ads matching ["d6ade","27176"]
343d8 interests:African- American culture 3 ['d6ade', '343d8'] See 3 ads matching ["d6ade","343d8"]
550f6 interests:Police misconduct 3 ['d6ade', '550f6'] See 3 ads matching ["d6ade","550f6"]
d4e6c interests:Melanin 3 ['d6ade', 'd4e6c'] See 3 ads matching ["d6ade","d4e6c"]
0d4bc location:united_states:Washington 2 ['d6ade', '0d4bc'] See 2 ads matching ["d6ade","0d4bc"]
189ac location_living_in:united_states:Charlotte (+25 mi) North Carolina 2 ['d6ade', '189ac'] See 2 ads matching ["d6ade","189ac"]
51b28 interests:Black Women Are Beautiful 2 ['d6ade', '51b28'] See 2 ads matching ["d6ade","51b28"]
94840 interests:Black Owned Business Network 2 ['d6ade', '94840'] See 2 ads matching ["d6ade","94840"]
2cd39 interests:Anti- discrimination 1 ['d6ade', '2cd39'] See 1 ads matching ["d6ade","2cd39"]
32c9f age:18 — 50 1 ['d6ade', '32c9f'] See 1 ads matching ["d6ade","32c9f"]
37b6b interests:HuffPost Black Voices 1 ['d6ade', '37b6b'] See 1 ads matching ["d6ade","37b6b"]
63865 age:18 - 5O 1 ['d6ade', '63865'] See 1 ads matching ["d6ade","63865"]
acba4 page:18 - 65+ 1 ['d6ade', 'acba4'] See 1 ads matching ["d6ade","acba4"]
b69b4 location_living_in:united_states:Detroit (+50 mi) Michigan 1 ['d6ade', 'b69b4'] See 1 ads matching ["d6ade","b69b4"]
d461c interests:T- shirt 1 ['d6ade', 'd461c'] See 1 ads matching ["d6ade","d461c"]
e6034 interests:African— American culture 1 ['d6ade', 'e6034'] See 1 ads matching ["d6ade","e6034"]
Powered by Datasette · Query took 28.316ms