← К задачам SQL
← Предыдущая Следующая →

44. Клиенты выше средней выручки

Medium 0 решили CTE

Посчитайте выручку каждого клиента по заказам со статусом paid или shipped (сумма quantity × unit_price). Оставьте тех, чья выручка строго больше средней выручки по этим клиентам (клиентов без такой выручки в среднее не включайте).

Используйте таблицы customers, orders и order_items.

Результат: customer, revenue. Сначала больше выручка, при равенстве — по имени.

Схема базы интернет-магазина

Схема БД состоит из пяти отношений:

categories (id, name)

products (id, name, category_id, price, in_stock)

customers (id, name, city, registered_at)

orders (id, customer_id, order_date, status)

order_items (order_id, product_id, quantity, unit_price)

Таблица categories содержит идентификатор и название категории товаров. Таблица products — товары: название, категория, цена и остаток на складе. Таблица customers — клиентов: имя, город и дату регистрации. Таблица orders — заказы: клиента, дату и статус. Таблица order_items — позиции заказа: товар, количество и цену на момент покупки. При этом следует иметь в виду, что

  • status принимает значения new, paid, shipped, cancelled;
  • в одном заказе товар не повторяется: первичный ключ позиций — пара (order_id, product_id);
  • price и unit_price заданы в рублях, тип numeric(10,2);
  • клиент может не иметь заказов; заказ без позиций в учебной базе не встречается;
  • связи и ограничения показаны на схеме данных.
Схема данных: customers, orders, order_items, products, categories orders Column Name Data Type Nulls id int customer_id int order_date date status text order_items Column Name Data Type Nulls order_id int product_id int quantity int unit_price numeric products Column Name Data Type Nulls id int name text category_id int price numeric in_stock int customers Column Name Data Type Nulls id int name text city text registered_at date categories Column Name Data Type Nulls id int name text

Данные таблиц

Первые 5 строк каждой таблицы

categories 4

id name
1 Электроника
2 Одежда
3 Книги
4 Дом

products 15

id name category_id price in_stock
1 Смартфон 1 45000 12
2 Ноутбук 1 89000 5
3 Наушники 1 3500 40
4 Футболка 2 1200 80
5 Джинсы 2 4500 25

customers 11

id name city registered_at
1 Анна Смирнова Москва 2024-01-10
2 Иван Петров Санкт-Петербург 2024-02-01
3 Мария Козлова Москва 2024-03-15
4 Сергей Волков Казань 2024-01-20
5 Елена Новикова Москва 2023-11-05

orders 15

id customer_id order_date status
1 1 2024-06-01 paid
2 1 2024-07-12 shipped
3 2 2024-06-15 paid
4 3 2024-06-20 new
5 3 2024-08-01 shipped

order_items 23

order_id product_id quantity unit_price
1 1 1 45000
1 3 2 3500
2 2 1 89000
3 4 2 1200
3 5 1 4500

Данные таблиц

Первые 5 строк. Полная схема — по кнопке выше.

categories 4

id name
1 Электроника
2 Одежда
3 Книги
4 Дом

products 15

id name category_id price in_stock
1 Смартфон 1 45000 12
2 Ноутбук 1 89000 5
3 Наушники 1 3500 40
4 Футболка 2 1200 80
5 Джинсы 2 4500 25

customers 11

id name city registered_at
1 Анна Смирнова Москва 2024-01-10
2 Иван Петров Санкт-Петербург 2024-02-01
3 Мария Козлова Москва 2024-03-15
4 Сергей Волков Казань 2024-01-20
5 Елена Новикова Москва 2023-11-05

orders 15

id customer_id order_date status
1 1 2024-06-01 paid
2 1 2024-07-12 shipped
3 2 2024-06-15 paid
4 3 2024-06-20 new
5 3 2024-08-01 shipped

order_items 23

order_id product_id quantity unit_price
1 1 1 45000
1 3 2 3500
2 2 1 89000
3 4 2 1200
3 5 1 4500

Комментарии (0)

Зарегистрируйтесь или войдите, чтобы оставить комментарий

Пока нет комментариев. Будьте первым!