extract_data.sql

-- Общие метрики бизнеса
WITH tx AS (
    SELECT transaction_id, MAX(total_cost) AS total_cost
    FROM third_wave_coffee_shop 
    GROUP BY transaction_id
)
SELECT
    COUNT(*) AS total_orders,
    ROUND(SUM(total_cost), 2) AS total_revenue,
    ROUND(AVG(total_cost), 2) AS avg_ticket
FROM tx;


-- Динамика по дням недели
WITH tx AS (
    SELECT transaction_id, MAX(total_cost) AS total_cost, MIN(day_name) AS day_name
    FROM third_wave_coffee_shop
    GROUP BY transaction_id
)
SELECT
    day_name,
    COUNT(*) AS total_orders,
    ROUND(SUM(total_cost), 2) AS revenue,
    ROUND(AVG(total_cost), 2) AS avg_ticket
FROM tx
GROUP BY day_name
ORDER BY revenue DESC;


-- Заказы: будни vs выходные
WITH tx AS (
    SELECT 
        transaction_id, 
        MAX(total_cost) AS total_cost, 
        BOOL_OR(is_weekend) AS is_weekend
    FROM third_wave_coffee_shop 
    GROUP BY transaction_id
)
SELECT
    is_weekend,
    COUNT(*) AS total_orders,
    ROUND(SUM(total_cost), 2) AS revenue,
    ROUND(AVG(total_cost), 2) AS avg_ticket
FROM tx
GROUP BY is_weekend;


-- Популярность напитков
SELECT
    coffee_name,
    COUNT(*) AS drinks_sold,
    ROUND(SUM(drink_price), 2) AS revenue
FROM third_wave_coffee_shop
GROUP BY coffee_name
ORDER BY revenue DESC
LIMIT 8;


-- Распределение заказов по времени дня
WITH tx AS (
    SELECT transaction_id, MAX(total_cost) AS total_cost, MIN(time_of_day) AS time_of_day
    FROM coffee_shop_sales
    GROUP BY transaction_id
)
SELECT
    time_of_day,
    COUNT(*) AS total_orders,
    ROUND(SUM(total_cost), 2) AS total_revenue,
    ROUND(AVG(total_cost), 2) AS avg_ticket
FROM tx
GROUP BY time_of_day
ORDER BY total_revenue DESC;


-- Динамика по часам
WITH tx AS (
    SELECT 
        transaction_id,
        MAX(total_cost) AS total_cost,
        EXTRACT(HOUR FROM MIN(datetime)) AS hour
    FROM third_wave_coffee_shop
    GROUP BY transaction_id
)
SELECT
    hour::INT AS hour,
    COUNT(*) AS num_tx,
    ROUND(SUM(total_cost), 2) AS total_revenue,
    ROUND(AVG(total_cost), 2) AS avg_check
FROM tx
GROUP BY hour
ORDER BY hour;


-- Среднее количество напитков в заказе
SELECT 
    ROUND(AVG(drinks_per_tx), 2) AS avg_drinks_per_order
FROM (
    SELECT transaction_id, COUNT(*) AS drinks_per_tx
    FROM third_wave_coffee_shop
    GROUP BY transaction_id
) sub;


-- Концентрация выручки на топ-напитках
WITH revenue_stats AS (
	SELECT
		coffee_name,
		SUM(drink_price) AS revenue,
		RANK() OVER (ORDER BY SUM(drink_price) DESC) as rnk
	FROM third_wave_coffee_shop
	GROUP BY coffee_name
)
SELECT
	CASE WHEN rnk <= 3 THEN coffee_name ELSE 'other' END AS category,
	ROUND(SUM(revenue), 2) AS revenue,
	ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 2) AS share_percent
FROM revenue_stats 
GROUP BY category
ORDER BY revenue DESC;

