Need Help Generating NULL Entries for Months with No Orders

I have to create output that shows all fields from a table (Tbl) and create an additional column that calculates Cumulative Sum for each customer by month, (eg if a customer has two sales in April, the new column will have the Sum of those sales and any prior sales on both rows). That much I can do.

My issue is generating rows every month for every client even when they have no sales, and still having the cumulative column show the previous month’s cumulative sum correctly.

Desired output: Picture Link

Customer_ID Order_ID    Order_Date  Order_Amt_Total_USD Month_ID    Cum_Total_By_Month
John        123        4/4/2019      30                  Jun-19          120
John        124        4/12/2019     90                  Jun-19          120
Mark       null         null        null                 Jun-19           0
Sally       150        4/20/2019     50                  Jun-19           50
John        null         null       null                 Jul-19           120
Mark        165        7/7/2019      80                  Jul-19           170
Mark        166        7/7/2019      90                  Jul-19           170
Sally       160        7/5/2019      75                  Jul-19           125
John        null        null         null                Aug-19           120
Mark        null        null         null                Aug-19           170
Sally       null        null         null                Aug-19           125

I’ll list the code below, but this is a link to a SQL fiddle with sample data and the two queries of the pieces that i have working (with help from you wonderful people on this site). http://sqlfiddle.com/#!15/1d86b/11

I can generate the desired Cumulative running sum by customer and month using the first query.

I can also generate a base table that gives me a month_id for every customer for every month in the second query.

I need help making the combination of those two that will generate the desired output with the null rows for when months/Customers don’t have any sales.

Any ideas? Thanks!

-- Generates cumulative total by month by Customer, but only shows when they have a sale
SELECT 
    Customer_ID, Order_Date, order_id, Order_Amt_Total_USD,
    to_char(date_trunc('month', Order_Date), 'Mon YYYY') AS mon_text,
    (Select 
     sum(Order_Amt_Total_USD) 
        FROM tbl t2
         WHERE t2.Customer_ID = t.Customer_ID
         AND date_trunc('month', t2.Order_Date) <= t.Order_Date ) AS Cumulative
FROM    tbl t
GROUP BY mon_text, Customer_ID, Order_Date, order_id, Order_Amt_Total_USD
ORDER BY date_trunc('month', Order_Date), Customer_ID, Order_Date
;

-- Generates Proper List of All Month IDs for each Customer from entered date through today
WITH temp AS (
   SELECT date_trunc('month', Order_Date) AS mon_id
  FROM tbl
  )
Select
Customer_ID,
to_char(mon_id, 'Mon YYYY') AS mon_text
From tbl,
generate_series('2015-01-01'::date, now(), interval '1 month') mon_id
LEFT  JOIN temp USING (mon_id)
GROUP BY mon_id,Customer_ID
;

Answer

Based on your description, you can combine window functions with generate_series():

SELECT c.Customer_ID, mon,
       SUM(Order_Amt_Total_USD) as month_total,
       SUM(SUM(Order_Amt_Total_USD)) OVER (PARTITION BY c.Customer_ID ORDER BY mon) as running_total
FROM (SELECT DISTINCT Customer_Id FROM tbl) c CROSS JOIN
     generate_series('2015-01-01'::date, now(), interval '1 month') mon LEFT JOIN
     tbl t
     ON t.Customer_Id = c.customer_id and
        date_trunc('month', t.Order_Date) = mon
GROUP BY c.Customer_ID, mon
ORDER BY 1, 2;

Here is a SQL Fiddle.

Advertisement

Answer