home / russian-ads

russian-ads

Custom SQL query returning 29 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
87fac interests:Stop Police Brutality 14 ['1073c', '550f6', '87fac'] See 14 ads matching ["1073c","550f6","87fac"]
aabe0 interests:Black Consciousness Movement 14 ['1073c', '550f6', 'aabe0'] See 14 ads matching ["1073c","550f6","aabe0"]
b1201 placements:News Feed on mobile devices 14 ['1073c', '550f6', 'b1201'] See 14 ads matching ["1073c","550f6","b1201"]
e6200 placements:News Feed on desktop computers 14 ['1073c', '550f6', 'e6200'] See 14 ads matching ["1073c","550f6","e6200"]
17183 interests:Black nationalism 11 ['1073c', '550f6', '17183'] See 11 ads matching ["1073c","550f6","17183"]
3e5f6 interests:Martin Luther King III 11 ['1073c', '550f6', '3e5f6'] See 11 ads matching ["1073c","550f6","3e5f6"]
59d75 language:English (US) 11 ['1073c', '550f6', '59d75'] See 11 ads matching ["1073c","550f6","59d75"]
5e075 language:English (UK) 11 ['1073c', '550f6', '5e075'] See 11 ads matching ["1073c","550f6","5e075"]
ec3ac location:United States 11 ['1073c', '550f6', 'ec3ac'] See 11 ads matching ["1073c","550f6","ec3ac"]
343d8 interests:African- American culture 10 ['1073c', '550f6', '343d8'] See 10 ads matching ["1073c","550f6","343d8"]
4801f age:16 - 65+ 10 ['1073c', '550f6', '4801f'] See 10 ads matching ["1073c","550f6","4801f"]
96bbd interests:Pan-Africanism 10 ['1073c', '550f6', '96bbd'] See 10 ads matching ["1073c","550f6","96bbd"]
313af interests:African-American Civil Rights Movement (1954—68) 9 ['1073c', '550f6', '313af'] See 9 ads matching ["1073c","550f6","313af"]
f9cc2 interests:African- American history 7 ['1073c', '550f6', 'f9cc2'] See 7 ads matching ["1073c","550f6","f9cc2"]
e0460 interests:African—American Civil Rights Movement (1954—68) 5 ['1073c', '550f6', 'e0460'] See 5 ads matching ["1073c","550f6","e0460"]
02db8 interests:African— American history 4 ['1073c', '550f6', '02db8'] See 4 ads matching ["1073c","550f6","02db8"]
371f0 age:18 - 65+ 3 ['1073c', '550f6', '371f0'] See 3 ads matching ["1073c","550f6","371f0"]
40c27 interests:Police Brutality is a Crime 3 ['1073c', '550f6', '40c27'] See 3 ads matching ["1073c","550f6","40c27"]
8e7b3 interests:Jr. 3 ['1073c', '550f6', '8e7b3'] See 3 ads matching ["1073c","550f6","8e7b3"]
a06cb interests:Police brutality in the United States 3 ['1073c', '550f6', 'a06cb'] See 3 ads matching ["1073c","550f6","a06cb"]
a0f55 interests:Malcolm X 3 ['1073c', '550f6', 'a0f55'] See 3 ads matching ["1073c","550f6","a0f55"]
cc5ed location_living_in:United States 3 ['1073c', '550f6', 'cc5ed'] See 3 ads matching ["1073c","550f6","cc5ed"]
d6ade interests:Martin Luther King 3 ['1073c', '550f6', 'd6ade'] See 3 ads matching ["1073c","550f6","d6ade"]
ebe26 interests:African-American history 3 ['1073c', '550f6', 'ebe26'] See 3 ads matching ["1073c","550f6","ebe26"]
d4179 interests:African-American culture 2 ['1073c', '550f6', 'd4179'] See 2 ads matching ["1073c","550f6","d4179"]
afc04 interests:Pan—Africanism 1 ['1073c', '550f6', 'afc04'] See 1 ads matching ["1073c","550f6","afc04"]
c3b9b age:16 — 65+ 1 ['1073c', '550f6', 'c3b9b'] See 1 ads matching ["1073c","550f6","c3b9b"]
c4200 interests:African—American culture 1 ['1073c', '550f6', 'c4200'] See 1 ads matching ["1073c","550f6","c4200"]
e6034 interests:African— American culture 1 ['1073c', '550f6', 'e6034'] See 1 ads matching ["1073c","550f6","e6034"]
Powered by Datasette · Query took 28.803ms