-- Структура оплат
WITH tx AS (
	SELECT DISTINCT ON (transaction_id)
			transaction_id, total_cost, payment_method
	FROM third_wave_coffee_shop
	ORDER BY transaction_id
)
SELECT
    payment_method,
    COUNT(*) AS total_orders,
    ROUND(SUM(total_cost), 2) AS revenue,
    ROUND(100.0 * SUM(total_cost) / SUM(SUM(total_cost)) OVER (), 2) AS percent_of_total
FROM tx
GROUP BY payment_method
ORDER BY revenue DESC;


-- Размер корзины: сколько напитков люди обычно покупают за один раз
WITH tx_data AS (
	SELECT 
		transaction_id,
		COUNT(*) AS n_items,
		MAX(total_cost) AS tx_revenue
	FROM third_wave_coffee_shop
	GROUP BY transaction_id
)
SELECT
	n_items,
	COUNT(*) AS order_count,
	ROUND(AVG(tx_revenue), 2) AS avg_check,	-- Средний чек для группы
	ROUND(
		PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tx_revenue)::NUMERIC,
		2
	) AS median_check,
	ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percent,
	-- Доля этой группы в общей выручке кофейни
	ROUND(100.0 * SUM(tx_revenue) / SUM(SUM(tx_revenue)) OVER (), 2) AS revenue_share,
	ROUND(100.0 * SUM(COUNT(*)) OVER (ORDER BY n_items) / SUM(COUNT(*)) OVER (), 2) AS cumulative_percent
FROM tx_data
GROUP BY n_items
ORDER BY n_items;



-- Топ-10 клиентов по общим тратам
WITH unique_tx AS (
    SELECT DISTINCT ON (transaction_id)
        customer_id,
        total_cost
    FROM third_wave_coffee_shop
    ORDER BY transaction_id
)
SELECT
    customer_id,
    SUM(total_cost) AS total_spent,
    COUNT(*) AS num_transactions,
    ROUND(AVG(total_cost), 2) AS avg_check
FROM unique_tx
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;

-- Анализ распределения сумм чеков
WITH check_summary AS (
  SELECT
    transaction_id,
    MAX(total_cost) AS check_amount
  FROM public.third_wave_coffee_shop
  GROUP BY transaction_id
  HAVING MAX(total_cost) = MIN(total_cost)  
),
stats AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY check_amount) AS q1,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY check_amount) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY check_amount) AS q3,
    MAX(check_amount) AS max_amount
  FROM check_summary
)
SELECT
  q1,
  median,
  q3,
  q3 - q1 AS iqr,
  q3 + 1.5 * (q3 - q1) AS upper_bound,
  max_amount
FROM stats;

-- Инсайдеры: самые большие чеки, попавшие под удаление
WITH check_summary AS (
  SELECT
    transaction_id,
    MAX(total_cost) AS check_amount,
    COUNT(*) AS items_count,
    STRING_AGG(coffee_name, ', ') AS items_list,
    MIN(sale_date) AS sale_date,
    MIN(time_of_day) AS time_of_day
  FROM public.third_wave_coffee_shop
  GROUP BY transaction_id
  HAVING MAX(total_cost) = MIN(total_cost)
),
iqr AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY check_amount) AS q1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY check_amount) AS q3
  FROM check_summary
),
outliers AS (
  SELECT
    cs.*,
    i.q3 + 1.5 * (i.q3 - i.q1) AS upper_bound
  FROM check_summary cs
  CROSS JOIN iqr i
  WHERE cs.check_amount > i.q3 + 1.5 * (i.q3 - i.q1)
)
SELECT
  transaction_id,
  check_amount,
  items_count,
  items_list,
  sale_date,
  time_of_day,
  ROUND(upper_bound::NUMERIC) AS upper_bound  
FROM outliers
ORDER BY check_amount DESC;

-- Управлюющий кофейни хочет понять: насколько стабильно работает утренняя смена.
-- Oтчёт по ежедневной выручке только за Morning (1)
SELECT 
    sale_date,
    COUNT(*) AS unique_transactions,
    SUM(total_cost) AS total_revenue,
    ROUND(AVG(total_cost), 2) AS avg_check
