day2.sql

create schema day2

--------------TAbLE CREATION--------------
create table day2.SF_Transaction
(
	ID_generated int primary key identity(1,1),
	Purchases varchar(20),
	Revenue int, 
	Date date
)

--------------DATA INSERTION--------------
INSERT INTO day2.SF_Transaction (Purchases, Revenue, Date)
VALUES
-- January
('Books', 200, '2026-01-05'),
('Electronics', 1500, '2026-01-12'),
('Clothes', 800, '2026-01-20'),
('Shoes', 400, '2026-01-25'),

-- February
('Furniture', 2500, '2026-02-03'),
('Books', 300, '2026-02-10'),
('Electronics', 1800, '2026-02-15'),
('Shoes', 600, '2026-02-22'),

-- March
('Clothes', 1200, '2026-03-05'),
('Electronics', 2200, '2026-03-12'),
('Books', 500, '2026-03-18'),
('Furniture', 2700, '2026-03-25'),

-- April
('Shoes', 700, '2026-04-04'),
('Clothes', 1500, '2026-04-11'),
('Electronics', 2000, '2026-04-19'),
('Books', 400, '2026-04-27'),

-- May
('Furniture', 3000, '2026-05-06'),
('Electronics', 2500, '2026-05-13'),
('Shoes', 800, '2026-05-20'),
('Clothes', 1600, '2026-05-28'),

-- June
('Books', 600, '2026-06-02'),
('Electronics', 2800, '2026-06-10'),
('Furniture', 3200, '2026-06-18'),
('Shoes', 900, '2026-06-25'),

-- July
('Clothes', 1700, '2026-07-03'),
('Electronics', 2600, '2026-07-12'),
('Books', 700, '2026-07-19'),
('Furniture', 3500, '2026-07-27'),

-- August
('Shoes', 1000, '2026-08-05'),
('Clothes', 1800, '2026-08-13'),
('Electronics', 3000, '2026-08-20'),
('Books', 800, '2026-08-28'),

-- September
('Furniture', 3700, '2026-09-06'),
('Electronics', 3200, '2026-09-14'),
('Shoes', 1100, '2026-09-21'),
('Clothes', 1900, '2026-09-29'),

-- October
('Books', 900, '2026-10-03'),
('Electronics', 3400, '2026-10-11'),
('Furniture', 3900, '2026-10-19'),
('Shoes', 1200, '2026-10-27'),

-- November
('Clothes', 2000, '2026-11-05'),
('Electronics', 3600, '2026-11-13'),
('Books', 1000, '2026-11-21'),
('Furniture', 4100, '2026-11-29'),

-- December
('Shoes', 1300, '2026-12-04'),
('Clothes', 2100, '2026-12-12'),
('Electronics', 3800, '2026-12-20'),
('Books', 1100, '2026-12-28');

--------------QUERIES--------------  
-----------------------------------

--STEP 1 GET MONTH
create view day2.VW_Month
as
(
	select 
		  T.Date,
		  month(T.Date) as Month_Number,
		  datename(month,T.Date) as Month_Name
	from day2.SF_Transaction T
)

select * from day2.VW_Month

--STEP 2 Revenue BY EACH MONTH
create view day2.VW_Revenue_BY_EACH_MONTH
as 
(
	select V.Month_Number,
	       V.Month_Name,
	       sum(T.Revenue) as 'Total Revenue'
	from day2.SF_Transaction T join day2.VW_Month V
	on T.Date = V.Date
	group by V.Month_Number, V.Month_Name
)

select * 
from day2.VW_Revenue_BY_EACH_MONTH V
order by V.Month_Number

--STEP 3 CALCULATION
create view day2.Substraction
as
(
	select V.Month_Number,
	       V.Month_Name,
		   V.[Total Revenue] - lag(V.[Total Revenue]) over (order by V.Month_Number) as Revenue_Substraction
	from day2.VW_Revenue_BY_EACH_MONTH V
    
)

select * from day2.Substraction s order by s.Month_Number

create view day2.Division_process
as
(
	select V.Month_Number,
	       V.Month_Name,
		   lag(V.[Total Revenue]) over (order by V.Month_Number) as Revenue_Division
	from day2.VW_Revenue_BY_EACH_MONTH V
    
)

select * from day2.Division_process d order by d.Month_Number

--STEP 4 Percentage_Change
select 
	    D.Month_Name,
		format(datefromparts(2026, D.Month_Number, 1),'yyyy-MM') as Year_Month,
		cast( round((S.Revenue_Substraction * 100.0 / D.Revenue_Division),2) as decimal(6,2) ) as Percentage_Change
from day2.Division_process D join day2.Substraction S
on D.Month_Number = S.Month_Number
order by Year_Month