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
ec3ac location:United States 98 ['398aa', 'ec3ac'] See 98 ads matching ["398aa","ec3ac"]
18789 interests:BuzzFeed 96 ['398aa', '18789'] See 96 ads matching ["398aa","18789"]
49bfd interests:9GAG 96 ['398aa', '49bfd'] See 96 ads matching ["398aa","49bfd"]
b1201 placements:News Feed on mobile devices 96 ['398aa', 'b1201'] See 96 ads matching ["398aa","b1201"]
e6200 placements:News Feed on desktop computers 96 ['398aa', 'e6200'] See 96 ads matching ["398aa","e6200"]
371f0 age:18 - 65+ 92 ['398aa', '371f0'] See 92 ads matching ["398aa","371f0"]
7b150 age:18 — 65+ 5 ['398aa', '7b150'] See 5 ads matching ["398aa","7b150"]
3502d placements:Instagram Feed 2 ['398aa', '3502d'] See 2 ads matching ["398aa","3502d"]
59d75 language:English (US) 2 ['398aa', '59d75'] See 2 ads matching ["398aa","59d75"]
61c1a age:18 - 50 1 ['398aa', '61c1a'] See 1 ads matching ["398aa","61c1a"]
6f57d interests:Politics and social issues 1 ['398aa', '6f57d'] See 1 ads matching ["398aa","6f57d"]
94d51 interests:Imgur 1 ['398aa', '94d51'] See 1 ads matching ["398aa","94d51"]
b5747 interests:Reddit 1 ['398aa', 'b5747'] See 1 ads matching ["398aa","b5747"]
e5c56 interests:Government 1 ['398aa', 'e5c56'] See 1 ads matching ["398aa","e5c56"]
Powered by Datasette · Query took 45.431ms