home / russian-ads

russian-ads

Custom SQL query returning 14 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 47 ['2db78', '1d2ab', '41b26'] See 47 ads matching ["2db78","1d2ab","41b26"]
4801f age:16 - 65+ 47 ['2db78', '1d2ab', '4801f'] See 47 ads matching ["2db78","1d2ab","4801f"]
59d75 language:English (US) 47 ['2db78', '1d2ab', '59d75'] See 47 ads matching ["2db78","1d2ab","59d75"]
5e075 language:English (UK) 47 ['2db78', '1d2ab', '5e075'] See 47 ads matching ["2db78","1d2ab","5e075"]
6a38c people_who_match:interests:Stop Police Brutality 47 ['2db78', '1d2ab', '6a38c'] See 47 ads matching ["2db78","1d2ab","6a38c"]
91693 people_who_match:interests:African—American culture 47 ['2db78', '1d2ab', '91693'] See 47 ads matching ["2db78","1d2ab","91693"]
9a8c6 people_who_match:interests:Police misconduct 47 ['2db78', '1d2ab', '9a8c6'] See 47 ads matching ["2db78","1d2ab","9a8c6"]
abb72 people_who_match:interests:African-American history 47 ['2db78', '1d2ab', 'abb72'] See 47 ads matching ["2db78","1d2ab","abb72"]
b1201 placements:News Feed on mobile devices 47 ['2db78', '1d2ab', 'b1201'] See 47 ads matching ["2db78","1d2ab","b1201"]
d6347 people_who_match:interests:Black nationalism 47 ['2db78', '1d2ab', 'd6347'] See 47 ads matching ["2db78","1d2ab","d6347"]
e128e people_who_match:interests:Black (Color) 47 ['2db78', '1d2ab', 'e128e'] See 47 ads matching ["2db78","1d2ab","e128e"]
e6200 placements:News Feed on desktop computers 47 ['2db78', '1d2ab', 'e6200'] See 47 ads matching ["2db78","1d2ab","e6200"]
ebf60 people_who_match:interests:Pan-Africanism 47 ['2db78', '1d2ab', 'ebf60'] See 47 ads matching ["2db78","1d2ab","ebf60"]
ec3ac location:United States 47 ['2db78', '1d2ab', 'ec3ac'] See 47 ads matching ["2db78","1d2ab","ec3ac"]
Powered by Datasette · Query took 18.87ms