Monthly Percentage Difference

Given a table of purchases by date, calculate the month-over-month percentage change in revenue. The output should include the year-month date (YYYY-MM) and percentage change, rounded to the 2nd decimal point, and sorted from the beginning of the year to the end of the year.

The percentage change column will be populated from the 2nd month forward and can be calculated as ((this month's revenue - last month's revenue) / last month's revenue)*100.

table : sf_transactions


Solution:
with cte as 
(
select  convert(varchar(7), created_at, 126) as year_month,sum(value) as monthly_revenu,
lag(sum(value),1) over ( order by convert(varchar(7), created_at, 126)) as prev_month_revenu
from sf_transactions
group by convert(varchar(7), created_at, 126)
)
select year_month, convert(decimal(8,2),100.00 * (monthly_revenu - prev_month_revenu)/(prev_month_revenu)) as revenue_diff_pct from cte

Output:


SQL Script:
CREATE TABLE [dbo].[sf_transactions](
	[id] [int] NULL,
	[created_at] [date] NULL,
	[value] [int] NULL,
	[puchase_id] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (1, CAST(N'2019-01-01' AS Date), 172692, 43)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (2, CAST(N'2019-01-05' AS Date), 177194, 36)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (3, CAST(N'2019-01-09' AS Date), 109513, 30)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (4, CAST(N'2019-01-13' AS Date), 164911, 30)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (5, CAST(N'2019-01-17' AS Date), 198872, 39)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (6, CAST(N'2019-01-21' AS Date), 184853, 31)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (7, CAST(N'2019-01-25' AS Date), 186817, 26)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (8, CAST(N'2019-01-29' AS Date), 137784, 22)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (9, CAST(N'2019-02-02' AS Date), 140032, 25)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (10, CAST(N'2019-02-06' AS Date), 116948, 43)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (11, CAST(N'2019-02-10' AS Date), 162515, 25)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (12, CAST(N'2019-02-14' AS Date), 114256, 12)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (13, CAST(N'2019-02-18' AS Date), 197465, 48)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (14, CAST(N'2019-02-22' AS Date), 120741, 20)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (15, CAST(N'2019-02-26' AS Date), 100074, 49)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (16, CAST(N'2019-03-02' AS Date), 157548, 19)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (17, CAST(N'2019-03-06' AS Date), 105506, 16)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (18, CAST(N'2019-03-10' AS Date), 189351, 46)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (19, CAST(N'2019-03-14' AS Date), 191231, 29)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (20, CAST(N'2019-03-18' AS Date), 120575, 44)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (21, CAST(N'2019-03-22' AS Date), 151688, 47)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (22, CAST(N'2019-03-26' AS Date), 102327, 18)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (23, CAST(N'2019-03-30' AS Date), 156147, 25)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (24, CAST(N'2019-04-03' AS Date), 192530, 36)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (25, CAST(N'2019-04-07' AS Date), 154765, 42)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (26, CAST(N'2019-04-11' AS Date), 113336, 12)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (27, CAST(N'2019-04-15' AS Date), 129073, 50)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (28, CAST(N'2019-04-19' AS Date), 123477, 21)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (29, CAST(N'2019-04-23' AS Date), 182142, 31)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (30, CAST(N'2019-04-27' AS Date), 116546, 39)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (31, CAST(N'2019-05-01' AS Date), 174748, 26)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (32, CAST(N'2019-05-05' AS Date), 155693, 42)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (33, CAST(N'2019-05-09' AS Date), 103012, 25)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (34, CAST(N'2019-05-13' AS Date), 187960, 33)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (35, CAST(N'2019-05-17' AS Date), 101202, 18)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (36, CAST(N'2019-05-21' AS Date), 112522, 10)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (37, CAST(N'2019-05-25' AS Date), 195969, 37)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (38, CAST(N'2019-05-29' AS Date), 117284, 40)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (39, CAST(N'2019-06-02' AS Date), 112956, 36)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (40, CAST(N'2019-06-06' AS Date), 174157, 29)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (41, CAST(N'2019-06-10' AS Date), 125975, 45)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (42, CAST(N'2019-06-14' AS Date), 110340, 29)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (43, CAST(N'2019-06-18' AS Date), 143066, 31)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (44, CAST(N'2019-06-22' AS Date), 153270, 11)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (45, CAST(N'2019-06-26' AS Date), 139635, 29)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (46, CAST(N'2019-06-30' AS Date), 157071, 35)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (47, CAST(N'2019-07-04' AS Date), 166552, 18)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (48, CAST(N'2019-07-08' AS Date), 197587, 34)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (49, CAST(N'2019-07-12' AS Date), 103958, 37)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (50, CAST(N'2019-07-16' AS Date), 111305, 28)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (51, CAST(N'2019-07-20' AS Date), 190266, 39)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (52, CAST(N'2019-07-24' AS Date), 116060, 44)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (53, CAST(N'2019-07-28' AS Date), 163802, 48)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (54, CAST(N'2019-08-01' AS Date), 188558, 21)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (55, CAST(N'2019-08-05' AS Date), 166528, 49)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (56, CAST(N'2019-08-09' AS Date), 141463, 28)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (57, CAST(N'2019-08-13' AS Date), 120110, 34)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (58, CAST(N'2019-08-17' AS Date), 159368, 12)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (59, CAST(N'2019-08-21' AS Date), 184900, 46)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (60, CAST(N'2019-08-25' AS Date), 190372, 38)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (61, CAST(N'2019-08-29' AS Date), 195877, 15)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (62, CAST(N'2019-09-02' AS Date), 143221, 21)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (63, CAST(N'2019-09-06' AS Date), 160413, 41)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (64, CAST(N'2019-09-10' AS Date), 183919, 43)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (65, CAST(N'2019-09-14' AS Date), 134535, 34)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (66, CAST(N'2019-09-18' AS Date), 188646, 31)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (67, CAST(N'2019-09-22' AS Date), 122706, 27)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (68, CAST(N'2019-09-26' AS Date), 160016, 21)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (69, CAST(N'2019-09-30' AS Date), 186777, 21)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (70, CAST(N'2019-10-04' AS Date), 165442, 50)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (71, CAST(N'2019-10-08' AS Date), 172445, 43)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (72, CAST(N'2019-10-12' AS Date), 167910, 21)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (73, CAST(N'2019-10-16' AS Date), 116646, 35)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (74, CAST(N'2019-10-20' AS Date), 163287, 15)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (75, CAST(N'2019-10-24' AS Date), 187293, 43)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (76, CAST(N'2019-10-28' AS Date), 144823, 11)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (77, CAST(N'2019-11-01' AS Date), 118317, 10)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (78, CAST(N'2019-11-05' AS Date), 166105, 38)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (79, CAST(N'2019-11-09' AS Date), 121128, 11)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (80, CAST(N'2019-11-13' AS Date), 177355, 38)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (81, CAST(N'2019-11-17' AS Date), 176442, 50)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (82, CAST(N'2019-11-21' AS Date), 129837, 10)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (83, CAST(N'2019-11-25' AS Date), 122363, 38)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (84, CAST(N'2019-11-29' AS Date), 125469, 10)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (85, CAST(N'2019-12-03' AS Date), 109657, 20)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (86, CAST(N'2019-12-07' AS Date), 108782, 35)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (87, CAST(N'2019-12-11' AS Date), 149235, 18)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (88, CAST(N'2019-12-15' AS Date), 187243, 36)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (89, CAST(N'2019-12-19' AS Date), 152538, 20)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (90, CAST(N'2019-12-23' AS Date), 178861, 34)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (91, CAST(N'2019-12-27' AS Date), 122675, 30)
GO
INSERT [dbo].[sf_transactions] ([id], [created_at], [value], [puchase_id]) VALUES (92, CAST(N'2019-12-31' AS Date), 104037, 18)
GO


Comments (0)
Questions List