windows_func.sql - master - vorobkin/Homework3DB

windows_func.sql
218 строк8 KB
artemvorobkin
first_commit
30 ноя 2025, 18:24
30 ноя 2025, 18:2498683fa
SELECT 
    job_industry_category AS "Сфера деятельности",
    COUNT(customer_id) AS "Количество клиентов"
FROM customer
GROUP BY job_industry_category
ORDER BY COUNT(customer_id) DESC;
---
SELECT 
    EXTRACT(YEAR FROM o.order_date) AS year,
    EXTRACT(MONTH FROM o.order_date) AS month,
    c.job_industry_category AS industry_category,
    SUM(oi.item_list_price_at_sale * oi.quantity) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN customer c ON o.customer_id = c.customer_id
WHERE o.order_status = 'Approved'  -- или другой статус подтвержденного заказа
GROUP BY 
    EXTRACT(YEAR FROM o.order_date),
    EXTRACT(MONTH FROM o.order_date),
    c.job_industry_category
ORDER BY 
    year, 
    month, 
    industry_category;
--- Вывести количество уникальных онлайн-заказов для всех брендов в рамках подтвержденных заказов клиентов из сферы IT. 
--- Включить бренды, у которых нет онлайн-заказов от IT-клиентов, — для них должно быть указано количество 0.
SELECT 
    p.brand AS brand,
    COUNT(DISTINCT CASE 
        WHEN o.online_order = true AND o.order_status = 'Approved' AND c.job_industry_category = 'IT' 
        THEN o.order_id 
    END) AS unique_online_orders
FROM product p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
LEFT JOIN customer c ON o.customer_id = c.customer_id
GROUP BY p.brand
ORDER BY unique_online_orders DESC;
---используя только GROUP BY
EXPLAIN ANALYZE
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    SUM(oi.item_list_price_at_sale * oi.quantity) AS total_revenue,
    MAX(oi.item_list_price_at_sale * oi.quantity) AS max_order_amount,
    MIN(oi.item_list_price_at_sale * oi.quantity) AS min_order_amount,
    COUNT(DISTINCT o.order_id) AS order_count,
    AVG(oi.item_list_price_at_sale * oi.quantity) AS avg_order_amount
FROM customer c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_revenue DESC, order_count DESC;

--оконные функции (запрос выполняются в 2 раза дольше)
EXPLAIN ANALYZE
WITH customer_orders AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        o.order_id,
        SUM(oi.item_list_price_at_sale * oi.quantity) as order_total
    FROM customer c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY c.customer_id, c.first_name, c.last_name, o.order_id
),
customer_stats AS (
    SELECT 
        customer_id,
        first_name,
        last_name,
        SUM(order_total) OVER w AS total_revenue,
        MAX(order_total) OVER w AS max_order_amount,
        MIN(order_total) OVER w AS min_order_amount,
        COUNT(*) OVER w AS order_count,
        AVG(order_total) OVER w AS avg_order_amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_id) as rn
    FROM customer_orders
    WINDOW w AS (PARTITION BY customer_id)
)
SELECT 
    customer_id,
    first_name,
    last_name,
    total_revenue,
    max_order_amount,
    min_order_amount,
    order_count,
    avg_order_amount
FROM customer_stats
WHERE rn = 1
ORDER BY total_revenue DESC, order_count DESC;

--- Найти имена и фамилии клиентов с топ-3 минимальной и топ-3 максимальной суммой транзакций за весь период (учесть клиентов, у которых нет заказов, 
--- приняв их сумму транзакций за 0).
WITH customer_totals AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        COALESCE(SUM(oi.item_list_price_at_sale * oi.quantity), 0) AS total_amount
    FROM customer c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    LEFT JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY c.customer_id, c.first_name, c.last_name
),
ranked_customers AS (
    SELECT 
        customer_id,
        first_name,
        last_name,
        total_amount,
        RANK() OVER (ORDER BY total_amount ASC) AS min_rank,
        RANK() OVER (ORDER BY total_amount DESC) AS max_rank
    FROM customer_totals
)
SELECT 
    customer_id,
    first_name,
    last_name,
    total_amount,
    CASE 
        WHEN min_rank <= 3 THEN 'Топ-3 минимальная'
        WHEN max_rank <= 3 THEN 'Топ-3 максимальная'
    END as category
FROM ranked_customers
WHERE min_rank <= 3 OR max_rank <= 3
ORDER BY total_amount;
--- Вывести только вторые транзакции клиентов (если они есть) с помощью оконных функций. 
--- Если у клиента меньше двух транзакций, он не должен попасть в результат.
WITH numbered_orders AS (
    SELECT 
        o.customer_id,
        c.first_name,
        c.last_name,
        o.order_id,
        o.order_date,
        oi.item_list_price_at_sale * oi.quantity as order_amount,
        ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date, o.order_id) as order_rank
    FROM orders o
    JOIN customer c ON o.customer_id = c.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
)
SELECT 
    customer_id,
    first_name,
    last_name,
    order_id,
    order_date,
    order_amount
FROM numbered_orders
WHERE order_rank = 2;

---Вывести имена, фамилии и профессии клиентов, а также длительность максимального интервала (в днях) между двумя последовательными заказами. 
--- Исключить клиентов, у которых только один или меньше заказов.
WITH order_gaps AS (
    SELECT 
        o.customer_id,
        c.first_name,
        c.last_name,
        c.job_title,
        o.order_date - LAG(o.order_date) OVER (
            PARTITION BY o.customer_id 
            ORDER BY o.order_date
        ) as gap_days
    FROM orders o
    JOIN customer c ON o.customer_id = c.customer_id
)
SELECT 
    customer_id,
    first_name,
    last_name,
    job_title,
    MAX(gap_days) as max_interval_days
FROM order_gaps
WHERE gap_days IS NOT NULL
GROUP BY customer_id, first_name, last_name, job_title
ORDER BY max_interval_days DESC;
   
---Найти топ-5 клиентов (по общему доходу) в каждом сегменте благосостояния (wealth_segment). 
--- Вывести имя, фамилию, сегмент и общий доход. Если в сегменте менее 5 клиентов, вывести всех.
WITH customer_revenue AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        c.wealth_segment,
        COALESCE(SUM(oi.item_list_price_at_sale * oi.quantity), 0) AS total_revenue
    FROM customer c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    LEFT JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY c.customer_id, c.first_name, c.last_name, c.wealth_segment
),
ranked_customers AS (
    SELECT 
        customer_id,
        first_name,
        last_name,
        wealth_segment,
        total_revenue,
        ROW_NUMBER() OVER (
            PARTITION BY wealth_segment 
            ORDER BY total_revenue DESC
        ) as rank_in_segment
    FROM customer_revenue
)
SELECT 
    first_name,
    last_name,
    wealth_segment,
    total_revenue,
    rank_in_segment
FROM ranked_customers
WHERE rank_in_segment <= 5
ORDER BY wealth_segment, rank_in_segment;