home / russian-ads

russian-ads

Custom SQL query returning 25 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
59d75 language:English (US) 99 ['26bad', '59d75'] See 99 ads matching ["26bad","59d75"]
5e075 language:English (UK) 99 ['26bad', '5e075'] See 99 ads matching ["26bad","5e075"]
e6200 placements:News Feed on desktop computers 99 ['26bad', 'e6200'] See 99 ads matching ["26bad","e6200"]
ec3ac location:United States 99 ['26bad', 'ec3ac'] See 99 ads matching ["26bad","ec3ac"]
b043e and_must_also_match:interests:SoundCloud 46 ['26bad', 'b043e'] See 46 ads matching ["26bad","b043e"]
21b44 age:15 - 25 44 ['26bad', '21b44'] See 44 ads matching ["26bad","21b44"]
1bffd and_must_also_match:interests:Last.fm 38 ['26bad', '1bffd'] See 38 ads matching ["26bad","1bffd"]
3fd8f and_must_also_match:interests:Grooveshark 30 ['26bad', '3fd8f'] See 30 ads matching ["26bad","3fd8f"]
6fd4d and_must_also_match:interests:Vevo 30 ['26bad', '6fd4d'] See 30 ads matching ["26bad","6fd4d"]
b6530 and_must_also_match:interests:Google Play Music 30 ['26bad', 'b6530'] See 30 ads matching ["26bad","b6530"]
00d26 gender:Female 28 ['26bad', '00d26'] See 28 ads matching ["26bad","00d26"]
9dfd5 age:13 - 30 25 ['26bad', '9dfd5'] See 25 ads matching ["26bad","9dfd5"]
bf1e7 gender:Male 25 ['26bad', 'bf1e7'] See 25 ads matching ["26bad","bf1e7"]
929ee and_must_also_match:interests:Shazam (service) 22 ['26bad', '929ee'] See 22 ads matching ["26bad","929ee"]
cd1a8 and_must_also_match:interests:Free software Interests: Music 20 ['26bad', 'cd1a8'] See 20 ads matching ["26bad","cd1a8"]
658c9 and_must_also_match:interests:Free software 17 ['26bad', '658c9'] See 17 ads matching ["26bad","658c9"]
77f6e and_must_also_match:interests:free music 17 ['26bad', '77f6e'] See 17 ads matching ["26bad","77f6e"]
31a58 and_must_also_match:interests:Apple Music 16 ['26bad', '31a58'] See 16 ads matching ["26bad","31a58"]
56921 and_must_also_match:interests:Music 16 ['26bad', '56921'] See 16 ads matching ["26bad","56921"]
d7ff4 and_must_also_match:interests:Spotify 16 ['26bad', 'd7ff4'] See 16 ads matching ["26bad","d7ff4"]
ea164 age:15 - 30 14 ['26bad', 'ea164'] See 14 ads matching ["26bad","ea164"]
3958e and_must_also_match:interests:Music Interests: Free software 8 ['26bad', '3958e'] See 8 ads matching ["26bad","3958e"]
aa8b2 age:13 - 45 8 ['26bad', 'aa8b2'] See 8 ads matching ["26bad","aa8b2"]
ad989 age:14 - 17 8 ['26bad', 'ad989'] See 8 ads matching ["26bad","ad989"]
ded2a and_must_also_match:interests:ITunes 8 ['26bad', 'ded2a'] See 8 ads matching ["26bad","ded2a"]
Powered by Datasette · Query took 24.446ms