FROM (
    SELECT DISTINCT ON (transaction_id)
        sale_date,
        transaction_id,
        total_cost
    FROM third_wave_coffee_shop
    WHERE time_of_day = 'Morning'
    ORDER BY transaction_id 
) AS unique_checks
GROUP BY sale_date
ORDER BY sale_date;

-- Oтчёт по ежедневной выручке только за Morning (2)
SELECT 
    sale_date,
    COUNT(*) AS unique_transactions,
    SUM(max_total_cost) AS total_revenue,
    ROUND(AVG(max_total_cost), 2) AS avg_check
FROM (
    SELECT 
        sale_date,
        transaction_id,
        MAX(total_cost) AS max_total_cost
    FROM third_wave_coffee_shop
    WHERE time_of_day = 'Morning'
    GROUP BY sale_date, transaction_id
) AS daily_transactions
GROUP BY sale_date
ORDER BY sale_date;

-- Выручка в час (Revenue per Hour), жестко фиксируя временные интервалы.
WITH hourly_stats AS (
    SELECT 
        sale_date,
        transaction_id,
        MAX(total_cost) AS check_cost, -- Дедупликация суммы чека
        -- Сегментация по 3-часовым слотам (только будни)
        CASE 
            WHEN sale_time::time BETWEEN '11:00:00' AND '13:59:59' THEN '1. Lunch_Rush (11–14)'
            WHEN sale_time::time BETWEEN '14:00:00' AND '16:59:59' THEN '2. Dead_Hours (14–17)'
            WHEN sale_time::time >= '17:00:00' THEN '3. Evening_Peak (17–20)'
        END AS real_segment
    FROM third_wave_coffee_shop
    WHERE is_weekend = FALSE      -- Исключаем выходные
      AND sale_time >= '11:00:00' -- Исключаем утро
    GROUP BY sale_date, transaction_id, real_segment
)
SELECT 
    real_segment,
    COUNT(DISTINCT sale_date) AS unique_days,       -- Количество рабочих дней
    COUNT(*) AS total_checks,                       -- Всего чеков
    ROUND(AVG(check_cost), 2) AS avg_check_rub,     -- Средний чек
    SUM(check_cost) AS total_revenue,               -- Общая выручка за всё время
    
    -- KPI: Эффективность одного часа работы 
    ROUND(
        SUM(check_cost) / (COUNT(DISTINCT sale_date) * 3.0), 
        0
    ) AS revenue_per_hour
FROM hourly_stats
WHERE real_segment IS NOT NULL
GROUP BY real_segment
ORDER BY real_segment;

-- KPI кофейни
select
	sum(total_cost) as "Total Revenue",
	count(*) as "Transactions",
	round(avg(total_cost), 2) as "Avg Ticket"
from (
	select distinct
		transaction_id,
		total_cost
	from third_wave_coffee_shop
) as unique_tickets;

-- Анализ продаж по продуктам (Menu Engineering)
-- Топ и анти-топ продуктов
select
	coffee_name,
	sum(drink_price) as revenue,
	count(*) as quantity,
	count(distinct transaction_id) as transactions
from third_wave_coffee_shop
group by coffee_name
order by revenue desc
limit 10;

-- ABC-анализ (фокус на выручку)
WITH product_revenue AS (
    SELECT
        coffee_name,
        SUM(drink_price::NUMERIC) AS revenue
    FROM third_wave_coffee_shop
    GROUP BY coffee_name
),
product_totals AS (
    SELECT
        coffee_name,
        revenue,
        SUM(revenue) OVER () AS total_revenue
    FROM product_revenue
    WHERE revenue > 0  -- исключаем нулевые
),
abc_calculation AS (
    SELECT
        coffee_name,
        revenue,
        revenue / total_revenue AS revenue_share,
        SUM(revenue) OVER (ORDER BY revenue DESC) 
            / total_revenue AS cumulative_share
    FROM product_totals
)
SELECT
    coffee_name,
    revenue,
    ROUND(revenue_share::NUMERIC, 4) AS revenue_share,
    ROUND(cumulative_share::NUMERIC, 4) AS cumulative_share,
    CASE
        WHEN cumulative_share <= 0.80 THEN 'A'  -- top 80%
        WHEN cumulative_share <= 0.95 THEN 'B'  -- next 15%
        ELSE 'C'                                -- bottom 5%
    END AS abc_class
