home / russian-ads

russian-ads

Custom SQL query returning 95 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 123 ['1c31d', 'b1201'] See 123 ads matching ["1c31d","b1201"]
59d75 language:English (US) 107 ['1c31d', '59d75'] See 107 ads matching ["1c31d","59d75"]
5e075 language:English (UK) 107 ['1c31d', '5e075'] See 107 ads matching ["1c31d","5e075"]
e6200 placements:News Feed on desktop computers 107 ['1c31d', 'e6200'] See 107 ads matching ["1c31d","e6200"]
ec3ac location:United States 90 ['1c31d', 'ec3ac'] See 90 ads matching ["1c31d","ec3ac"]
37b6b interests:HuffPost Black Voices 62 ['1c31d', '37b6b'] See 62 ads matching ["1c31d","37b6b"]
40ff5 behaviors:African American (US) 57 ['1c31d', '40ff5'] See 57 ads matching ["1c31d","40ff5"]
6fba5 excluded_connections:Exclude people who like Black Matters 56 ['1c31d', '6fba5'] See 56 ads matching ["1c31d","6fba5"]
eaba0 interests:BlackNews.com 47 ['1c31d', 'eaba0'] See 47 ads matching ["1c31d","eaba0"]
ecb59 and_must_also_match:interests:HuffPost Black Voices 15 ['1c31d', 'ecb59'] See 15 ads matching ["1c31d","ecb59"]
74353 interests:BIackNews.com 14 ['1c31d', '74353'] See 14 ads matching ["1c31d","74353"]
9faf6 people_who_match:behaviors:African American (US) 14 ['1c31d', '9faf6'] See 14 ads matching ["1c31d","9faf6"]
6b827 excluded_connections:Exclude people who like Don't Shoot 11 ['1c31d', '6b827'] See 11 ads matching ["1c31d","6b827"]
cc5ed location_living_in:United States 11 ['1c31d', 'cc5ed'] See 11 ads matching ["1c31d","cc5ed"]
26b5a and_must_also_match:interests:BIackNews.com 10 ['1c31d', '26b5a'] See 10 ads matching ["1c31d","26b5a"]
7ee91 people_who_match:interests:Cop Block 10 ['1c31d', '7ee91'] See 10 ads matching ["1c31d","7ee91"]
13f83 and_must_also_match:interests:HuffPost Politics 9 ['1c31d', '13f83'] See 9 ads matching ["1c31d","13f83"]
d32da location:united_states:Detroit Michigan 9 ['1c31d', 'd32da'] See 9 ads matching ["1c31d","d32da"]
17a50 location:united_states:Milwaukee (+25 mi) Wisconsin 7 ['1c31d', '17a50'] See 7 ads matching ["1c31d","17a50"]
28463 location:united_states:Atlanta (+25 mi) Georgia 7 ['1c31d', '28463'] See 7 ads matching ["1c31d","28463"]
5bb13 placements:Third-party Apps and Websites on mobile devices 7 ['1c31d', '5bb13'] See 7 ads matching ["1c31d","5bb13"]
9dc02 location:united_states:Baltimore (+25 mi) Maryland 7 ['1c31d', '9dc02'] See 7 ads matching ["1c31d","9dc02"]
c4902 location:united_states:New Orleans (+25 mi) Louisiana 7 ['1c31d', 'c4902'] See 7 ads matching ["1c31d","c4902"]
dad97 location:united_states:Philadelphia (+25 mi) Pennsylvania 7 ['1c31d', 'dad97'] See 7 ads matching ["1c31d","dad97"]
e95f6 excluded_connections:Exclude people who like Blacktivist 7 ['1c31d', 'e95f6'] See 7 ads matching ["1c31d","e95f6"]
ec4f5 location:united_states:Ferguson (+25 mi), St. Louis (+25 mi) Missouri 7 ['1c31d', 'ec4f5'] See 7 ads matching ["1c31d","ec4f5"]
fb88d location:united_states:Oakland (+25 mi) California 7 ['1c31d', 'fb88d'] See 7 ads matching ["1c31d","fb88d"]
12cf3 and_must_also_match:employers:US. Army Reserve 6 ['1c31d', '12cf3'] See 6 ads matching ["1c31d","12cf3"]
17bc7 exclude:behaviors:Hispanic (US - English dominant) 6 ['1c31d', '17bc7'] See 6 ads matching ["1c31d","17bc7"]
2e370 industry:Veterans (US) 6 ['1c31d', '2e370'] See 6 ads matching ["1c31d","2e370"]
397c1 and_must_also_match:employers:Job title: Veteran 6 ['1c31d', '397c1'] See 6 ads matching ["1c31d","397c1"]
4f54f people_who_match:interests:United States Department of Veterans Affairs 6 ['1c31d', '4f54f'] See 6 ads matching ["1c31d","4f54f"]
55a79 and_must_also_match:employers: 6 ['1c31d', '55a79'] See 6 ads matching ["1c31d","55a79"]
7e7c6 people_who_match:interests:Veterans 6 ['1c31d', '7e7c6'] See 6 ads matching ["1c31d","7e7c6"]
902e5 placements:Right column on desktop computers 6 ['1c31d', '902e5'] See 6 ads matching ["1c31d","902e5"]
95f91 exclude:behaviors:Asian American (US) 6 ['1c31d', '95f91'] See 6 ads matching ["1c31d","95f91"]
9c537 friends_of_connections:Friends of people who are connected to Black Matters 6 ['1c31d', '9c537'] See 6 ads matching ["1c31d","9c537"]
aa04d exclude:behaviors:Hispanic (US - Bilingual: English and Spanish) 6 ['1c31d', 'aa04d'] See 6 ads matching ["1c31d","aa04d"]
b42a4 exclude:behaviors:Hispanic (US - All) 6 ['1c31d', 'b42a4'] See 6 ads matching ["1c31d","b42a4"]
cba3e excluded_connections:Exclude people who like Veterans Come First 6 ['1c31d', 'cba3e'] See 6 ads matching ["1c31d","cba3e"]
ccbed industry:Military (Global) 6 ['1c31d', 'ccbed'] See 6 ads matching ["1c31d","ccbed"]
d679e exclude:behaviors:Hispanic (US - Spanish dominant) 6 ['1c31d', 'd679e'] See 6 ads matching ["1c31d","d679e"]
1073c interests:Black (Color) 5 ['1c31d', '1073c'] See 5 ads matching ["1c31d","1073c"]
16102 interests:Human rights 5 ['1c31d', '16102'] See 5 ads matching ["1c31d","16102"]
235b1 interests:Pan Africanist Congress of Azania 5 ['1c31d', '235b1'] See 5 ads matching ["1c31d","235b1"]
2a6ea interests:Violence prevention 5 ['1c31d', '2a6ea'] See 5 ads matching ["1c31d","2a6ea"]
8da48 interests:Nelson Mandela 5 ['1c31d', '8da48'] See 5 ads matching ["1c31d","8da48"]
8e7b3 interests:Jr. 5 ['1c31d', '8e7b3'] See 5 ads matching ["1c31d","8e7b3"]
9d04d interests:African National Congress 5 ['1c31d', '9d04d'] See 5 ads matching ["1c31d","9d04d"]
a0f55 interests:Malcolm X 5 ['1c31d', 'a0f55'] See 5 ads matching ["1c31d","a0f55"]
d6ade interests:Martin Luther King 5 ['1c31d', 'd6ade'] See 5 ads matching ["1c31d","d6ade"]
e7597 and_must_also_match:interests:BIaokNewsoom 5 ['1c31d', 'e7597'] See 5 ads matching ["1c31d","e7597"]
e9db4 interests:Justice 5 ['1c31d', 'e9db4'] See 5 ads matching ["1c31d","e9db4"]
f6590 interests:Muhammad Ali 5 ['1c31d', 'f6590'] See 5 ads matching ["1c31d","f6590"]
16071 and_must_also_match:behaviors:African American (US) 4 ['1c31d', '16071'] See 4 ads matching ["1c31d","16071"]
cbcce interests:Anti-discrimination 4 ['1c31d', 'cbcce'] See 4 ads matching ["1c31d","cbcce"]
d32ce interests:HuffPost Politics 4 ['1c31d', 'd32ce'] See 4 ads matching ["1c31d","d32ce"]
d75c0 people_who_match:interests:HuffPost Black Voices 4 ['1c31d', 'd75c0'] See 4 ads matching ["1c31d","d75c0"]
02a85 politics:Likely to engage with political content (conservative) 3 ['1c31d', '02a85'] See 3 ads matching ["1c31d","02a85"]
0b529 accessing_facebook_on:Wi-Fi 3 ['1c31d', '0b529'] See 3 ads matching ["1c31d","0b529"]
20a66 people_who_match:interests:Disabled American Veterans or Supporting Our Veterans 3 ['1c31d', '20a66'] See 3 ads matching ["1c31d","20a66"]
508a5 location:united_states:South Carolina 3 ['1c31d', '508a5'] See 3 ads matching ["1c31d","508a5"]
8751c people_who_match:interests:Supporting Our Veterans 3 ['1c31d', '8751c'] See 3 ads matching ["1c31d","8751c"]
99241 people_who_match:interests: 3 ['1c31d', '99241'] See 3 ads matching ["1c31d","99241"]
be0e2 politics:Likely to engage with political content (moderate) 3 ['1c31d', 'be0e2'] See 3 ads matching ["1c31d","be0e2"]
be4a2 people_who_match:interests:HuffPost Politics 3 ['1c31d', 'be4a2'] See 3 ads matching ["1c31d","be4a2"]
db9ea people_who_match:interests:Disabled American Veterans 3 ['1c31d', 'db9ea'] See 3 ads matching ["1c31d","db9ea"]
0524b interests:Same-sex marriage 2 ['1c31d', '0524b'] See 2 ads matching ["1c31d","0524b"]
0b2ab excluded_connections:Exclude people who like LGBT United 2 ['1c31d', '0b2ab'] See 2 ads matching ["1c31d","0b2ab"]
1021f interests:Gay pride 2 ['1c31d', '1021f'] See 2 ads matching ["1c31d","1021f"]
3f3cb friends_of_connections:Friends of people who are connected to LGBT United 2 ['1c31d', '3f3cb'] See 2 ads matching ["1c31d","3f3cb"]
51b28 interests:Black Women Are Beautiful 2 ['1c31d', '51b28'] See 2 ads matching ["1c31d","51b28"]
57006 location:united_states:New York 2 ['1c31d', '57006'] See 2 ads matching ["1c31d","57006"]
65a38 people_who_match:interests:BIackNews.com 2 ['1c31d', '65a38'] See 2 ads matching ["1c31d","65a38"]
84f2d interests:Homosexuality 2 ['1c31d', '84f2d'] See 2 ads matching ["1c31d","84f2d"]
94452 people_who_match:interests:Homosexuality 2 ['1c31d', '94452'] See 2 ads matching ["1c31d","94452"]
a1291 people_who_match:interests:Same-sex marriage 2 ['1c31d', 'a1291'] See 2 ads matching ["1c31d","a1291"]
c08cd people_who_match:interests:LGBT community 2 ['1c31d', 'c08cd'] See 2 ads matching ["1c31d","c08cd"]
c8374 people_who_match:interests:Gay pride 2 ['1c31d', 'c8374'] See 2 ads matching ["1c31d","c8374"]
d1c9d people_who_match:interests:LGBT culture 2 ['1c31d', 'd1c9d'] See 2 ads matching ["1c31d","d1c9d"]
d4e6c interests:Melanin 2 ['1c31d', 'd4e6c'] See 2 ads matching ["1c31d","d4e6c"]
eed19 interests:LGBT community 2 ['1c31d', 'eed19'] See 2 ads matching ["1c31d","eed19"]
f8d8c interests:LGBT culture 2 ['1c31d', 'f8d8c'] See 2 ads matching ["1c31d","f8d8c"]
0e67d interests:Black Knowledge 1 ['1c31d', '0e67d'] See 1 ads matching ["1c31d","0e67d"]
2cd39 interests:Anti- discrimination 1 ['1c31d', '2cd39'] See 1 ads matching ["1c31d","2cd39"]
40b91 people_who_match:interests:BlackNews.com 1 ['1c31d', '40b91'] See 1 ads matching ["1c31d","40b91"]
552b9 location:united_states:Oakland California 1 ['1c31d', '552b9'] See 1 ads matching ["1c31d","552b9"]
5cae5 interests:BlaCkNews.com 1 ['1c31d', '5cae5'] See 1 ads matching ["1c31d","5cae5"]
74230 and_must_also_match:interests:Black Knowledge 1 ['1c31d', '74230'] See 1 ads matching ["1c31d","74230"]
90a93 interests:BEING BLACK!!! 1 ['1c31d', '90a93'] See 1 ads matching ["1c31d","90a93"]
b156f people_who_match:multicultural_affinity:African American (US) 1 ['1c31d', 'b156f'] See 1 ads matching ["1c31d","b156f"]
c4bb6 interests:Black Tea Patriots 1 ['1c31d', 'c4bb6'] See 1 ads matching ["1c31d","c4bb6"]
d461c interests:T- shirt 1 ['1c31d', 'd461c'] See 1 ads matching ["1c31d","d461c"]
f0dcd people_who_match:interests:BIaokNewsoom 1 ['1c31d', 'f0dcd'] See 1 ads matching ["1c31d","f0dcd"]
ff545 and_must_also_match:interests:Black Tea Patriots 1 ['1c31d', 'ff545'] See 1 ads matching ["1c31d","ff545"]
Powered by Datasette · Query took 63.148ms