Дни с продажами и пропуски по каналам за 12 месяцев
SQL, по которому посчитано
WITH bounds AS (
SELECT max(date) AS max_date
FROM sales
),
period AS (
SELECT (max_date - INTERVAL 12 MONTH)::DATE AS start_date,
max_date::DATE AS max_date
FROM bounds
),
calendar AS (
SELECT CAST(unnest(generate_series(
(start_date + INTERVAL 1 DAY)::TIMESTAMP,
max_date::TIMESTAMP,
INTERVAL 1 DAY
)) AS DATE) AS date
FROM period
),
channels AS (
SELECT DISTINCT channel
FROM sales
),
expected AS (
SELECT c.channel,
count(*) AS expected_days
FROM channels c
CROSS JOIN calendar
GROUP BY c.channel
),
actual AS (
SELECT channel,
count(DISTINCT date) AS days_with_sales,
min(date) AS first_sale,
max(date) AS last_sale
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY channel
)
SELECT e.channel,
e.expected_days,
coalesce(a.days_with_sales, 0) AS days_with_sales,
e.expected_days - coalesce(a.days_with_sales, 0) AS missing_days,
round(coalesce(a.days_with_sales, 0) * 1.0 / e.expected_days, 4) AS coverage_share,
a.first_sale,
a.last_sale
FROM expected e
LEFT JOIN actual a
ON e.channel = a.channel
ORDER BY coverage_share ASC, e.channel
LIMIT 30