home / russian-ads

russian-ads

Custom SQL query returning 19 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
16102 interests:Human rights 9 ['1073c', '9d04d', '16102'] See 9 ads matching ["1073c","9d04d","16102"]
235b1 interests:Pan Africanist Congress of Azania 9 ['1073c', '9d04d', '235b1'] See 9 ads matching ["1073c","9d04d","235b1"]
2a6ea interests:Violence prevention 9 ['1073c', '9d04d', '2a6ea'] See 9 ads matching ["1073c","9d04d","2a6ea"]
8e7b3 interests:Jr. 9 ['1073c', '9d04d', '8e7b3'] See 9 ads matching ["1073c","9d04d","8e7b3"]
a0f55 interests:Malcolm X 9 ['1073c', '9d04d', 'a0f55'] See 9 ads matching ["1073c","9d04d","a0f55"]
b1201 placements:News Feed on mobile devices 9 ['1073c', '9d04d', 'b1201'] See 9 ads matching ["1073c","9d04d","b1201"]
cc5ed location_living_in:United States 9 ['1073c', '9d04d', 'cc5ed'] See 9 ads matching ["1073c","9d04d","cc5ed"]
d6ade interests:Martin Luther King 9 ['1073c', '9d04d', 'd6ade'] See 9 ads matching ["1073c","9d04d","d6ade"]
e6200 placements:News Feed on desktop computers 9 ['1073c', '9d04d', 'e6200'] See 9 ads matching ["1073c","9d04d","e6200"]
e9db4 interests:Justice 9 ['1073c', '9d04d', 'e9db4'] See 9 ads matching ["1073c","9d04d","e9db4"]
cbcce interests:Anti-discrimination 8 ['1073c', '9d04d', 'cbcce'] See 8 ads matching ["1073c","9d04d","cbcce"]
1c31d age:18 - 54 5 ['1073c', '9d04d', '1c31d'] See 5 ads matching ["1073c","9d04d","1c31d"]
8da48 interests:Nelson Mandela 5 ['1073c', '9d04d', '8da48'] See 5 ads matching ["1073c","9d04d","8da48"]
f6590 interests:Muhammad Ali 5 ['1073c', '9d04d', 'f6590'] See 5 ads matching ["1073c","9d04d","f6590"]
371f0 age:18 - 65+ 4 ['1073c', '9d04d', '371f0'] See 4 ads matching ["1073c","9d04d","371f0"]
51b28 interests:Black Women Are Beautiful 2 ['1073c', '9d04d', '51b28'] See 2 ads matching ["1073c","9d04d","51b28"]
d4e6c interests:Melanin 2 ['1073c', '9d04d', 'd4e6c'] See 2 ads matching ["1073c","9d04d","d4e6c"]
2cd39 interests:Anti- discrimination 1 ['1073c', '9d04d', '2cd39'] See 1 ads matching ["1073c","9d04d","2cd39"]
d461c interests:T- shirt 1 ['1073c', '9d04d', 'd461c'] See 1 ads matching ["1073c","9d04d","d461c"]
Powered by Datasette · Query took 21.384ms