home / russian-ads

russian-ads

Custom SQL query returning 56 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 121 ['7ee91', 'b1201'] See 121 ads matching ["7ee91","b1201"]
18a64 people_who_match:interests:Black Power 101 ['7ee91', '18a64'] See 101 ads matching ["7ee91","18a64"]
59d75 language:English (US) 101 ['7ee91', '59d75'] See 101 ads matching ["7ee91","59d75"]
5e075 language:English (UK) 95 ['7ee91', '5e075'] See 95 ads matching ["7ee91","5e075"]
e6200 placements:News Feed on desktop computers 93 ['7ee91', 'e6200'] See 93 ads matching ["7ee91","e6200"]
4801f age:16 - 65+ 90 ['7ee91', '4801f'] See 90 ads matching ["7ee91","4801f"]
7c38f people_who_match:interests:Black Panther Party 81 ['7ee91', '7c38f'] See 81 ads matching ["7ee91","7c38f"]
31177 people_who_match:interests:Social justice 54 ['7ee91', '31177'] See 54 ads matching ["7ee91","31177"]
9295b people_who_match:interests:Racial equality 51 ['7ee91', '9295b'] See 51 ads matching ["7ee91","9295b"]
ebcbb location_living_in:united_states:New York (+25 mi) New York 35 ['7ee91', 'ebcbb'] See 35 ads matching ["7ee91","ebcbb"]
cc5ed location_living_in:United States 33 ['7ee91', 'cc5ed'] See 33 ads matching ["7ee91","cc5ed"]
ec3ac location:United States 25 ['7ee91', 'ec3ac'] See 25 ads matching ["7ee91","ec3ac"]
27369 people_who_match:interests:Malcolm X 22 ['7ee91', '27369'] See 22 ads matching ["7ee91","27369"]
6b827 excluded_connections:Exclude people who like Don't Shoot 20 ['7ee91', '6b827'] See 20 ads matching ["7ee91","6b827"]
862ed people_who_match:interests:Jr. 20 ['7ee91', '862ed'] See 20 ads matching ["7ee91","862ed"]
8a7ee people_who_match:interests:Martin Luther King 20 ['7ee91', '8a7ee'] See 20 ads matching ["7ee91","8a7ee"]
9848a people_who_match:interests:African- American Civil Rights Movement (1954—68) 20 ['7ee91', '9848a'] See 20 ads matching ["7ee91","9848a"]
48e80 location:united_states:Philadelphia, Pittsburgh Pennsylvania 19 ['7ee91', '48e80'] See 19 ads matching ["7ee91","48e80"]
abb72 people_who_match:interests:African-American history 19 ['7ee91', 'abb72'] See 19 ads matching ["7ee91","abb72"]
371f0 age:18 - 65+ 18 ['7ee91', '371f0'] See 18 ads matching ["7ee91","371f0"]
93975 people_who_match:interests:Angela Davis 18 ['7ee91', '93975'] See 18 ads matching ["7ee91","93975"]
c6b59 location_living_in:united_states:Minneapolis (+25 mi) Minnesota 17 ['7ee91', 'c6b59'] See 17 ads matching ["7ee91","c6b59"]
3502d placements:Instagram Feed 12 ['7ee91', '3502d'] See 12 ads matching ["7ee91","3502d"]
902e5 placements:Right column on desktop computers 11 ['7ee91', '902e5'] See 11 ads matching ["7ee91","902e5"]
1c31d age:18 - 54 10 ['7ee91', '1c31d'] See 10 ads matching ["7ee91","1c31d"]
b825d age:14 - 54 10 ['7ee91', 'b825d'] See 10 ads matching ["7ee91","b825d"]
4accc placements:on pages: News Feed on desktop computers 8 ['7ee91', '4accc'] See 8 ads matching ["7ee91","4accc"]
a340a placements:Instream Video on desktop computers 8 ['7ee91', 'a340a'] See 8 ads matching ["7ee91","a340a"]
d003e placements:Instream Video on mobile devices 8 ['7ee91', 'd003e'] See 8 ads matching ["7ee91","d003e"]
5b8a4 people_who_match:interests:Filming Cops 5 ['7ee91', '5b8a4'] See 5 ads matching ["7ee91","5b8a4"]
6a38c people_who_match:interests:Stop Police Brutality 5 ['7ee91', '6a38c'] See 5 ads matching ["7ee91","6a38c"]
a2b0d excluded_connections:Exclude people who like Watch the Police 5 ['7ee91', 'a2b0d'] See 5 ads matching ["7ee91","a2b0d"]
d83f4 people_who_match:interests:Police corruption 3 ['7ee91', 'd83f4'] See 3 ads matching ["7ee91","d83f4"]
5bb13 placements:Third-party Apps and Websites on mobile devices 2 ['7ee91', '5bb13'] See 2 ads matching ["7ee91","5bb13"]
d65e1 people_who_match:interests:Fight the Power 2 ['7ee91', 'd65e1'] See 2 ads matching ["7ee91","d65e1"]
e128e people_who_match:interests:Black (Color) 2 ['7ee91', 'e128e'] See 2 ads matching ["7ee91","e128e"]
e42ca people_who_match:interests:Slavery by Another Name 2 ['7ee91', 'e42ca'] See 2 ads matching ["7ee91","e42ca"]
045ef location:united_states:Louisiana 1 ['7ee91', '045ef'] See 1 ads matching ["7ee91","045ef"]
203d9 age:13 - 6O 1 ['7ee91', '203d9'] See 1 ads matching ["7ee91","203d9"]
3fbed people_who_match:interests:African-Amerioan history 1 ['7ee91', '3fbed'] See 1 ads matching ["7ee91","3fbed"]
4b220 age:13 — 40 1 ['7ee91', '4b220'] See 1 ads matching ["7ee91","4b220"]
598d9 people_who_match:interests:Racism in the United States 1 ['7ee91', '598d9'] See 1 ads matching ["7ee91","598d9"]
65b27 location:united_states:Cleveland (+40 km) Ohio 1 ['7ee91', '65b27'] See 1 ads matching ["7ee91","65b27"]
67cbf people_who_match:interests:Demonstration (protest) 1 ['7ee91', '67cbf'] See 1 ads matching ["7ee91","67cbf"]
711dd location:united_states:Chicago (+40 km) Illinois 1 ['7ee91', '711dd'] See 1 ads matching ["7ee91","711dd"]
89dda age:13 - 60 1 ['7ee91', '89dda'] See 1 ads matching ["7ee91","89dda"]
9dc02 location:united_states:Baltimore (+25 mi) Maryland 1 ['7ee91', '9dc02'] See 1 ads matching ["7ee91","9dc02"]
a1307 people_who_match:interests:African-American Civil Rights Movement (1954—68) 1 ['7ee91', 'a1307'] See 1 ads matching ["7ee91","a1307"]
aa8b2 age:13 - 45 1 ['7ee91', 'aa8b2'] See 1 ads matching ["7ee91","aa8b2"]
ac7e2 location:united_states:Baltimore (+40 km) Maryland 1 ['7ee91', 'ac7e2'] See 1 ads matching ["7ee91","ac7e2"]
bf5f5 people_who_match:interests:Anti-racism 1 ['7ee91', 'bf5f5'] See 1 ads matching ["7ee91","bf5f5"]
c0c52 age:18 - 40 1 ['7ee91', 'c0c52'] See 1 ads matching ["7ee91","c0c52"]
c182a location_living_in:united_states:Maryland 1 ['7ee91', 'c182a'] See 1 ads matching ["7ee91","c182a"]
ca0eb location:united_states:Baton Rouge (+40 km) Louisiana 1 ['7ee91', 'ca0eb'] See 1 ads matching ["7ee91","ca0eb"]
e0435 location:united_states:Minnesota 1 ['7ee91', 'e0435'] See 1 ads matching ["7ee91","e0435"]
e6b2f people_who_match:interests:Anti-Racist Action 1 ['7ee91', 'e6b2f'] See 1 ads matching ["7ee91","e6b2f"]
Powered by Datasette · Query took 57.191ms