Данная задача может пригодится для соискателя на должность разработчика SQL Server в туристической компании. Суть ее в следующем
Есть 2 таблицы:
Orders (покупки) с полями: id, price, id_client, date_order, category (тур, экскурсия, билеты, отель)
clients (клиенты) с полями: id, name, status (активный\неактивный)
1. Напишите запрос, который выведет активных клиентов и кол-во их покупок за последнюю неделю.
2. Вывести процентное соотношение активных и неактивных клиентов.
3. Вывести процент от общего кол-ва покупок по каждой категории товара
4. Вывести клиентов, которые покупали все 4 категории товаров
5. Вывести клиентов, у которых было 2 и более купленных тура за последние пол года 6. Вывести кол-во заказов за текущий месяц и кол-во заказов за аналогичный месяц прошлого года в разрезе категорий.
Решение
-- =============================================
CREATE PROCEDURE [dbo].[TestDB_SP]
AS
BEGIN
SET NOCOUNT ON;
-- ================== Административные действия ====================
BEGIN TRY
-- Создаем таблицу клиентов
IF OBJECT_ID('[dbo].[clients]', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[clients](
[id] [int] NOT NULL,
[name] [nvarchar](512) NULL,
[status] [bit] NULL,
CONSTRAINT [PK_clients] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
-- Создаем таблицу заказов
IF OBJECT_ID('[dbo].[Orders]', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[Orders](
[id] [int] IDENTITY(1,1) NOT NULL,
[price] [money] NULL,
[id_client] [int] NULL,
[date_order] [datetime] NULL,
[category] [nvarchar](64) NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
END TRY
BEGIN CATCH
PRINT 'Ошибка создания таблиц ' + CONVERT(VARCHAR, ERROR_NUMBER()) + ':' + ERROR_MESSAGE()
END CATCH
-- Удаляем предварительные данные
TRUNCATE TABLE Orders
TRUNCATE TABLE clients
-- Добавляем список клиентов
BEGIN TRY
INSERT INTO [dbo].[clients] (id,[name],[status])
VALUES
(1,'Мальцев Андрей',1),
(2,'Сидоров Иван',1),
(3,'Петрова Ольга',0),
(4,'Мальцев Алексей',0),
(5,'Харламов Петр',0),
(6,'Рогозин Иван',0),
(7,'Пушкин Александр',0),
(8,'Лермонтов Сергей',0)
END TRY
BEGIN CATCH
PRINT 'Ошибка добавления клиентов ' + CONVERT(VARCHAR, ERROR_NUMBER()) + ':' + ERROR_MESSAGE()
END CATCH
DECLARE @CurrentDate as date
SET @CurrentDate = getdate()
-- Добавляем список заказов
INSERT INTO [dbo].[Orders] (price, id_client, date_order, category )
VALUES
(300, 1, @CurrentDate, N'тур'),
(400, 1, @CurrentDate, N'билеты'),
(500, 1, @CurrentDate, N'экскурсия'),
(500, 1, @CurrentDate, N'отель'),
(700, 2, @CurrentDate, N'тур'),
(800, 2, @CurrentDate, N'отель'),
(400, 1, DATEADD(day, -30, @CurrentDate), N'тур'),
(300, 2, CONVERT(date,'2022-12-01'),N'билеты'),
(400, 3, CONVERT(date,'2023-11-01'),N'экскурсия'),
(6.1, 2, CONVERT(date,'2022-11-30'),N'отель'),
(400, 1, DATEADD(year, -1, @CurrentDate), N'билеты'),
(500, 1, DATEADD(year, -1, @CurrentDate), N'билеты'),
(500, 1, DATEADD(year, -1, @CurrentDate), N'отель')
;
-- ================== Аналитические запросы ====================
-- 1. Активные клиенты и кол-во их покупок за последнюю неделю, т.е. 7 дней
SELECT c.[name] as [Активный клиент], so.cnt as [Кол-во покупок за последнюю неделю, 7 дней] FROM [clients] c
LEFT JOIN
(SELECT o.id_client, COUNT(*) as cnt FROM [Orders] o WHERE date_order between DATEADD(day, -7, getdate()) AND getdate() GROUP BY o.id_client) as so
ON c.id=so.id_client WHERE c.[status]=1
-- 2. Процентное соотношение активных и неактивных клиентов
SELECT CONVERT(numeric(10,1),(100.0*(SELECT COUNT(*) FROM clients c WHERE c.status=1) /
(SELECT COUNT(*) FROM clients c WHERE c.status=0))) as [Процентное соотношение активных и неактивных клиентов]
-- 3. Процент от общего кол-ва покупок по каждой категории товара
SELECT o.category as [Категория товара],
CONVERT(numeric(10,1),100.0 * COUNT(*)/(SELECT COUNT(*) FROM Orders)) as [Процент от общего кол-ва покупок по каждой категории товара]
FROM Orders o Group by o.category
-- 4. Клиенты, которые покупали все 4 категории товаров
SELECT so.id_client as [Код клиента],c.[name] as [Клиент купил все 4 категории товаров]
FROM (SELECT DISTINCT o.id_client, o.category FROM [Orders] o) as so
INNER JOIN clients as c ON so.id_client = c.id
GROUP BY so.id_client,c.[name]
HAVING COUNT(so.id_client) = 4
-- 5. Клиенты, у которых было 2 и более купленных тура за последние пол года
SELECT o.id_client as [Код клиента], c.[name] as [Клиент у которого было 2 и более купленных тура за последние полгода] FROM [Orders] o
LEFT JOIN clients c ON c.id = o.id_client
WHERE o.date_order between DATEADD(MONTH, -6, getdate()) AND getdate() AND o.category = N'тур'
GROUP BY o.id_client, c.[name]
HAVING COUNT(*) >= 2
-- 6. Кол-во заказов за текущий месяц и кол-во заказов за аналогичный месяц прошлого года в разрезе категорий.
SELECT o1.Категория,
CONVERT(nvarchar(10),o1.Год1) + ' - ' + CONVERT(nvarchar(10),ISNULL(o2.Год2,o1.Год1-1)) as Год,
o1.Месяц1 as [Месяц],
o1.[Кол-во заказов1] as [Кол-во заказов в этом месяце],
--ISNULL(o2.Год2,o1.Год1-1) as Год2,
--ISNULL(o2.Месяц2,o1.Месяц1) as Месяц2,
ISNULL(o2.[Кол-во заказов2],0) as [Кол-во заказов в месяц в предыдущем году] FROM
(
SELECT o.category as [Категория], YEAR(o.date_order) as [Год1], MONTH(o.date_order) as [Месяц1],
Count(*) as [Кол-во заказов1] FROM [Orders] o
WHERE YEAR(o.date_order) = YEAR(getdate()) AND MONTH(o.date_order) = MONTH(getdate())
GROUP BY o.category,YEAR(o.date_order), MONTH(o.date_order)
) o1
LEFT JOIN
(
SELECT o.category as [Категория], YEAR(o.date_order) as [Год2], MONTH(o.date_order) as [Месяц2],
Count(*) as [Кол-во заказов2] FROM [Orders] o
WHERE YEAR(o.date_order) = YEAR(getdate())-1 AND MONTH(o.date_order) = MONTH(getdate())
GROUP BY o.category,YEAR(o.date_order), MONTH(o.date_order)
) o2
ON o2.Категория = o1.Категория AND o2.Год2 = o1.Год1-1 AND o2.Месяц2 = o2.Месяц2
END