FROM abc_calculation
ORDER BY revenue DESC;


-- ABC-анализ с форматированием для отчетности
WITH product_revenue AS (
	SELECT
		coffee_name,
		SUM(drink_price::NUMERIC) AS revenue
	FROM third_wave_coffee_shop
	GROUP BY coffee_name
),
product_totals AS (
	SELECT
		coffee_name,
		revenue,
		SUM(revenue) OVER () AS total_revenue
	FROM product_revenue
	WHERE revenue > 0	-- исключаем нулевые
),
abc_calculation AS (
	SELECT
		coffee_name,
		revenue,
		revenue / total_revenue AS revenue_share,
		SUM(revenue) OVER (ORDER BY revenue DESC) / total_revenue AS cumulative_share
	FROM product_totals
)
SELECT
	coffee_name,
	-- Форматируем выручку (пример: 123 456.78)
	TO_CHAR(revenue, 'FM999 999 999.00') AS revenue_formatted,
	
	-- Переводим долю в проценты (пример: 15.25%)
	TO_CHAR(revenue_share * 100, 'FM990.00') || '%' AS revenue_share_pct,
	
	-- Переводим кумулятивную долю в проценты (пример: 85.00%)
	TO_CHAR(cumulative_share * 100, 'FM990.00') || '%' AS cumulative_share_pct,
	
	CASE
		WHEN cumulative_share <= 0.80 THEN 'A'	-- top 80%
		WHEN cumulative_share <= 0.95 THEN 'B'	-- next 15%
		ELSE 'C' 								-- bottom 5%
	END AS abc_class
FROM abc_calculation
ORDER BY revenue DESC;


-- Временной анализ (нагрузка и планирование персонала)
select
	extract(hour from datetime)::int as hour,
	sum(drink_price) as revenue,
	count(distinct transaction_id) as transactions,
	count(*) as items_sold
from third_wave_coffee_shop
group by hour
order by hour;

--  Анализ дней недели
SELECT
    day_name,
    SUM(drink_price) AS revenue,
    COUNT(DISTINCT transaction_id) AS transactions,
    COUNT(*) AS items_sold
FROM third_wave_coffee_shop
GROUP BY day_name, EXTRACT(DOW FROM datetime)  -- DOW: 0=Sun, 1=Mon, ..., 6=Sat
ORDER BY 
    CASE day_name
        WHEN 'Monday'    THEN 1
        WHEN 'Tuesday'   THEN 2
        WHEN 'Wednesday' THEN 3
        WHEN 'Thursday'  THEN 4
        WHEN 'Friday'    THEN 5
        WHEN 'Saturday'  THEN 6
        WHEN 'Sunday'    THEN 7
    END;

-- Подсчёт количества проданных единиц каждого напитка по неделям
SELECT
    date_trunc('week', sale_date::date) AS week_start,
    coffee_name,
    COUNT(*) AS units_sold,
    SUM(COUNT(*)) OVER (PARTITION BY date_trunc('week', sale_date::date)) AS total_units_week,
    ROUND(
        COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY date_trunc('week', sale_date::date)),
        2
    ) AS percentage
FROM
    third_wave_coffee_shop
GROUP BY
    week_start,
    coffee_name
ORDER BY
    week_start,
    units_sold DESC;

