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) AS revenue
    FROM third_wave_coffee_shop
    GROUP BY coffee_name
),
total_revenue AS (
    SELECT SUM(revenue) AS total_rev
    FROM product_revenue
),
abc_prep AS (
    SELECT
        pr.coffee_name,
        pr.revenue,
        round((pr.revenue::NUMERIC / tr.total_rev), 6) AS revenue_share  -- приведение к NUMERIC для точности
    FROM product_revenue pr
    CROSS JOIN total_revenue tr
),
abc_final AS (
    SELECT
        coffee_name,
        revenue,
        revenue_share,
        round(SUM(revenue_share) OVER (ORDER BY revenue DESC ROWS UNBOUNDED PRECEDING), 6) AS cumulative_share
    FROM abc_prep
)
SELECT
    coffee_name,
    revenue,
    revenue_share,
    cumulative_share,
    CASE
        WHEN cumulative_share <= 0.8 THEN 'A'
        WHEN cumulative_share <= 0.95 THEN 'B'
        ELSE 'C'
    END AS abc_class
FROM abc_final
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,
        CASE 
            WHEN EXTRACT(DOW FROM datetime) = 0 THEN 7 
            ELSE EXTRACT(DOW FROM datetime) 
        END AS day_of_week_num,
        EXTRACT(HOUR FROM datetime) AS tx_hour,
        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
)
SELECT
    -- Ось X: часы с ведущим нулём для сортировки
    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_transaction_load
FROM hourly_stats
GROUP BY tx_hour, day_of_week_num, hour_label, day_label
ORDER BY day_of_week_num, tx_hour;