Выбросы дневной выручки и обычный уровень
SQL, по которому посчитано
WITH daily AS (
SELECT
date,
sum(revenue - returned_revenue) AS revenue
FROM sales
WHERE date >= (SELECT date_trunc('month', max(date)) - INTERVAL '11 months' FROM sales)
GROUP BY date
),
center AS (
SELECT median(revenue) AS median_revenue
FROM daily
),
spread AS (
SELECT median(abs(d.revenue - c.median_revenue)) AS mad
FROM daily d
CROSS JOIN center c
),
flagged AS (
SELECT
d.date,
d.revenue,
c.median_revenue,
d.revenue - c.median_revenue AS excess_revenue
FROM daily d
CROSS JOIN center c
CROSS JOIN spread s
WHERE s.mad > 0
AND d.revenue > c.median_revenue + 3 * 1.4826 * s.mad
)
SELECT
date AS sale_date,
round(revenue, 2) AS revenue,
round(median_revenue, 2) AS typical_revenue,
round(excess_revenue, 2) AS excess_revenue,
round(revenue / NULLIF(median_revenue, 0), 3) AS revenue_ratio
FROM flagged
ORDER BY excess_revenue DESC
LIMIT 30