Есть следующая схема базы данных

Условие

Есть следующая схема базы данных

-- Таблица клиентов

CREATE TABLE customers ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL );

-- Таблица товаров

CREATE TABLE products ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, category VARCHAR(100) );

-- Таблица позиций заказа

CREATE TABLE order_items ( order_id BIGINT NOT NULL, product_id BIGINT NOT NULL, qty DECIMAL(10, 3) NOT NULL CHECK (qty > 0), -- 10 цифр, 3 знака после запятой price NUMERIC(10, 2) NOT NULL CHECK (price >= 0), PRIMARY KEY (order_id, product_id) );

-- Таблица заказов

CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id BIGINT, order_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, status VARCHAR(50) NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')) );

1. Выведите ТОП 10 по сумме заказов пользователей совершивших более 2-х заказов за последние 30 дней
2. Выведите ТОП 3 категорий по выручке за последние 6 месяцев

Ответ

1. ТОП 10 пользователей по сумме заказов, совершивших более 2 заказов за последние 30 дней:

WITH recent_orders AS (
    SELECT *
    FROM orders
    WHERE order_date >= NOW() - INTERVAL '30 days'
),
user_order_counts AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM recent_orders
    GROUP BY customer_id
    HAVING COUNT(*) > 2
),
user_order_sums AS (
    SELECT o.customer_id, SUM(oi.qty * oi.price) AS total_sum
    FROM recent_orders o
    JOIN order_items oi ON o.id = oi.order_id
    WHERE o.customer_id IN (SELECT customer_id FROM user_order_counts)
    GROUP BY o.customer_id
)
SELECT c.id, c.name, c.city, uos.total_sum
FROM user_order_sums uos
JOIN customers c ON c.id = uos.customer_id
ORDER BY uos.total_sum DESC
LIMIT 10;

2. ТОП 3 категорий по выручке за последние 6 месяцев:

WITH recent_orders AS (
    SELECT id
    FROM orders
    WHERE order_date >= NOW() - INTERVAL '6 months'
),
category_revenue AS (
    SELECT p.category, SUM(oi.qty * oi.price) AS revenue
    FROM order_items oi
    JOIN recent_orders ro ON oi.order_id = ro.id
    JOIN products p ON oi.product_id = p.id
    GROUP BY p.category
)
SELECT category, revenue
FROM category_revenue
ORDER BY revenue DESC
LIMIT 3;

Эти запросы используют CTE для удобства и читаемости, фильтруют заказы по дате, затем агрегируют данные по нужным критериям.