I have the following tables (simplified below):
Orders:
<id: 1, shipping: 6.0, price: 20.0>
<id: 2, shipping: 10.0, price: 30.0>
<id: 3, shipping: 7.0, price: 12.0>
<id: 4, shipping: 5.0, price: 0.0> #0 dollars because it was updated after return
Sales:
<id: 1, order_id: 1, price:10.0, qty:2, date: "2020-06-01T01:16:15-04:00">
<id: 2, order_id: 1, price:9.0, qty: 1, date: "2020-06-01T01:16:15-04:00">
<id: 3, order_id: 2, price:15.0, qty:2, date: "2020-06-01T01:23:53-04:00">
<id: 4, order_id: 3, price:4.0, qty: 1, date: "2020-06-01T20:28:18-04:00">
<id: 5, order_id: 3, price:4.0, qty: 2, date: "2020-06-01T20:31:15-04:00">
<id: 6, order_id: 4, price:29.0, qty:1, date: "2020-06-03T20:16:15-04:00">
Refunds:
<id: 1, order_id: 1, qty:1, amount: 9.0, date: "2020-06-01T01:23:15-04:00">
<id: 2, order_id: 4, qty:1, amount: 29.0, date: "2020-06-04T03:34:53-04:00">
I'm writing raw sql to calculate the shipping (i.e sum(orders.shipping)), total orders (i.e COUNT(DISTINCT orders.id)) and net sales(i.e sales.price * sales.qty - COALESCE(refunds.refund_amount, 0)) grouped by the days. The search is going to take a min_date and max_date in format: YYYY-MM-DDThh24:mi:ss to filter out the sales or refunds that aren't within the date range. The problem I'm having is using generate_series to add in all the days that don't exist in the tables with the values all set to 0. So a sample response if min_date = 2020-06-01T00:00:00 and max_date = 2020-06-05T23:59:59 would be something like:
"2020-06-01": {shipping: 6, total_orders: 3, net_sales: 62.0},
"2020-06-02": {shipping: 0, total_orders: 0, net_sales: 0}, --> newly added
"2020-06-03": {shipping: 5, total_orders: 1, net_sales: 29},
"2020-06-04": {shipping: 0, total_orders: 1, net_sales: -29.0},
"2020-06-05": {shipping: 0, total_orders: 0, net_sales: 0} --> newly added.
Can anyone help me receive the desired results above. I've seen examples but I can't get it to work with my scenario. Thanks!
I think that this would do what you want:
select
d.dt,
o.shipping,
s.total_orders,
coalesce(s.sales_amount, 0) - coalesce(r.refound_amount, 0) net_sales
from generate_series(?::timestamp, ?::timestamp, interval '1 day') d(dt)
left join lateral (
select
count(distinct order_id) total_orders,
sum(price * quantity) sales_amount,
array_agg(order_id) order_ids
from sales s
where s.date >= d.dt and s.date < d.dt + interval '1 day'
) s on true
left join lateral (
select sum(o.shipping) shipping
from orders o
where o.id = any(s.order_ids)
) o on true
left join lateral (
select sum(r.amount) refound_amount
from refunds r
where r.order_id = any(s.order_ids)
) r on true
The query starts by generating all dates within the given interval (the ? represent the two date parameters).
Then, we use a lateral join with an aggregate query to bring the information about all sales that occur within the period. Another later join brings the shippings that correspond to the order_ids selected by the first lateral join, and another one brings the corresponding refunds.