-- Анализ за весь период (июль-сентябрь 2025)
WITH unique_tx AS (
    SELECT DISTINCT ON (transaction_id)
        transaction_id,
        total_cost
    FROM third_wave_coffee_shop
    ORDER BY transaction_id
),
stats_raw AS (
    SELECT
        AVG(total_cost) AS avg_check,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_cost) AS median_check,
        COUNT(*) AS n_transactions
    FROM unique_tx
),
stats_final AS (
    SELECT
        ROUND(avg_check::NUMERIC, 2) AS avg_check,
        ROUND(median_check::NUMERIC, 2) AS median_check,
        n_transactions
    FROM stats_raw
)
SELECT
    avg_check,
    median_check,
    ROUND((avg_check - median_check)::NUMERIC, 2) AS avg_median_diff,
    ROUND(
        ((avg_check - median_check) / NULLIF(median_check, 0))::NUMERIC * 100,
        2
    ) AS diff_percent,
    n_transactions,
    CASE
        WHEN ABS(avg_check - median_check) > median_check * 0.5
        THEN 'Внимание: возможны выбросы'
        ELSE 'Распределение нормальное'
    END AS data_quality_note
FROM stats_final;

 -- Выручка по дням + динамика vs вчера
 WITH daily_revenue AS (
	SELECT
		sale_date,
		SUM(total_cost) AS revenue
	FROM (
		SELECT DISTINCT ON (transaction_id)
			transaction_id,
			sale_date,
			total_cost
		FROM third_wave_coffee_shop
		ORDER BY transaction_id
	) AS unique_tx
	GROUP BY sale_date
	ORDER BY sale_date
)
SELECT
	sale_date,
	revenue,
	LAG(revenue, 1) OVER (ORDER BY sale_date) AS prev_day_revenue,
	ROUND(
		(revenue - LAG(revenue, 1) OVER (ORDER BY sale_date))
		/ NULLIF(LAG(revenue, 1) OVER (ORDER BY sale_date), 0) * 100,
		2
	) AS revenue_change_pct
FROM daily_revenue;

-- Вместо DISTINCT ON...
SELECT
	transaction_id,
	sale_date,
	MAX(total_cost) AS total_cost
FROM third_wave_coffee_shop
GROUP BY transaction_id, sale_date

-- Средний чек по дням
WITH unique_tx AS (
	SELECT DISTINCT ON (transaction_id)
		transaction_id,
		sale_date,
		total_cost
	FROM third_wave_coffee_shop
	ORDER BY transaction_id
)
SELECT
	sale_date,
	ROUND(AVG(total_cost), 2) AS avg_check
FROM unique_tx
GROUP BY sale_date
ORDER BY sale_date;

-- Количество чеков (трафик) по дням
SELECT
	sale_date,
	COUNT(DISTINCT transaction_id) AS n_transactions
FROM third_wave_coffee_shop
GROUP BY sale_date
ORDER BY sale_date;

-- Динамика выручки по аналогичным дням недели
WITH unique_tx AS (
    SELECT DISTINCT ON (transaction_id)
        transaction_id,
        sale_date,
        total_cost
    FROM third_wave_coffee_shop
    ORDER BY transaction_id
),
weekly_dow AS (
    SELECT
        EXTRACT(ISODOW FROM sale_date) AS day_of_week_num,  -- 1=Mon, 7=Sun
        DATE_TRUNC('week', sale_date)::DATE AS week_start,  -- понедельник
        SUM(total_cost) AS weekly_dow_revenue
    FROM unique_tx
    GROUP BY 1, 2
)
SELECT
    day_of_week_num,
    TO_CHAR(week_start, 'YYYY-MM-DD') AS week_start,
    weekly_dow_revenue,
    LAG(weekly_dow_revenue, 1) OVER (
        PARTITION BY day_of_week_num
        ORDER BY week_start
    ) AS prev_week_revenue,
    ROUND(
        (weekly_dow_revenue - LAG(weekly_dow_revenue, 1) OVER w) 
        / NULLIF(LAG(weekly_dow_revenue, 1) OVER w, 0) * 100,
        2
    ) AS weekly_growth_pct
