Средняя длительность кампаний по источникам за последний квартал
SQL, по которому посчитано
WITH camp AS (
SELECT
source,
campaign,
min(date) AS start_date,
max(date) AS end_date,
date_diff('day', min(date), max(date)) + 1 AS duration_days
FROM ads
WHERE date >= (SELECT date_trunc('quarter', max(date)) FROM ads)
AND (impressions > 0 OR clicks > 0 OR cost > 0)
GROUP BY source, campaign
)
SELECT
source,
count(*) AS campaigns,
round(avg(duration_days), 1) AS avg_duration_days
FROM camp
GROUP BY source
ORDER BY avg_duration_days DESC