Revenue Over Time

StrataScratch

#Amazon #ID_10314

Question:

Find the 3-month rolling average of total revenue from purchases given a table with users, their purchase amount, and date purchased. Do not include returns which are represented by negative purchase values. Output the year-month (YYYY-MM) and 3-month rolling average of revenue, sorted from earliest month to latest month.

A 3-month rolling average is defined by calculating the average total revenue from all user purchases for the current month and previous two months. The first two months will not be a true 3-month rolling average since we are not given data from last year. Assume each month has at least one purchase.

table: amazon_purchases

How to calculate 3-month rolling average


  • To get the previous 2 months revenue we are going to apply self join.
  • Left join is used so that in case when the previous month revenue is not there for month 1 and 2 then null should be there in the row.

STEP 1: Calculate total revenue for each month-year and exclude negative amount value.

select  month(created_at) as month1, year(created_at) as year1,
sum(purchase_amt) as amount from amazon_purchases
where purchase_amt>0
group by month(created_at) , year(created_at)

STEP 2:

with cte as(
select  month(created_at) as month1, year(created_at) as year1,
sum(purchase_amt) as amount from amazon_purchases
where purchase_amt>0
group by month(created_at) , year(created_at)
)
select *
from cte as c1
left join cte as c2 on c1.month1-1 = c2.month1 
left join cte as c3 on c1.month1-2 = c3.month1
order by c1.year1,c1.month1


Solution:

with cte as(
select  month(created_at) as month1, year(created_at) as year1,
sum(purchase_amt) as amount from amazon_purchases
where purchase_amt>0
group by month(created_at) , year(created_at)
)
select 
concat(c1.year1,'-',RIGHT('0' + CAST(c1.month1 AS VARCHAR(2)), 2)) as year_month,
case when c1.month1>2 then (sum(isnull(c1.amount,0)+isnull(c2.amount,0)+isnull(c3.amount,0)) )/3.0 else (1.0*sum(isnull(c1.amount,0)+isnull(c2.amount,0)+isnull(c3.amount,0)) )/c1.month1 end as amount
from cte as c1
 left join cte as c2 on c1.month1-1 = c2.month1 
left join cte as c3 on c1.month1-2 = c3.month1
group by c1.month1,c1.year1
order by c1.year1,c1.month1

 Output:

SQL Script:

