Пропуски и нулевые дни по месяцам
SQL, по которому посчитано
WITH bounds AS (
SELECT max(date) AS max_date
FROM sales
),
calendar AS (
SELECT CAST(day AS DATE) AS date
FROM bounds b
CROSS JOIN LATERAL generate_series(
CAST(b.max_date - INTERVAL 12 MONTH + INTERVAL 1 DAY AS DATE),
CAST(b.max_date AS DATE),
INTERVAL 1 DAY
) AS t(day)
),
sales_by_day AS (
SELECT s.date, SUM(s.revenue) AS revenue
FROM sales s, bounds b
WHERE s.date > b.max_date - INTERVAL 12 MONTH
AND s.date <= b.max_date
GROUP BY s.date
),
joined AS (
SELECT c.date,
CASE WHEN s.date IS NULL THEN 0 ELSE 1 END AS has_sales,
CASE WHEN s.date IS NOT NULL AND COALESCE(s.revenue, 0) = 0 THEN 1 ELSE 0 END AS zero_revenue
FROM calendar c
LEFT JOIN sales_by_day s ON s.date = c.date
)
SELECT date_trunc('month', date)::DATE AS month,
COUNT(*) AS calendar_days,
SUM(has_sales) AS days_with_sales,
COUNT(*) - SUM(has_sales) AS missing_days,
SUM(zero_revenue) AS zero_revenue_days,
ROUND((COUNT(*) - SUM(has_sales))::DOUBLE / COUNT(*), 3) AS missing_share
FROM joined
GROUP BY 1
ORDER BY 1