Get Customer Year to Date Spending-CodePudding

I'm trying to a get list of all customers and how much money they spent each month from January to October.

I have a Users table and an Orders table. The Orders table has a user_id field that links to the Users table's id, so a user can have many orders or none.

I have is this query, which will give me the number of orders and how much money the user spent in January. How can I change this, so I can get a column for number of orders and total spent for each month? Also, for this query, I'm not getting any results for users that have 0 orders.

select users.id, users.first_name, users.last_name, count(orders.id) as num_of_orders, sum(orders.total) as total_spent
from users
left join orders on users.id = orders.user_id
where orders.created_at between '2020-01-01 00:00:00' and '2020-01-31 23:59:00'
group by users.id
order by num_of_orders asc

Any help would be greatly appreciated. Thanks in advanced.

CodePudding user response：

you need to group by month/year as well :

select users.id
       , users.first_name
       , users.last_name
       , count(orders.id) as num_of_orders
       , sum(orders.total) as total_spent
       , to_char(orders.created_at, 'yyyy-mm')
from users
left join orders on users.id = orders.user_id
group by users.id, users.first_name, users.last_name, to_char(orders.created_at, 'yyyy-mm')
order by num_of_orders asc

CodePudding user response：

Building on top of your query - build a of set of month periods (months CTE), join it with orders by the month period of the order and with users by id. Group by user and period. I assume that users.id is a primary key so you do not need to group by other users attributes. You will get zero results for months/users that have no orders too.

with month_periods(m) as
(
 select date_trunc('month', s)::date 
 from generate_series('2020-01-01'::timestamp, '2020-10-01', interval '1 month') s
)
select month_periods.m month_period, users.id, users.first_name, users.last_name, 
       count(orders.id) as num_of_orders, sum(orders.total) as total_spent 
from month_periods
left join orders on date_trunc('month', orders.created_at)::date = month_periods.m 
left join users on users.id = orders.user_id
group by users.id, month_periods.m
order by num_of_orders;