home / russian-ads

russian-ads

Custom SQL query returning 30 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
007df people_who_match:interests:Latin hip hop 184 ['5f10e', '007df'] See 184 ads matching ["5f10e","007df"]
60626 people_who_match:interests:Hispanidad 184 ['5f10e', '60626'] See 184 ads matching ["5f10e","60626"]
b1201 placements:News Feed on mobile devices 184 ['5f10e', 'b1201'] See 184 ads matching ["5f10e","b1201"]
cc5ed location_living_in:United States 184 ['5f10e', 'cc5ed'] See 184 ads matching ["5f10e","cc5ed"]
d3539 people_who_match:interests:Chicano Movement 184 ['5f10e', 'd3539'] See 184 ads matching ["5f10e","d3539"]
e6200 placements:News Feed on desktop computers 184 ['5f10e', 'e6200'] See 184 ads matching ["5f10e","e6200"]
839f9 people_who_match:interests:Lowrider 183 ['5f10e', '839f9'] See 183 ads matching ["5f10e","839f9"]
4f89e people_who_match:interests:La Raza 181 ['5f10e', '4f89e'] See 181 ads matching ["5f10e","4f89e"]
371f0 age:18 - 65+ 179 ['5f10e', '371f0'] See 179 ads matching ["5f10e","371f0"]
ffca2 people_who_match:interests:Chicano rap 176 ['5f10e', 'ffca2'] See 176 ads matching ["5f10e","ffca2"]
4a957 people_who_match:interests:Mexican Pride 67 ['5f10e', '4a957'] See 67 ads matching ["5f10e","4a957"]
5849a people_who_match:interests:Mexican american culture 67 ['5f10e', '5849a'] See 67 ads matching ["5f10e","5849a"]
5dcb2 people_who_match:interests:Chicano 67 ['5f10e', '5dcb2'] See 67 ads matching ["5f10e","5dcb2"]
8b401 people_who_match:interests:Latino culture 67 ['5f10e', '8b401'] See 67 ads matching ["5f10e","8b401"]
eab5e people_who_match:interests:Hispanic culture 67 ['5f10e', 'eab5e'] See 67 ads matching ["5f10e","eab5e"]
d9d8c people_who_match:interests:Hispanic and latino american culture 64 ['5f10e', 'd9d8c'] See 64 ads matching ["5f10e","d9d8c"]
81cf6 people_who_match:interests:Being Chicano 29 ['5f10e', '81cf6'] See 29 ads matching ["5f10e","81cf6"]
68743 people_who_match:interests:Chicano rep 7 ['5f10e', '68743'] See 7 ads matching ["5f10e","68743"]
2ed33 excluded_connections:Exclude people who like Brown Power 3 ['5f10e', '2ed33'] See 3 ads matching ["5f10e","2ed33"]
59d75 language:English (US) 3 ['5f10e', '59d75'] See 3 ads matching ["5f10e","59d75"]
5e075 language:English (UK) 3 ['5f10e', '5e075'] See 3 ads matching ["5f10e","5e075"]
7b150 age:18 — 65+ 3 ['5f10e', '7b150'] See 3 ads matching ["5f10e","7b150"]
902e5 placements:Right column on desktop computers 3 ['5f10e', '902e5'] See 3 ads matching ["5f10e","902e5"]
a56e6 and_must_also_match:interests:La Raza 3 ['5f10e', 'a56e6'] See 3 ads matching ["5f10e","a56e6"]
d2747 language:Spanish 3 ['5f10e', 'd2747'] See 3 ads matching ["5f10e","d2747"]
f9d07 language:Spanish (Spain) 3 ['5f10e', 'f9d07'] See 3 ads matching ["5f10e","f9d07"]
4801f age:16 - 65+ 2 ['5f10e', '4801f'] See 2 ads matching ["5f10e","4801f"]
e395d people_who_match:interests:Hispanic and latino americah culture 2 ['5f10e', 'e395d'] See 2 ads matching ["5f10e","e395d"]
9d28e people_who_match:interests:Hispanic: and latino american culture 1 ['5f10e', '9d28e'] See 1 ads matching ["5f10e","9d28e"]
f4a45 people_who_match:interests:Lowrideri Chicano rap 1 ['5f10e', 'f4a45'] See 1 ads matching ["5f10e","f4a45"]
Powered by Datasette · Query took 102.429ms