home / russian-ads

russian-ads

Custom SQL query returning 28 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) 128 ['6fba5', '59d75'] See 128 ads matching ["6fba5","59d75"]
5e075 language:English (UK) 128 ['6fba5', '5e075'] See 128 ads matching ["6fba5","5e075"]
b1201 placements:News Feed on mobile devices 128 ['6fba5', 'b1201'] See 128 ads matching ["6fba5","b1201"]
e6200 placements:News Feed on desktop computers 128 ['6fba5', 'e6200'] See 128 ads matching ["6fba5","e6200"]
371f0 age:18 - 65+ 72 ['6fba5', '371f0'] See 72 ads matching ["6fba5","371f0"]
37b6b interests:HuffPost Black Voices 62 ['6fba5', '37b6b'] See 62 ads matching ["6fba5","37b6b"]
40ff5 behaviors:African American (US) 62 ['6fba5', '40ff5'] See 62 ads matching ["6fba5","40ff5"]
ec3ac location:United States 59 ['6fba5', 'ec3ac'] See 59 ads matching ["6fba5","ec3ac"]
1c31d age:18 - 54 56 ['6fba5', '1c31d'] See 56 ads matching ["6fba5","1c31d"]
dd761 location:united_states:Baltimore (+20 km) Maryland 52 ['6fba5', 'dd761'] See 52 ads matching ["6fba5","dd761"]
eaba0 interests:BlackNews.com 52 ['6fba5', 'eaba0'] See 52 ads matching ["6fba5","eaba0"]
a1ac8 location:united_states:St. Louis (+20 km) Missouri 42 ['6fba5', 'a1ac8'] See 42 ads matching ["6fba5","a1ac8"]
1ef30 location:united_states:Atlanta (+20 km) Georgia 36 ['6fba5', '1ef30'] See 36 ads matching ["6fba5","1ef30"]
d5d33 location:united_states:Richmond (+20 km) Virginia 22 ['6fba5', 'd5d33'] See 22 ads matching ["6fba5","d5d33"]
0c41e location:united_states:St. Louis (+50 km) Missouri 18 ['6fba5', '0c41e'] See 18 ads matching ["6fba5","0c41e"]
18d8d location:united_states:Virginia 14 ['6fba5', '18d8d'] See 14 ads matching ["6fba5","18d8d"]
c448c location:united_states:Maryland 14 ['6fba5', 'c448c'] See 14 ads matching ["6fba5","c448c"]
74353 interests:BIackNews.com 10 ['6fba5', '74353'] See 10 ads matching ["6fba5","74353"]
69e43 location:united_states:Ferguson (+20 km), St. Louis (+20 km) Missouri 6 ['6fba5', '69e43'] See 6 ads matching ["6fba5","69e43"]
0250c location:united_states:Cleveland (+20 km) Ohio 4 ['6fba5', '0250c'] See 4 ads matching ["6fba5","0250c"]
04bf8 location:united_states:Milwaukee (+20 km) Wisconsin 4 ['6fba5', '04bf8'] See 4 ads matching ["6fba5","04bf8"]
4ba69 location:united_states:Memphis (+20 km) Tennessee 4 ['6fba5', '4ba69'] See 4 ads matching ["6fba5","4ba69"]
52fc7 location:united_states:New Orleans (+20 km) Louisiana 4 ['6fba5', '52fc7'] See 4 ads matching ["6fba5","52fc7"]
c71fb location:united_states:Philadelphia (+20 km) Pennsylvania 4 ['6fba5', 'c71fb'] See 4 ads matching ["6fba5","c71fb"]
cde0d location:united_states:Washington (+20 km) District of Columbia 4 ['6fba5', 'cde0d'] See 4 ads matching ["6fba5","cde0d"]
d2054 location:united_states:Chester (+20 km) Arkansas 4 ['6fba5', 'd2054'] See 4 ads matching ["6fba5","d2054"]
508a5 location:united_states:South Carolina 3 ['6fba5', '508a5'] See 3 ads matching ["6fba5","508a5"]
5bb13 placements:Third-party Apps and Websites on mobile devices 1 ['6fba5', '5bb13'] See 1 ads matching ["6fba5","5bb13"]
Powered by Datasette · Query took 62.449ms