home / russian-ads

russian-ads

Custom SQL query returning 24 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 3 ['1073c', '878ce', '16102'] See 3 ads matching ["1073c","878ce","16102"]
1f895 interests:Culture 3 ['1073c', '878ce', '1f895'] See 3 ads matching ["1073c","878ce","1f895"]
3e5f6 interests:Martin Luther King III 3 ['1073c', '878ce', '3e5f6'] See 3 ads matching ["1073c","878ce","3e5f6"]
59d75 language:English (US) 3 ['1073c', '878ce', '59d75'] See 3 ads matching ["1073c","878ce","59d75"]
5c628 interests:African culture 3 ['1073c', '878ce', '5c628'] See 3 ads matching ["1073c","878ce","5c628"]
5e075 language:English (UK) 3 ['1073c', '878ce', '5e075'] See 3 ads matching ["1073c","878ce","5e075"]
87fac interests:Stop Police Brutality 3 ['1073c', '878ce', '87fac'] See 3 ads matching ["1073c","878ce","87fac"]
8de7c interests:Angela Davis 3 ['1073c', '878ce', '8de7c'] See 3 ads matching ["1073c","878ce","8de7c"]
a5df2 interests:Slavery in the United States 3 ['1073c', '878ce', 'a5df2'] See 3 ads matching ["1073c","878ce","a5df2"]
aabe0 interests:Black Consciousness Movement 3 ['1073c', '878ce', 'aabe0'] See 3 ads matching ["1073c","878ce","aabe0"]
ade9f interests:End Slavery Now 3 ['1073c', '878ce', 'ade9f'] See 3 ads matching ["1073c","878ce","ade9f"]
b1201 placements:News Feed on mobile devices 3 ['1073c', '878ce', 'b1201'] See 3 ads matching ["1073c","878ce","b1201"]
c3780 interests:Black is beautiful 3 ['1073c', '878ce', 'c3780'] See 3 ads matching ["1073c","878ce","c3780"]
d4179 interests:African-American culture 3 ['1073c', '878ce', 'd4179'] See 3 ads matching ["1073c","878ce","d4179"]
d582c interests:Equal opportunity 3 ['1073c', '878ce', 'd582c'] See 3 ads matching ["1073c","878ce","d582c"]
e17ba interests:Malcolm X Memorial Foundation 3 ['1073c', '878ce', 'e17ba'] See 3 ads matching ["1073c","878ce","e17ba"]
e5284 interests:Malcolm X Grassroots Movement 3 ['1073c', '878ce', 'e5284'] See 3 ads matching ["1073c","878ce","e5284"]
e6200 placements:News Feed on desktop computers 3 ['1073c', '878ce', 'e6200'] See 3 ads matching ["1073c","878ce","e6200"]
e9db4 interests:Justice 3 ['1073c', '878ce', 'e9db4'] See 3 ads matching ["1073c","878ce","e9db4"]
ec3ac location:United States 3 ['1073c', '878ce', 'ec3ac'] See 3 ads matching ["1073c","878ce","ec3ac"]
4801f age:16 - 65+ 2 ['1073c', '878ce', '4801f'] See 2 ads matching ["1073c","878ce","4801f"]
cbcce interests:Anti-discrimination 2 ['1073c', '878ce', 'cbcce'] See 2 ads matching ["1073c","878ce","cbcce"]
32c58 interests:Anti-disorimination 1 ['1073c', '878ce', '32c58'] See 1 ads matching ["1073c","878ce","32c58"]
c3b9b age:16 — 65+ 1 ['1073c', '878ce', 'c3b9b'] See 1 ads matching ["1073c","878ce","c3b9b"]
Powered by Datasette · Query took 22.383ms