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
371f0 age:18 - 65+ 163 ['cb431', '371f0'] See 163 ads matching ["cb431","371f0"]
b1201 placements:News Feed on mobile devices 163 ['cb431', 'b1201'] See 163 ads matching ["cb431","b1201"]
d5740 people_who_match:Friends of connections: Friends of people who are connected to Black Matters 163 ['cb431', 'd5740'] See 163 ads matching ["cb431","d5740"]
e6200 placements:News Feed on desktop computers 163 ['cb431', 'e6200'] See 163 ads matching ["cb431","e6200"]
4e91f location:united_states:Ferguson, St. Louis Missouri 68 ['cb431', '4e91f'] See 68 ads matching ["cb431","4e91f"]
72c69 location:united_states:Baltimore Maryland 68 ['cb431', '72c69'] See 68 ads matching ["cb431","72c69"]
75114 location:united_states:Cleveland Ohio 68 ['cb431', '75114'] See 68 ads matching ["cb431","75114"]
348d4 location_living_in:united_states:Baltimore Maryland; Ferguson 45 ['cb431', '348d4'] See 45 ads matching ["cb431","348d4"]
34ac6 location_living_in:united_states:St. Louis Missouri; Cleveland Ohio 45 ['cb431', '34ac6'] See 45 ads matching ["cb431","34ac6"]
cc5ed location_living_in:United States 25 ['cb431', 'cc5ed'] See 25 ads matching ["cb431","cc5ed"]
e8dc0 location:united_states:New York New York 18 ['cb431', 'e8dc0'] See 18 ads matching ["cb431","e8dc0"]
53b62 location_living_in:united_states:Cleveland (+50 mi) Ohio 3 ['cb431', '53b62'] See 3 ads matching ["cb431","53b62"]
df3d4 location:united_states:Atlanta Georgia 3 ['cb431', 'df3d4'] See 3 ads matching ["cb431","df3d4"]
b08d4 location:united_states:New York (+40 km) New York 1 ['cb431', 'b08d4'] See 1 ads matching ["cb431","b08d4"]
Powered by Datasette · Query took 79.588ms