Пропущенные месяцы плана по каналам
SQL, по которому посчитано
WITH b AS (
SELECT date_trunc('month', max(month)) AS last_month
FROM plan
),
months AS (
SELECT CAST(unnest(generate_series(last_month - INTERVAL '11 months', last_month, INTERVAL '1 month')) AS DATE) AS month
FROM b
),
channels AS (
SELECT channel FROM plan
UNION
SELECT channel FROM sales
),
grid AS (
SELECT m.month, c.channel
FROM months m
CROSS JOIN channels c
)
SELECT
g.channel,
count(*) AS months_total,
count(p.month) AS months_in_plan,
count(*) - count(p.month) AS missing_months,
round(sum(coalesce(p.revenue, 0))) AS planned_revenue,
coalesce(string_agg(CASE WHEN p.month IS NULL THEN strftime(g.month, '%Y-%m') END, ', ' ORDER BY g.month), '') AS missing_list
FROM grid g
LEFT JOIN plan p
ON p.month = g.month
AND p.channel = g.channel
GROUP BY g.channel
ORDER BY missing_months DESC, g.channel