home / russian-ads

russian-ads

Custom SQL query returning 30 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
41b26 people_who_match:interests:Black Consciousness Movement 103 ['2db78', '41b26'] See 103 ads matching ["2db78","41b26"]
59d75 language:English (US) 103 ['2db78', '59d75'] See 103 ads matching ["2db78","59d75"]
5e075 language:English (UK) 103 ['2db78', '5e075'] See 103 ads matching ["2db78","5e075"]
6a38c people_who_match:interests:Stop Police Brutality 103 ['2db78', '6a38c'] See 103 ads matching ["2db78","6a38c"]
9a8c6 people_who_match:interests:Police misconduct 103 ['2db78', '9a8c6'] See 103 ads matching ["2db78","9a8c6"]
b1201 placements:News Feed on mobile devices 103 ['2db78', 'b1201'] See 103 ads matching ["2db78","b1201"]
d6347 people_who_match:interests:Black nationalism 103 ['2db78', 'd6347'] See 103 ads matching ["2db78","d6347"]
e128e people_who_match:interests:Black (Color) 103 ['2db78', 'e128e'] See 103 ads matching ["2db78","e128e"]
ec3ac location:United States 103 ['2db78', 'ec3ac'] See 103 ads matching ["2db78","ec3ac"]
e6200 placements:News Feed on desktop computers 102 ['2db78', 'e6200'] See 102 ads matching ["2db78","e6200"]
abb72 people_who_match:interests:African-American history 96 ['2db78', 'abb72'] See 96 ads matching ["2db78","abb72"]
4801f age:16 - 65+ 92 ['2db78', '4801f'] See 92 ads matching ["2db78","4801f"]
ebf60 people_who_match:interests:Pan-Africanism 90 ['2db78', 'ebf60'] See 90 ads matching ["2db78","ebf60"]
a1307 people_who_match:interests:African-American Civil Rights Movement (1954—68) 54 ['2db78', 'a1307'] See 54 ads matching ["2db78","a1307"]
a2d21 people_who_match:interests:African-American culture 54 ['2db78', 'a2d21'] See 54 ads matching ["2db78","a2d21"]
1d2ab people_who_match:interests:African—American Civil Rights Movement (1954—68) 47 ['2db78', '1d2ab'] See 47 ads matching ["2db78","1d2ab"]
91693 people_who_match:interests:African—American culture 47 ['2db78', '91693'] See 47 ads matching ["2db78","91693"]
13ab6 people_who_match:interests:Pan—Africanism 11 ['2db78', '13ab6'] See 11 ads matching ["2db78","13ab6"]
c3b9b age:16 — 65+ 11 ['2db78', 'c3b9b'] See 11 ads matching ["2db78","c3b9b"]
4225c people_who_match:interests:African—American history 5 ['2db78', '4225c'] See 5 ads matching ["2db78","4225c"]
3fbed people_who_match:interests:African-Amerioan history 2 ['2db78', '3fbed'] See 2 ads matching ["2db78","3fbed"]
487f2 people_who_match:interests:African-Amerioan Civil Rights Movement (1954—68) 2 ['2db78', '487f2'] See 2 ads matching ["2db78","487f2"]
a5ab5 people_who_match:interests:African-Amerioan culture 2 ['2db78', 'a5ab5'] See 2 ads matching ["2db78","a5ab5"]
b322d people_who_match:interests:Pan-Afrioanism 2 ['2db78', 'b322d'] See 2 ads matching ["2db78","b322d"]
42bf9 people_who_match:interests:Refugees of the Syrian Civil War 1 ['2db78', '42bf9'] See 1 ads matching ["2db78","42bf9"]
4accc placements:on pages: News Feed on desktop computers 1 ['2db78', '4accc'] See 1 ads matching ["2db78","4accc"]
60170 placements:Suggested Video on desktop computers 1 ['2db78', '60170'] See 1 ads matching ["2db78","60170"]
7a9b5 placements:Suggested Video on mobile devices 1 ['2db78', '7a9b5'] See 1 ads matching ["2db78","7a9b5"]
c4c8c people_who_match:interests:Syria-News 1 ['2db78', 'c4c8c'] See 1 ads matching ["2db78","c4c8c"]
f6c6f people_who_match:interests:Syria Charity 1 ['2db78', 'f6c6f'] See 1 ads matching ["2db78","f6c6f"]
Powered by Datasette · Query took 22.59ms