Middle+

Мынадай дерекқор схемасы бар -- Тұтынушылар кестесі 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. Соңғы 30 күнде 2-ден астам тапсырыс жасаған пайдаланушылардың жалпы тапсырыс сомасы бойынша ТОП 10-ды көрсетіңіз 2. Соңғы 6 айда табыс бойынша ТОП 3 категорияны көрсетіңіз

sobes.tech AI

AI-дан жауап

Соңғы 30 күн ішінде 2-ден астам тапсырыс жасаған ең көп 10 пайдаланушы:

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;

Соңғы 6 ай ішінде ең көп табыс әкелген 3 категория:

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-лерді оқылымды әрі ыңғайлы ету үшін пайдаланады, тапсырыстарды күн бойынша сүзгілейді, содан кейін қажетті критерийлер бойынша деректерді агрегаттайды.

Алдыңғы Келесі