home / russian-ads

russian-ads

Custom SQL query returning 47 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
59d75 language:English (US) 90 ['b668a', '59d75'] See 90 ads matching ["b668a","59d75"]
5e075 language:English (UK) 90 ['b668a', '5e075'] See 90 ads matching ["b668a","5e075"]
b1201 placements:News Feed on mobile devices 90 ['b668a', 'b1201'] See 90 ads matching ["b668a","b1201"]
e6200 placements:News Feed on desktop computers 90 ['b668a', 'e6200'] See 90 ads matching ["b668a","e6200"]
ec3ac location:United States 90 ['b668a', 'ec3ac'] See 90 ads matching ["b668a","ec3ac"]
17bc7 exclude:behaviors:Hispanic (US - English dominant) 59 ['b668a', '17bc7'] See 59 ads matching ["b668a","17bc7"]
95f91 exclude:behaviors:Asian American (US) 59 ['b668a', '95f91'] See 59 ads matching ["b668a","95f91"]
aa04d exclude:behaviors:Hispanic (US - Bilingual: English and Spanish) 59 ['b668a', 'aa04d'] See 59 ads matching ["b668a","aa04d"]
b42a4 exclude:behaviors:Hispanic (US - All) 59 ['b668a', 'b42a4'] See 59 ads matching ["b668a","b42a4"]
d679e exclude:behaviors:Hispanic (US - Spanish dominant) 59 ['b668a', 'd679e'] See 59 ads matching ["b668a","d679e"]
7ce59 people_who_match:interests:National Museum of American History 43 ['b668a', '7ce59'] See 43 ads matching ["b668a","7ce59"]
a524c people_who_match:interests:Maya Angelou 43 ['b668a', 'a524c'] See 43 ads matching ["b668a","a524c"]
aaf5a age:18 - 51 43 ['b668a', 'aaf5a'] See 43 ads matching ["b668a","aaf5a"]
e128e people_who_match:interests:Black (Color) 41 ['b668a', 'e128e'] See 41 ads matching ["b668a","e128e"]
a14cf age:18 - 45 37 ['b668a', 'a14cf'] See 37 ads matching ["b668a","a14cf"]
100b7 people_who_match:interests:The Raw Story or motherjones 36 ['b668a', '100b7'] See 36 ads matching ["b668a","100b7"]
1518e people_who_match:interests:Behaviors: African American (US) 36 ['b668a', '1518e'] See 36 ads matching ["b668a","1518e"]
234f1 people_who_match:interests:Mumia Abu-Jamal 36 ['b668a', '234f1'] See 36 ads matching ["b668a","234f1"]
d75c0 people_who_match:interests:HuffPost Black Voices 28 ['b668a', 'd75c0'] See 28 ads matching ["b668a","d75c0"]
16071 and_must_also_match:behaviors:African American (US) 23 ['b668a', '16071'] See 23 ads matching ["b668a","16071"]
65a38 people_who_match:interests:BIackNews.com 23 ['b668a', '65a38'] See 23 ads matching ["b668a","65a38"]
598d9 people_who_match:interests:Racism in the United States 18 ['b668a', '598d9'] See 18 ads matching ["b668a","598d9"]
25fe0 exclude:multicultural_affinity:Hispanic (US - English dominant) 12 ['b668a', '25fe0'] See 12 ads matching ["b668a","25fe0"]
98471 exclude:multicultural_affinity:Hispanic (US - Bilingual) 12 ['b668a', '98471'] See 12 ads matching ["b668a","98471"]
ce60d exclude:multicultural_affinity:Asian American (US) 12 ['b668a', 'ce60d'] See 12 ads matching ["b668a","ce60d"]
6f86f exclude:multicultural_affinity:Hispanic (US - All) 11 ['b668a', '6f86f'] See 11 ads matching ["b668a","6f86f"]
89130 exclude:multicultural_affinity:Hispanic (US - Spanish dominant) 11 ['b668a', '89130'] See 11 ads matching ["b668a","89130"]
04629 interest_expansion:On 9 ['b668a', '04629'] See 9 ads matching ["b668a","04629"]
371f0 age:18 - 65+ 9 ['b668a', '371f0'] See 9 ads matching ["b668a","371f0"]
902e5 placements:Right column on desktop computers 9 ['b668a', '902e5'] See 9 ads matching ["b668a","902e5"]
3896c people_who_match:interests:Malcolm X Memorial Foundation 7 ['b668a', '3896c'] See 7 ads matching ["b668a","3896c"]
41ee7 people_who_match:interests:Mumia Abu- Jamal 7 ['b668a', '41ee7'] See 7 ads matching ["b668a","41ee7"]
94bdc people_who_match:interests:Multicultural Affinity: African American (US) 7 ['b668a', '94bdc'] See 7 ads matching ["b668a","94bdc"]
b8d0a people_who_match:interests:Stop Racism” 7 ['b668a', 'b8d0a'] See 7 ads matching ["b668a","b8d0a"]
ed356 people_who_match:interests:The Raw Story or mother jones 7 ['b668a', 'ed356'] See 7 ads matching ["b668a","ed356"]
2d469 people_who_match:interests:Black Tea Patriots 5 ['b668a', '2d469'] See 5 ads matching ["b668a","2d469"]
53e0d and_must_also_match:multicultural_affinity:African American (US) 5 ['b668a', '53e0d'] See 5 ads matching ["b668a","53e0d"]
973aa people_who_match:interests:Black Knowledge 5 ['b668a', '973aa'] See 5 ads matching ["b668a","973aa"]
f0dcd people_who_match:interests:BIaokNewsoom 4 ['b668a', 'f0dcd'] See 4 ads matching ["b668a","f0dcd"]
a1307 people_who_match:interests:African-American Civil Rights Movement (1954—68) 2 ['b668a', 'a1307'] See 2 ads matching ["b668a","a1307"]
27369 people_who_match:interests:Malcolm X 1 ['b668a', '27369'] See 1 ads matching ["b668a","27369"]
663d7 people_who_match:interests:BIackNewscom 1 ['b668a', '663d7'] See 1 ads matching ["b668a","663d7"]
8a7ee people_who_match:interests:Martin Luther King 1 ['b668a', '8a7ee'] See 1 ads matching ["b668a","8a7ee"]
9adee exclude:multicultural_affinity:Hispanic (US — All) 1 ['b668a', '9adee'] See 1 ads matching ["b668a","9adee"]
abb72 people_who_match:interests:African-American history 1 ['b668a', 'abb72'] See 1 ads matching ["b668a","abb72"]
b75b8 exclude:multicultural_affinity:Hispanic (US — Spanish dominant) 1 ['b668a', 'b75b8'] See 1 ads matching ["b668a","b75b8"]
d9950 age:18 — 45 1 ['b668a', 'd9950'] See 1 ads matching ["b668a","d9950"]
Powered by Datasette · Query took 19.504ms