Data source: simonw/russian-ira-facebook-ads-datasette
This data as JSON, CSV (advanced)
CREATE VIEW display_ads AS
select ads.id,
case when image is not null then
json_object("img_src", "https://raw.githubusercontent.com/edsu/irads/03fb4b/site/" || image, "width", 200)
else
"no image"
end as img,
json_group_array(
json_object(
"label", targets.name,
"href", "/russian-ads/display_ads?_target="
|| urllib_quote_plus(targets.id)
)
) as targeting,
ads.impressions, ads.clicks, ads.url, ads.text,
cast(case
when ads.spend_currency == "RUB" then ads.spend_amount * 0.016
else ads.spend_amount
end as float) as spend_usd,
ads.spend_amount, ads.spend_currency,
ads.created, ads.ended
from ads
join ad_targets on ads.id = ad_targets.ad_id
join targets on ad_targets.target_id = targets.id
group by ads.id
order by ads.id