SQL: Write a query to get the monthly average order value-CodePudding

I have 3 tables:

Orders: Columns (id, total_price, user_id, created_at)

Order_status_history: Columns (order_id, order_status, updated_at)

All_days: Column (date_day)

How do i write a query to get the monthly average order value?

CodePudding user response：

I've created this testing version which includes a temp table:

Declare @TestData TABLE
(
  ID             integer,
  total_price    decimal(10,2),
  user_id        integer,
  created_at     datetime
);

INSERT INTO @TestData (ID, total_price, user_id, created_at) select 1, 99.99,  21, '2022-01-11 07:21:01.00'
INSERT INTO @TestData (ID, total_price, user_id, created_at) select 2, 13.07,  17, '2022-01-11 13:50:23.11'
INSERT INTO @TestData (ID, total_price, user_id, created_at) select 3, 2.23,   21, '2022-01-13 04:01:36.00'
INSERT INTO @TestData (ID, total_price, user_id, created_at) select 4, 108.07, 13, '2022-02-03 22:39:04.23'
INSERT INTO @TestData (ID, total_price, user_id, created_at) select 5, 313.44, 18, '2022-02-04 06:18:02.02'

select 
       datepart(year, o1.created_at) as OrdYear,
       datepart(month, o1.created_at) as OrdMonth,
       sum(total_price) as MonthlyAvg
from @TestData as o1
group by datepart(year, o1.created_at),
         datepart(month, o1.created_at)

And here's how you do it with your table:

select 
       datepart(year, o1.created_at) as OrdYear,
       datepart(month, o1.created_at) as OrdMonth,
       sum(total_price) as MonthlyAvg
from @Orders as o1
group by datepart(year, o1.created_at),
         datepart(month, o1.created_at)

Here's how to avoid the group by:

select distinct
       datepart(year, o1.created_at) as OrdYear,
       datepart(month, o1.created_at) as OrdMonth,
       sum(total_price) over(partition by datepart(year, o1.created_at), datepart(month, o1.created_at) ) as MonthlyAvg
from Orders as o1