home / russian-ads

russian-ads

Custom SQL query returning 50 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
371f0 age:18 - 65+ 88 ['72f3e', '371f0'] See 88 ads matching ["72f3e","371f0"]
0b28b interests:Independence 85 ['72f3e', '0b28b'] See 85 ads matching ["72f3e","0b28b"]
b1201 placements:News Feed on mobile devices 83 ['72f3e', 'b1201'] See 83 ads matching ["72f3e","b1201"]
e6200 placements:News Feed on desktop computers 83 ['72f3e', 'e6200'] See 83 ads matching ["72f3e","e6200"]
59d75 language:English (US) 70 ['72f3e', '59d75'] See 70 ads matching ["72f3e","59d75"]
5e075 language:English (UK) 63 ['72f3e', '5e075'] See 63 ads matching ["72f3e","5e075"]
24202 excluded_connections:Exclude people who like Heart of Texas 45 ['72f3e', '24202'] See 45 ads matching ["72f3e","24202"]
9eadd location:united_states:Texas 45 ['72f3e', '9eadd'] See 45 ads matching ["72f3e","9eadd"]
ec3ac location:United States 39 ['72f3e', 'ec3ac'] See 39 ads matching ["72f3e","ec3ac"]
72597 excluded_connections:Exclude people who like Being Patriotic 21 ['72f3e', '72597'] See 21 ads matching ["72f3e","72597"]
3502d placements:Instagram Feed 7 ['72f3e', '3502d'] See 7 ads matching ["72f3e","3502d"]
184db interests:WikiLeaks 4 ['72f3e', '184db'] See 4 ads matching ["72f3e","184db"]
1acaf interests:Racism in the United States 4 ['72f3e', '1acaf'] See 4 ads matching ["72f3e","1acaf"]
38933 interests:Conservatism in the United States 4 ['72f3e', '38933'] See 4 ads matching ["72f3e","38933"]
3b980 interests:United States Bill of Rights 4 ['72f3e', '3b980'] See 4 ads matching ["72f3e","3b980"]
3d9ff interests:Illegal immigration 4 ['72f3e', '3d9ff'] See 4 ads matching ["72f3e","3d9ff"]
54497 interests:Welfare state 4 ['72f3e', '54497'] See 4 ads matching ["72f3e","54497"]
58f61 interests:Michael Savage 4 ['72f3e', '58f61'] See 4 ads matching ["72f3e","58f61"]
5ba64 interests:Conservatism 4 ['72f3e', '5ba64'] See 4 ads matching ["72f3e","5ba64"]
5bf28 interests:National identity 4 ['72f3e', '5bf28'] See 4 ads matching ["72f3e","5bf28"]
63163 interests:Stop Illegal Immigration 4 ['72f3e', '63163'] See 4 ads matching ["72f3e","63163"]
6623a interests:Bill O'Reilly (political commentator) 4 ['72f3e', '6623a'] See 4 ads matching ["72f3e","6623a"]
6b2e6 interests:Julian Assange 4 ['72f3e', '6b2e6'] See 4 ads matching ["72f3e","6b2e6"]
6f57d interests:Politics and social issues 4 ['72f3e', '6f57d'] See 4 ads matching ["72f3e","6f57d"]
72024 interests:Mike Huckabee 4 ['72f3e', '72024'] See 4 ads matching ["72f3e","72024"]
8c874 interests:United States Constitution 4 ['72f3e', '8c874'] See 4 ads matching ["72f3e","8c874"]
902e5 placements:Right column on desktop computers 4 ['72f3e', '902e5'] See 4 ads matching ["72f3e","902e5"]
903a7 interests:Immigration law 4 ['72f3e', '903a7'] See 4 ads matching ["72f3e","903a7"]
9527f interests:Donald Trump 4 ['72f3e', '9527f'] See 4 ads matching ["72f3e","9527f"]
9528f interests:Christopher Hitchens 4 ['72f3e', '9528f'] See 4 ads matching ["72f3e","9528f"]
95484 interests:Republican Party (United States) 4 ['72f3e', '95484'] See 4 ads matching ["72f3e","95484"]
9f427 interests:Laura Ingraham 4 ['72f3e', '9f427'] See 4 ads matching ["72f3e","9f427"]
bbef6 interests:Deportation 4 ['72f3e', 'bbef6'] See 4 ads matching ["72f3e","bbef6"]
be927 interests:Foreign policy 4 ['72f3e', 'be927'] See 4 ads matching ["72f3e","be927"]
c0da0 interests:Old Glory 4 ['72f3e', 'c0da0'] See 4 ads matching ["72f3e","c0da0"]
c237c excluded_connections:Exclude people who like Secured Borders 4 ['72f3e', 'c237c'] See 4 ads matching ["72f3e","c237c"]
ca320 interests:Ron Paul 4 ['72f3e', 'ca320'] See 4 ads matching ["72f3e","ca320"]
cc5ed location_living_in:United States 4 ['72f3e', 'cc5ed'] See 4 ads matching ["72f3e","cc5ed"]
ccb7c interests:Sean Hannity 4 ['72f3e', 'ccb7c'] See 4 ads matching ["72f3e","ccb7c"]
d0251 interests:United States Department of Homeland Security 4 ['72f3e', 'd0251'] See 4 ads matching ["72f3e","d0251"]
e85a2 interests:Law enforcement 4 ['72f3e', 'e85a2'] See 4 ads matching ["72f3e","e85a2"]
f009b interests:Immigration to the United States 4 ['72f3e', 'f009b'] See 4 ads matching ["72f3e","f009b"]
f5427 age:14 - 65+ 2 ['72f3e', 'f5427'] See 2 ads matching ["72f3e","f5427"]
0d4bc location:united_states:Washington 1 ['72f3e', '0d4bc'] See 1 ads matching ["72f3e","0d4bc"]
1d2c1 placements:on pages: Right column on desktop computers 1 ['72f3e', '1d2c1'] See 1 ads matching ["72f3e","1d2c1"]
2f63d behaviors:facebook_access_(mobile):all mobile devices 1 ['72f3e', '2f63d'] See 1 ads matching ["72f3e","2f63d"]
407d7 interests:Veterans Day 1 ['72f3e', '407d7'] See 1 ads matching ["72f3e","407d7"]
5bb13 placements:Third-party Apps and Websites on mobile devices 1 ['72f3e', '5bb13'] See 1 ads matching ["72f3e","5bb13"]
b08d4 location:united_states:New York (+40 km) New York 1 ['72f3e', 'b08d4'] See 1 ads matching ["72f3e","b08d4"]
e786b interests:Remembrance Day 1 ['72f3e', 'e786b'] See 1 ads matching ["72f3e","e786b"]
Powered by Datasette · Query took 19.363ms