home / russian-ads

russian-ads

Custom SQL query returning 39 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
6a38c people_who_match:interests:Stop Police Brutality 106 ['9a8c6', '6a38c'] See 106 ads matching ["9a8c6","6a38c"]
ec3ac location:United States 106 ['9a8c6', 'ec3ac'] See 106 ads matching ["9a8c6","ec3ac"]
41b26 people_who_match:interests:Black Consciousness Movement 105 ['9a8c6', '41b26'] See 105 ads matching ["9a8c6","41b26"]
59d75 language:English (US) 105 ['9a8c6', '59d75'] See 105 ads matching ["9a8c6","59d75"]
5e075 language:English (UK) 105 ['9a8c6', '5e075'] See 105 ads matching ["9a8c6","5e075"]
b1201 placements:News Feed on mobile devices 105 ['9a8c6', 'b1201'] See 105 ads matching ["9a8c6","b1201"]
d6347 people_who_match:interests:Black nationalism 105 ['9a8c6', 'd6347'] See 105 ads matching ["9a8c6","d6347"]
e128e people_who_match:interests:Black (Color) 105 ['9a8c6', 'e128e'] See 105 ads matching ["9a8c6","e128e"]
e6200 placements:News Feed on desktop computers 104 ['9a8c6', 'e6200'] See 104 ads matching ["9a8c6","e6200"]
2db78 people_who_match:interests:Martin Luther King III 103 ['9a8c6', '2db78'] See 103 ads matching ["9a8c6","2db78"]
abb72 people_who_match:interests:African-American history 98 ['9a8c6', 'abb72'] See 98 ads matching ["9a8c6","abb72"]
4801f age:16 - 65+ 94 ['9a8c6', '4801f'] See 94 ads matching ["9a8c6","4801f"]
ebf60 people_who_match:interests:Pan-Africanism 92 ['9a8c6', 'ebf60'] See 92 ads matching ["9a8c6","ebf60"]
a1307 people_who_match:interests:African-American Civil Rights Movement (1954—68) 56 ['9a8c6', 'a1307'] See 56 ads matching ["9a8c6","a1307"]
a2d21 people_who_match:interests:African-American culture 56 ['9a8c6', 'a2d21'] See 56 ads matching ["9a8c6","a2d21"]
1d2ab people_who_match:interests:African—American Civil Rights Movement (1954—68) 47 ['9a8c6', '1d2ab'] See 47 ads matching ["9a8c6","1d2ab"]
91693 people_who_match:interests:African—American culture 47 ['9a8c6', '91693'] See 47 ads matching ["9a8c6","91693"]
13ab6 people_who_match:interests:Pan—Africanism 11 ['9a8c6', '13ab6'] See 11 ads matching ["9a8c6","13ab6"]
c3b9b age:16 — 65+ 11 ['9a8c6', 'c3b9b'] See 11 ads matching ["9a8c6","c3b9b"]
4225c people_who_match:interests:African—American history 5 ['9a8c6', '4225c'] See 5 ads matching ["9a8c6","4225c"]
3fbed people_who_match:interests:African-Amerioan history 2 ['9a8c6', '3fbed'] See 2 ads matching ["9a8c6","3fbed"]
487f2 people_who_match:interests:African-Amerioan Civil Rights Movement (1954—68) 2 ['9a8c6', '487f2'] See 2 ads matching ["9a8c6","487f2"]
a5ab5 people_who_match:interests:African-Amerioan culture 2 ['9a8c6', 'a5ab5'] See 2 ads matching ["9a8c6","a5ab5"]
b322d people_who_match:interests:Pan-Afrioanism 2 ['9a8c6', 'b322d'] See 2 ads matching ["9a8c6","b322d"]
086cd people_who_match:interests:Martin Luther King I” 1 ['9a8c6', '086cd'] See 1 ads matching ["9a8c6","086cd"]
0deb1 people_who_match:interests:Police brutality in the United States 1 ['9a8c6', '0deb1'] See 1 ads matching ["9a8c6","0deb1"]
30a44 people_who_match:interests:Media bias 1 ['9a8c6', '30a44'] See 1 ads matching ["9a8c6","30a44"]
3502d placements:Instagram Feed 1 ['9a8c6', '3502d'] See 1 ads matching ["9a8c6","3502d"]
3c576 people_who_match:interests:Martin Luther King lll 1 ['9a8c6', '3c576'] See 1 ads matching ["9a8c6","3c576"]
42bf9 people_who_match:interests:Refugees of the Syrian Civil War 1 ['9a8c6', '42bf9'] See 1 ads matching ["9a8c6","42bf9"]
4accc placements:on pages: News Feed on desktop computers 1 ['9a8c6', '4accc'] See 1 ads matching ["9a8c6","4accc"]
60170 placements:Suggested Video on desktop computers 1 ['9a8c6', '60170'] See 1 ads matching ["9a8c6","60170"]
7a9b5 placements:Suggested Video on mobile devices 1 ['9a8c6', '7a9b5'] See 1 ads matching ["9a8c6","7a9b5"]
9fe47 people_who_match:interests:Police Brutality is a Crime 1 ['9a8c6', '9fe47'] See 1 ads matching ["9a8c6","9fe47"]
c4c8c people_who_match:interests:Syria-News 1 ['9a8c6', 'c4c8c'] See 1 ads matching ["9a8c6","c4c8c"]
d1e7b age:13 - 65+ 1 ['9a8c6', 'd1e7b'] See 1 ads matching ["9a8c6","d1e7b"]
e1f0e people_who_match:interests:National Resistance Movement 1 ['9a8c6', 'e1f0e'] See 1 ads matching ["9a8c6","e1f0e"]
e4519 people_who_match:interests:Anonymous (group) 1 ['9a8c6', 'e4519'] See 1 ads matching ["9a8c6","e4519"]
f6c6f people_who_match:interests:Syria Charity 1 ['9a8c6', 'f6c6f'] See 1 ads matching ["9a8c6","f6c6f"]
Powered by Datasette · Query took 45.179ms