FROM weekly_dow
WINDOW w AS (PARTITION BY day_of_week_num ORDER BY week_start)
ORDER BY day_of_week_num, week_start;


-- Staffing Load Matrix (Avg Transaction Volume by Day & Hour)
WITH hourly_stats AS (
    SELECT 
        DATE(datetime) AS tx_date,
        EXTRACT(ISODOW FROM datetime) AS day_of_week_num,
        EXTRACT(HOUR FROM datetime) AS tx_hour,
        EXTRACT(WEEK FROM datetime) AS week_num,
        COUNT(DISTINCT transaction_id) AS transaction_count
    FROM third_wave_coffee_shop
    WHERE datetime >= '2025-07-01' AND datetime < '2025-10-01'
    GROUP BY 1, 2, 3, 4
),
with_prev_week AS (
    SELECT 
        *,
        LAG(transaction_count) OVER (
            PARTITION BY day_of_week_num, tx_hour 
            ORDER BY week_num
        ) AS prev_week_count
    FROM hourly_stats
),
wow_comparison AS (
    SELECT 
        *,
        ROUND(
            (transaction_count - prev_week_count) / 
            NULLIF(prev_week_count, 0) * 100, 
            1
        ) AS wow_change_pct,
        CASE 
            WHEN transaction_count > prev_week_count THEN 'UP'
            WHEN transaction_count < prev_week_count THEN 'DOWN'
            ELSE 'FLAT'
        END AS trend_direction
    FROM with_prev_week
)
SELECT
    LPAD(tx_hour::TEXT, 2, '0') || ':00' AS hour_label,
    day_of_week_num || '. ' || 
    CASE day_of_week_num
        WHEN 1 THEN 'Monday' WHEN 2 THEN 'Tuesday' WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday' WHEN 5 THEN 'Friday' WHEN 6 THEN 'Saturday'
        WHEN 7 THEN 'Sunday'
    END AS day_label,
    
    -- Основные метрики нагрузки
    ROUND(AVG(transaction_count), 1) AS avg_receipts,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY transaction_count)::numeric, 1) AS median_receipts,
    ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY transaction_count)::numeric, 1) AS p90_receipts,
    
    -- Трендовые метрики
    ROUND(AVG(wow_change_pct), 1) AS avg_wow_growth_pct,
    ROUND(
        COUNT(CASE WHEN trend_direction = 'UP' THEN 1 END) * 100.0 / 
        NULLIF(COUNT(trend_direction), 0), 
        1
    ) AS pct_weeks_growing,
    COUNT(*) AS data_points_with_trend
FROM wow_comparison
WHERE prev_week_count IS NOT NULL
GROUP BY tx_hour, day_of_week_num
ORDER BY day_of_week_num, tx_hour;


-- Gap analysis
WITH aggregated_scans AS (
    SELECT 
        sku, 
        SUM(qty) AS fact_qty
    FROM scanner_scans
    GROUP BY sku
),
inventory_analysis AS (
    SELECT 
        c.sku,
        c.item_name,
        COALESCE(w.system_qty, 0) AS system_qty,
        COALESCE(a.fact_qty, 0) AS fact_qty,
        COALESCE(a.fact_qty, 0) - COALESCE(w.system_qty, 0) AS diff_qty,
        (COALESCE(a.fact_qty, 0) - COALESCE(w.system_qty, 0)) * COALESCE(w.purchase_price, 0) AS financial_impact_rub,
        CASE 
            WHEN COALESCE(a.fact_qty, 0) - COALESCE(w.system_qty, 0) = 0 THEN 'OK'
            WHEN COALESCE(a.fact_qty, 0) - COALESCE(w.system_qty, 0) < 0 THEN 'Недостача'
            ELSE 'Излишек'
        END AS status
    FROM sku_catalog c
    LEFT JOIN wms_inventory w ON c.sku = w.sku
    LEFT JOIN aggregated_scans a ON c.sku = a.sku
)
SELECT *
FROM inventory_analysis
ORDER BY ABS(financial_impact_rub) DESC;