home / russian-ads

russian-ads

Custom SQL query returning 9 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
b1201 placements:News Feed on mobile devices 92 ['18789', '371f0', 'b1201'] See 92 ads matching ["18789","371f0","b1201"]
e6200 placements:News Feed on desktop computers 92 ['18789', '371f0', 'e6200'] See 92 ads matching ["18789","371f0","e6200"]
ec3ac location:United States 92 ['18789', '371f0', 'ec3ac'] See 92 ads matching ["18789","371f0","ec3ac"]
398aa interests:CollegeHumor 91 ['18789', '371f0', '398aa'] See 91 ads matching ["18789","371f0","398aa"]
49bfd interests:9GAG 91 ['18789', '371f0', '49bfd'] See 91 ads matching ["18789","371f0","49bfd"]
423ed interests:Hobbies and activities 1 ['18789', '371f0', '423ed'] See 1 ads matching ["18789","371f0","423ed"]
674d7 excluded_connections:Exclude people who like Memopolis 1 ['18789', '371f0', '674d7'] See 1 ads matching ["18789","371f0","674d7"]
902e5 placements:Right column on desktop computers 1 ['18789', '371f0', '902e5'] See 1 ads matching ["18789","371f0","902e5"]
e3fd2 interests:Entertainment 1 ['18789', '371f0', 'e3fd2'] See 1 ads matching ["18789","371f0","e3fd2"]
Powered by Datasette · Query took 28.734ms