USE [StrataScratch]
GO
/****** Object:  Table [dbo].[amazon_purchases]    Script Date: 12-09-2024 22:26:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[amazon_purchases](
	[user_id] [int] NULL,
	[created_at] [datetime] NULL,
	[purchase_amt] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (10, CAST(N'2020-01-01T00:00:00.000' AS DateTime), 3742)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (11, CAST(N'2020-01-04T00:00:00.000' AS DateTime), 1290)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (12, CAST(N'2020-01-07T00:00:00.000' AS DateTime), 4249)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (13, CAST(N'2020-01-10T00:00:00.000' AS DateTime), 4899)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (14, CAST(N'2020-01-13T00:00:00.000' AS DateTime), -4656)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (15, CAST(N'2020-01-16T00:00:00.000' AS DateTime), -655)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (16, CAST(N'2020-01-19T00:00:00.000' AS DateTime), 4659)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (17, CAST(N'2020-01-22T00:00:00.000' AS DateTime), 3813)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (18, CAST(N'2020-01-25T00:00:00.000' AS DateTime), -2623)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (19, CAST(N'2020-01-28T00:00:00.000' AS DateTime), 3640)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (20, CAST(N'2020-01-31T00:00:00.000' AS DateTime), -1028)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (21, CAST(N'2020-02-03T00:00:00.000' AS DateTime), 2715)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (22, CAST(N'2020-02-06T00:00:00.000' AS DateTime), 1592)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (23, CAST(N'2020-02-09T00:00:00.000' AS DateTime), 1516)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (24, CAST(N'2020-02-12T00:00:00.000' AS DateTime), 2700)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (25, CAST(N'2020-02-15T00:00:00.000' AS DateTime), 1543)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (26, CAST(N'2020-02-18T00:00:00.000' AS DateTime), 4210)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (27, CAST(N'2020-02-21T00:00:00.000' AS DateTime), -608)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (28, CAST(N'2020-02-24T00:00:00.000' AS DateTime), 2855)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (29, CAST(N'2020-02-27T00:00:00.000' AS DateTime), 3564)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (30, CAST(N'2020-03-01T00:00:00.000' AS DateTime), 3037)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (31, CAST(N'2020-03-04T00:00:00.000' AS DateTime), 2552)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (32, CAST(N'2020-03-07T00:00:00.000' AS DateTime), 2487)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (33, CAST(N'2020-03-10T00:00:00.000' AS DateTime), -1933)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (34, CAST(N'2020-03-13T00:00:00.000' AS DateTime), 4973)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (35, CAST(N'2020-03-16T00:00:00.000' AS DateTime), 4475)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (36, CAST(N'2020-03-19T00:00:00.000' AS DateTime), -913)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (37, CAST(N'2020-03-22T00:00:00.000' AS DateTime), 2265)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (38, CAST(N'2020-03-25T00:00:00.000' AS DateTime), 3525)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (39, CAST(N'2020-03-28T00:00:00.000' AS DateTime), 3251)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (40, CAST(N'2020-03-31T00:00:00.000' AS DateTime), 3055)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (41, CAST(N'2020-04-03T00:00:00.000' AS DateTime), 4828)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (42, CAST(N'2020-04-06T00:00:00.000' AS DateTime), -3230)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (43, CAST(N'2020-04-09T00:00:00.000' AS DateTime), 4772)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (44, CAST(N'2020-04-12T00:00:00.000' AS DateTime), -775)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (45, CAST(N'2020-04-15T00:00:00.000' AS DateTime), 2051)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (46, CAST(N'2020-04-18T00:00:00.000' AS DateTime), 1974)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (47, CAST(N'2020-04-21T00:00:00.000' AS DateTime), 2311)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (48, CAST(N'2020-04-24T00:00:00.000' AS DateTime), -593)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (49, CAST(N'2020-04-27T00:00:00.000' AS DateTime), 2583)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (50, CAST(N'2020-04-30T00:00:00.000' AS DateTime), 3414)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (51, CAST(N'2020-05-03T00:00:00.000' AS DateTime), 4216)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (52, CAST(N'2020-05-06T00:00:00.000' AS DateTime), 2420)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (53, CAST(N'2020-05-09T00:00:00.000' AS DateTime), 3138)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (54, CAST(N'2020-05-12T00:00:00.000' AS DateTime), 1036)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (55, CAST(N'2020-05-15T00:00:00.000' AS DateTime), 2543)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (56, CAST(N'2020-05-18T00:00:00.000' AS DateTime), 2127)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (57, CAST(N'2020-05-21T00:00:00.000' AS DateTime), 1026)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (58, CAST(N'2020-05-24T00:00:00.000' AS DateTime), 1650)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (59, CAST(N'2020-05-27T00:00:00.000' AS DateTime), 3514)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (60, CAST(N'2020-05-30T00:00:00.000' AS DateTime), 3030)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (61, CAST(N'2020-06-02T00:00:00.000' AS DateTime), 4014)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (62, CAST(N'2020-06-05T00:00:00.000' AS DateTime), 4390)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (63, CAST(N'2020-06-08T00:00:00.000' AS DateTime), 4459)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (64, CAST(N'2020-06-11T00:00:00.000' AS DateTime), -2850)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (65, CAST(N'2020-06-14T00:00:00.000' AS DateTime), 4369)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (66, CAST(N'2020-06-17T00:00:00.000' AS DateTime), 1895)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (67, CAST(N'2020-06-20T00:00:00.000' AS DateTime), 2184)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (68, CAST(N'2020-06-23T00:00:00.000' AS DateTime), -765)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (69, CAST(N'2020-06-26T00:00:00.000' AS DateTime), 2001)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (70, CAST(N'2020-06-29T00:00:00.000' AS DateTime), 4375)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (71, CAST(N'2020-07-02T00:00:00.000' AS DateTime), 4104)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (72, CAST(N'2020-07-05T00:00:00.000' AS DateTime), 4223)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (73, CAST(N'2020-07-08T00:00:00.000' AS DateTime), 633)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (74, CAST(N'2020-07-11T00:00:00.000' AS DateTime), 3352)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (75, CAST(N'2020-07-14T00:00:00.000' AS DateTime), 4421)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (76, CAST(N'2020-07-17T00:00:00.000' AS DateTime), -4284)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (77, CAST(N'2020-07-20T00:00:00.000' AS DateTime), 1904)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (78, CAST(N'2020-07-23T00:00:00.000' AS DateTime), 4928)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (79, CAST(N'2020-07-26T00:00:00.000' AS DateTime), -1680)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (80, CAST(N'2020-07-29T00:00:00.000' AS DateTime), 1744)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (81, CAST(N'2020-08-01T00:00:00.000' AS DateTime), 3797)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (82, CAST(N'2020-08-04T00:00:00.000' AS DateTime), 4053)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (83, CAST(N'2020-08-07T00:00:00.000' AS DateTime), -1829)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (84, CAST(N'2020-08-10T00:00:00.000' AS DateTime), 2196)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (85, CAST(N'2020-08-13T00:00:00.000' AS DateTime), 1792)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (86, CAST(N'2020-08-16T00:00:00.000' AS DateTime), 4050)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (87, CAST(N'2020-08-19T00:00:00.000' AS DateTime), 1468)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (88, CAST(N'2020-08-22T00:00:00.000' AS DateTime), 2191)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (89, CAST(N'2020-08-25T00:00:00.000' AS DateTime), -594)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (90, CAST(N'2020-08-28T00:00:00.000' AS DateTime), 2318)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (91, CAST(N'2020-08-31T00:00:00.000' AS DateTime), 1631)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (92, CAST(N'2020-09-03T00:00:00.000' AS DateTime), 3804)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (93, CAST(N'2020-09-06T00:00:00.000' AS DateTime), -2032)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (94, CAST(N'2020-09-09T00:00:00.000' AS DateTime), 3599)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (95, CAST(N'2020-09-12T00:00:00.000' AS DateTime), 3043)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (96, CAST(N'2020-09-15T00:00:00.000' AS DateTime), 1999)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (97, CAST(N'2020-09-18T00:00:00.000' AS DateTime), -1334)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (98, CAST(N'2020-09-21T00:00:00.000' AS DateTime), 4344)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (99, CAST(N'2020-09-24T00:00:00.000' AS DateTime), -3960)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (100, CAST(N'2020-09-27T00:00:00.000' AS DateTime), 4316)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (101, CAST(N'2020-09-30T00:00:00.000' AS DateTime), 3722)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (102, CAST(N'2020-10-03T00:00:00.000' AS DateTime), 1433)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (103, CAST(N'2020-10-06T00:00:00.000' AS DateTime), -1045)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (104, CAST(N'2020-10-09T00:00:00.000' AS DateTime), 3035)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (105, CAST(N'2020-10-12T00:00:00.000' AS DateTime), 4865)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (106, CAST(N'2020-10-15T00:00:00.000' AS DateTime), -3330)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (107, CAST(N'2020-10-18T00:00:00.000' AS DateTime), 4228)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (108, CAST(N'2020-10-21T00:00:00.000' AS DateTime), -1834)
GO
INSERT [dbo].[amazon_purchases] ([user_id], [created_at], [purchase_amt]) VALUES (109, CAST(N'2020-10-24T00:00:00.000' AS DateTime), 1749)
GO


Comments (0)
Questions List