home / russian-ads

russian-ads

Custom SQL query returning 44 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) 92 ['40ff5', '59d75'] See 92 ads matching ["40ff5","59d75"]
5e075 language:English (UK) 85 ['40ff5', '5e075'] See 85 ads matching ["40ff5","5e075"]
b1201 placements:News Feed on mobile devices 84 ['40ff5', 'b1201'] See 84 ads matching ["40ff5","b1201"]
e6200 placements:News Feed on desktop computers 84 ['40ff5', 'e6200'] See 84 ads matching ["40ff5","e6200"]
ec3ac location:United States 70 ['40ff5', 'ec3ac'] See 70 ads matching ["40ff5","ec3ac"]
37b6b interests:HuffPost Black Voices 64 ['40ff5', '37b6b'] See 64 ads matching ["40ff5","37b6b"]
6fba5 excluded_connections:Exclude people who like Black Matters 62 ['40ff5', '6fba5'] See 62 ads matching ["40ff5","6fba5"]
1c31d age:18 - 54 57 ['40ff5', '1c31d'] See 57 ads matching ["40ff5","1c31d"]
eaba0 interests:BlackNews.com 53 ['40ff5', 'eaba0'] See 53 ads matching ["40ff5","eaba0"]
371f0 age:18 - 65+ 32 ['40ff5', '371f0'] See 32 ads matching ["40ff5","371f0"]
e95f6 excluded_connections:Exclude people who like Blacktivist 19 ['40ff5', 'e95f6'] See 19 ads matching ["40ff5","e95f6"]
17a50 location:united_states:Milwaukee (+25 mi) Wisconsin 17 ['40ff5', '17a50'] See 17 ads matching ["40ff5","17a50"]
24da8 location:united_states:New Orleans (+50 mi) Louisiana 17 ['40ff5', '24da8'] See 17 ads matching ["40ff5","24da8"]
28463 location:united_states:Atlanta (+25 mi) Georgia 17 ['40ff5', '28463'] See 17 ads matching ["40ff5","28463"]
36203 location:united_states:Ferguson (+50 mi), St. Louis (+50 mi) Missouri 17 ['40ff5', '36203'] See 17 ads matching ["40ff5","36203"]
d5b86 location:united_states:Baltimore (+50 mi), Oakland (+50 mi) Maryland 17 ['40ff5', 'd5b86'] See 17 ads matching ["40ff5","d5b86"]
16102 interests:Human rights 13 ['40ff5', '16102'] See 13 ads matching ["40ff5","16102"]
59677 interests:Humanitarian aid 11 ['40ff5', '59677'] See 11 ads matching ["40ff5","59677"]
aa5af interests:Humanitarianism 11 ['40ff5', 'aa5af'] See 11 ads matching ["40ff5","aa5af"]
3502d placements:Instagram Feed 10 ['40ff5', '3502d'] See 10 ads matching ["40ff5","3502d"]
74353 interests:BIackNews.com 10 ['40ff5', '74353'] See 10 ads matching ["40ff5","74353"]
508a5 location:united_states:South Carolina 3 ['40ff5', '508a5'] See 3 ads matching ["40ff5","508a5"]
f5427 age:14 - 65+ 3 ['40ff5', 'f5427'] See 3 ads matching ["40ff5","f5427"]
99241 people_who_match:interests: 2 ['40ff5', '99241'] See 2 ads matching ["40ff5","99241"]
a0f55 interests:Malcolm X 2 ['40ff5', 'a0f55'] See 2 ads matching ["40ff5","a0f55"]
06abe location:united_states:Ferguson (+40 km) Missouri 1 ['40ff5', '06abe'] See 1 ads matching ["40ff5","06abe"]
10536 interests:Tax 1 ['40ff5', '10536'] See 1 ads matching ["40ff5","10536"]
1073c interests:Black (Color) 1 ['40ff5', '1073c'] See 1 ads matching ["40ff5","1073c"]
4698a age:16 - 40 1 ['40ff5', '4698a'] See 1 ads matching ["40ff5","4698a"]
4acaf politics:Likely to engage with political content (liberal) 1 ['40ff5', '4acaf'] See 1 ads matching ["40ff5","4acaf"]
560cb location:united_states:Cleveland (+50 km) Ohio 1 ['40ff5', '560cb'] See 1 ads matching ["40ff5","560cb"]
5bb13 placements:Third-party Apps and Websites on mobile devices 1 ['40ff5', '5bb13'] See 1 ads matching ["40ff5","5bb13"]
5d3ab interests:TV talkshows 1 ['40ff5', '5d3ab'] See 1 ads matching ["40ff5","5d3ab"]
6b827 excluded_connections:Exclude people who like Don't Shoot 1 ['40ff5', '6b827'] See 1 ads matching ["40ff5","6b827"]
711dd location:united_states:Chicago (+40 km) Illinois 1 ['40ff5', '711dd'] See 1 ads matching ["40ff5","711dd"]
7ceb1 interests:Cop Block 1 ['40ff5', '7ceb1'] See 1 ads matching ["40ff5","7ceb1"]
8fb0c behaviors: 1 ['40ff5', '8fb0c'] See 1 ads matching ["40ff5","8fb0c"]
9fe34 people_who_match:interests:Understanding racial segregation in the united states 1 ['40ff5', '9fe34'] See 1 ads matching ["40ff5","9fe34"]
a2353 location_living_in:united_states:Georgia 1 ['40ff5', 'a2353'] See 1 ads matching ["40ff5","a2353"]
b83a3 people_who_match:interests:American Black Film Festival 1 ['40ff5', 'b83a3'] See 1 ads matching ["40ff5","b83a3"]
ba9f6 behaviors:Field of study: African-American history 1 ['40ff5', 'ba9f6'] See 1 ads matching ["40ff5","ba9f6"]
babaf people_who_match:interests:BlackNews.com or HuffPost Black Voices 1 ['40ff5', 'babaf'] See 1 ads matching ["40ff5","babaf"]
c3973 people_who_match:interests:Cop Block or HuffPost Black Voices 1 ['40ff5', 'c3973'] See 1 ads matching ["40ff5","c3973"]
d442d age:13 - 27 1 ['40ff5', 'd442d'] See 1 ads matching ["40ff5","d442d"]
Powered by Datasette · Query took 77.024ms