--
-- Definition for schema stats :
--

CREATE SCHEMA stats
  AUTHORIZATION [dbo]
GO

--
-- Definition for table Clients :
--

CREATE TABLE dbo.Clients (
  id INT IDENTITY(1, 1) NOT NULL,
  Name nvarchar(50) NOT NULL,
  RegisteredAt datetime2(0) DEFAULT getdate() NOT NULL,
  CONSTRAINT PK_Clients PRIMARY KEY CLUSTERED (id)
    WITH (
      PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]
GO

--
-- Definition for table Products :
--

CREATE TABLE dbo.Products (
  id INT IDENTITY(1, 1) NOT NULL,
  Name nvarchar(200) NOT NULL,
  Price INT NOT NULL,
  CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (id)
    WITH (
      PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
  CONSTRAINT CHECK_Products_Consistency CHECK ([Price]>=(0))
)
ON [PRIMARY]
GO

--
-- Definition for table ClientFirstPurchases :
--

CREATE TABLE stats.ClientFirstPurchases (
  ClientId INT NOT NULL,
  PurchaseId INT NOT NULL,
  CONSTRAINT PK_ClientFirstPurchases_ClientPurchase PRIMARY KEY CLUSTERED (ClientId, PurchaseId)
    WITH (
      PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]
GO

--
-- Definition for table Purchases :
--

CREATE TABLE dbo.Purchases (
  id INT IDENTITY(1, 1) NOT NULL,
  TIMESTAMP datetime2(0) DEFAULT getdate() NOT NULL,
  ClientId INT NOT NULL,
  ProductId INT NOT NULL,
  CONSTRAINT PK_Purchases PRIMARY KEY CLUSTERED (id)
    WITH (
      PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]
GO

--
-- Definition for table BuyingDays :
--

CREATE TABLE stats.BuyingDays (
  id INT IDENTITY(0, 1) NOT NULL,
  DayFrom INT NOT NULL,
  DayTo INT NOT NULL,
  CONSTRAINT PK_BuyingDays PRIMARY KEY CLUSTERED (id)
    WITH (
      PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
  CONSTRAINT CHECK_BuyingDays_Consistency CHECK ([DayFrom]>=(0) AND [DayTo]>=[DayFrom])
)
ON [PRIMARY]
GO

--
-- Definition for view ClientIdsWithFirstPurchaseInBuyingDays :
--
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW stats.ClientIdsWithFirstPurchaseInBuyingDays WITH SCHEMABINDING
AS

SELECT
    BuyingDayId = BD.id,
    ClientId = C.id
FROM stats.BuyingDays BD
INNER JOIN stats.ClientFirstPurchases CFP
    ON (1 = 1) -- CROSS JOIN in an Indexed View.
INNER JOIN dbo.Purchases P
    ON (P.id = CFP.PurchaseId)
INNER JOIN dbo.Clients C
    ON (C.id = P.ClientId)
WHERE
    DATEDIFF(DAY, C.RegisteredAt, P.[TIMESTAMP]) BETWEEN BD.DayFrom AND BD.DayTo
GO

SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE UNIQUE CLUSTERED INDEX UQCL_TheView ON stats.ClientIdsWithFirstPurchaseInBuyingDays
  (BuyingDayId, ClientId)
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

--
-- Definition for synonym TheView :
--

CREATE SYNONYM stats.TheView FOR [stats].[ClientIdsWithFirstPurchaseInBuyingDays]
GO

--
-- Data for table dbo.Clients  (LIMIT 0,500)
--

SET IDENTITY_INSERT dbo.Clients ON
GO

INSERT INTO dbo.Clients (id, Name, RegisteredAt)
VALUES
  (1, N'Василий', N'2020-06-15 12:39:35')
GO

SET IDENTITY_INSERT dbo.Clients OFF
GO

--
-- Data for table dbo.Products  (LIMIT 0,500)
--

SET IDENTITY_INSERT dbo.Products ON
GO

INSERT INTO dbo.Products (id, Name, Price)
VALUES
  (1, N'Помадка', 10)
GO

SET IDENTITY_INSERT dbo.Products OFF
GO

--
-- Data for table dbo.Purchases  (LIMIT 0,500)
--

SET IDENTITY_INSERT dbo.Purchases ON
GO

INSERT INTO dbo.Purchases (id, TIMESTAMP, ClientId, ProductId)
VALUES
  (2, N'2020-06-15 12:39:59', 1, 1)
GO

INSERT INTO dbo.Purchases (id, TIMESTAMP, ClientId, ProductId)
VALUES
  (3, N'2020-06-15 12:40:43', 1, 1)
GO

SET IDENTITY_INSERT dbo.Purchases OFF
GO

--
-- Data for table stats.BuyingDays  (LIMIT 0,500)
--

SET IDENTITY_INSERT stats.BuyingDays ON
GO

INSERT INTO stats.BuyingDays (id, DayFrom, DayTo)
VALUES
  (0, 0, 5)
GO

INSERT INTO stats.BuyingDays (id, DayFrom, DayTo)
VALUES
  (1, 3, 10)
GO

SET IDENTITY_INSERT stats.BuyingDays OFF
GO

--
-- Data for table stats.ClientFirstPurchases  (LIMIT 0,500)
--

INSERT INTO stats.ClientFirstPurchases (ClientId, PurchaseId)
VALUES
  (1, 2)
GO

--
-- Definition for indices :
--

CREATE NONCLUSTERED INDEX IN_Clients_RegisteredAt ON dbo.Clients
  (RegisteredAt)
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IN_ClientFirstPurchases_Purchase ON stats.ClientFirstPurchases
  (PurchaseId)
INCLUDE (ClientId)
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IN_Purchases_ClientTimestamp ON dbo.Purchases
  (ClientId, TIMESTAMP)
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE stats.BuyingDays
ADD CONSTRAINT UQ_BuyingDays_FromTo
UNIQUE NONCLUSTERED (DayFrom, DayTo)
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

--
-- Definition for foreign keys :
--

ALTER TABLE stats.ClientFirstPurchases
ADD CONSTRAINT FK_ClientFirstPurchases_Client FOREIGN KEY (ClientId)
  REFERENCES dbo.Clients (id)
  ON UPDATE NO ACTION
  ON DELETE CASCADE
GO

ALTER TABLE stats.ClientFirstPurchases
ADD CONSTRAINT FK_ClientFirstPurchases_Purchase FOREIGN KEY (PurchaseId)
  REFERENCES dbo.Purchases (id)
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE dbo.Purchases
ADD CONSTRAINT FK_Purchases_Client FOREIGN KEY (ClientId)
  REFERENCES dbo.Clients (id)
  ON UPDATE NO ACTION
  ON DELETE CASCADE
GO

ALTER TABLE dbo.Purchases
ADD CONSTRAINT FK_Purchases_Product FOREIGN KEY (ProductId)
  REFERENCES dbo.Products (id)
  ON UPDATE NO ACTION
  ON DELETE CASCADE
GO

--
-- Definition for triggers :
--
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.TRI_Purchases_Stats ON dbo.Purchases
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN

    ; WITH L1 AS (
        SELECT
            PurchaseId = I.id,
            I.ClientId,
            RN = ROW_NUMBER() OVER (PARTITION BY I.ClientId ORDER BY I.id ASC)
        FROM INSERTED I
        WHERE
            NOT EXISTS (
                SELECT
                    NULL
                FROM stats.ClientFirstPurchases CFP
                WHERE
                    CFP.ClientId = I.ClientId
            )
    ), L2 AS (
        SELECT
            L1.PurchaseId,
            L1.ClientId
        FROM L1
        WHERE L1.RN = 1
    )
    MERGE stats.ClientFirstPurchases T
    USING L2 S
        ON (T.ClientId = S.ClientId AND T.PurchaseId = S.PurchaseId)
    WHEN NOT MATCHED BY TARGET
        THEN INSERT (ClientId, PurchaseId)
        VALUES (S.ClientId, S.PurchaseId)
    ;

END
GO

/*
    Необходимый запрос: количество клиентов, совершивших первую покупку через 0-5 дней после регистрации.

    В задании умышленно опущены требования к производительности и безопасности, поэтому я оставляю последнее
    на "как есть", а по производительности у меня два решения - первое "в лоб", второе требует
    дополнительной таблицы первых покупок, диапазонов дней, триггера и кластерной вьюхи.
    Вышеперечисленное (кроме триггера) выведено в отдельную схему stats, т.к. не является частью бизнес-архитектуры.
*/

-- Решение 1. Использует SCAN на клиентах и оптимальный SEEK на покупках.
-- Execution Plan (картинкой): https://snipboard.io/GPV8is.jpg
-- Execution Plan (схемкой): https://www.brentozar.com/pastetheplan/?id=SkcXq646L

SELECT
    ClientsCount = COUNT (*)
FROM dbo.Clients C
CROSS APPLY (
    SELECT TOP (1)
        P.[TIMESTAMP]
    FROM dbo.Purchases P
    WHERE
        P.ClientId = C.id
    ORDER BY
        P.[TIMESTAMP] ASC
) FirstPurchase
WHERE
    DATEDIFF (DAY, C.RegisteredAt, FirstPurchase.[TIMESTAMP]) BETWEEN 0 AND 5

-- Решение 2. Безусловно, это-очень-сильно-преждевременная оптимизация, т.к. требования к производительности не указаны,
-- но я обязан показать и этот вариант.
-- Решение использует триггер на Purchases, но не подразумевает удаление из этой таблицы.
-- Решение позволяет задать любое количество диапазонов дней (не только 0..5) в таблице stats.BuyingDays

; WITH L1 AS (
    SELECT
        V.BuyingDayId,
        ClientsCount = COUNT(*)
    FROM stats.TheView V WITH (NOEXPAND)
    GROUP BY
        V.BuyingDayId
)
SELECT
    BD.DayFrom,
    BD.DayTo,
    ClientsCount = ISNULL(L1.ClientsCount, 0)
FROM L1
RIGHT OUTER JOIN stats.BuyingDays BD
    ON (BD.id = L1.BuyingDayId)

-- Execution Plan (картинкой): https://snipboard.io/mOYdsT.jpg
-- Execution Plan (схемкой): https://www.brentozar.com/pastetheplan/?id=Hkq_qaN6U