sequenceDiagram
    actor User
    participant App
    participant Backend

    User->>App: Регистрация
    App->>Backend: Отправка данных
    Backend-->>App: Подтверждение ✅
    App->>User: Показ онбординга
    User->>App: Клик "Далее"
    App->>User: Открытие главного экрана
    Note over User,App: Ключевое действие! 🎯
    User->>App: Клик "Создать проект"
    App->>Backend: Запрос на создание
    Backend-->>App: Успех 🎉
    App->>User: Проект создан 🏆

sequenceDiagram
    actor User
    participant App
    participant Backend

    User->>App: Регистрация
    App->>Backend: Отправка данных
    Backend-->>App: Подтверждение ✅
    App->>User: Показ онбординга
    User->>App: Клик "Далее"
    App->>User: Открытие главного экрана

    Note right of User: 📈 ПО ПОВЕДЕНЧЕСКИМ ДАННЫМ:<br/>🔥 80% пользователей здесь<br/>🌀 уходят в "блуждание"<br/>🚪 или покидают приложение.

    alt Успешный путь (20%) 🎯
        User->>App: Клик "Создать проект"
        App->>Backend: Запрос на создание
        Backend-->>App: Успех ✅
        App->>User: Проект создан 🏆
    else Проблемный путь (80%) 🚨
        User->>App: Блуждание по другим разделам 🗺
        App->>User: Показ нерелевантного контента 📺
        User->>App: Закрытие приложения / бездействие ❌
    end

SELECT 
    user_id,
    COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) as success_count,
    COUNT(CASE WHEN status = 'FAILED' THEN 1 END) as failed_count,
    COUNT(CASE WHEN status IS NULL THEN 1 END) as null_count  -- Вот это ключ!
FROM transactions 
WHERE created_at >= '2024-10-01'
GROUP BY user_id
HAVING null_count > 0;

WITH user_orders AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) as order_count,
        SUM(bonus_amount) as total_bonuses
    FROM orders
    WHERE created_at BETWEEN '2024-10-01' AND '2024-10-15'
    GROUP BY user_id
)
SELECT 
    CASE 
        WHEN order_count = 1 THEN 'Один заказ'
        WHEN order_count BETWEEN 2 AND 5 THEN '2-5 заказов'
        ELSE 'Более 5 заказов'
    END as segment,
    COUNT(user_id) as users_count,
    AVG(total_bonuses) as avg_bonus_per_user,
    SUM(total_bonuses) as segment_bonus_total
FROM user_orders
GROUP BY 1
ORDER BY segment_bonus_total DESC;

SELECT 
    DATE_TRUNC('week', order_date) as week,
    region,
    COUNT(order_id) as orders_count,
    SUM(order_amount) as revenue,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(order_amount) / COUNT(DISTINCT customer_id) as avg_revenue_per_customer
FROM sales
WHERE region = 'Сибирь'
    AND order_date >= '2024-09-01'
GROUP BY 1, 2
ORDER BY week DESC;

SELECT 
    customer_id,
    email,
    COUNT(*) as duplicate_count
FROM customers
GROUP BY customer_id, email
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

SELECT 
    order_id,
    SUM(item_price) as total_price,
    SUM(discount_amount) as total_discount,
    CASE 
        WHEN SUM(discount_amount) > SUM(item_price) * 0.3 THEN 'Подозрительно много'
        WHEN SUM(discount_amount) = 0 THEN 'Без скидки'
        ELSE 'Нормально'
    END as discount_check
FROM order_items
WHERE order_date >= '2024-10-01'
GROUP BY order_id
HAVING SUM(discount_amount) > SUM(item_price) * 0.3
ORDER BY total_discount DESC;

SELECT 
    customer_id,
    (SELECT COUNT(*) FROM orders o2 WHERE o2.customer_id = o1.customer_id) as total_orders,
    (SELECT SUM(amount) FROM payments p WHERE p.order_id IN 
        (SELECT order_id FROM orders o3 WHERE o3.customer_id = o1.customer_id)
    ) as total_paid
FROM customers o1
WHERE created_at >= '2024-01-01';

SELECT 
    c.customer_id,
    COUNT(DISTINCT o.order_id) as total_orders,
    SUM(p.amount) as total_paid
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN payments p ON o.order_id = p.order_id
WHERE c.created_at >= '2024-01-01'
GROUP BY c.customer_id;

SELECT 
    DATE_TRUNC('month', order_date) as month,
    SUM(amount) as revenue,
    LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) as prev_revenue,
    (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date))) / 
        LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) * 100 as growth_percent
FROM orders
GROUP BY month
ORDER BY month;

WITH ranked_products AS (
    SELECT 
        category_id,
        product_id,
        SUM(quantity) as total_sold,
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY SUM(quantity) DESC) as rn
    FROM order_items
    GROUP BY category_id, product_id
)
SELECT category_id, product_id, total_sold
FROM ranked_products
WHERE rn <= 3;

SELECT 
    date,
    revenue,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_7d
FROM daily_revenue
ORDER BY date;

WITH product_revenue AS (
    SELECT 
        product_id,
        SUM(amount) as revenue,
        SUM(SUM(amount)) OVER () as total_revenue
    FROM sales
    GROUP BY product_id
)
SELECT 
    product_id,
    revenue,
    revenue / total_revenue * 100 as percent,
    SUM(revenue / total_revenue * 100) OVER (ORDER BY revenue DESC) as cumulative_percent
FROM product_revenue
ORDER BY revenue DESC;