Программирование на SQL | Разработчик SQL Server в турфирме

В этом разделе сайта попытаемся разобраться с программированием баз данных. Язык называется SQL. В этом языке нет или почти нет классов, переменных, массивов и циклов. Сравнивать его с языками высокого уровня не имеет смысла. Но без него Вы не сможете написать интерфейс для настольных приложений Windows или интернет сайтов. Для Разработчика надо обязательно надо знать SQL, библиотеки обработки данных на C#, Microsoft Visual Studio, Microsoft SQL Server Management Studio или dbForge Studio for SQL Server

Разработчик SQL Server в турфирме

Данная задача может пригодится для соискателя на должность разработчика 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

 

 

Добавить комментарий

Loading