I need to find the number of users that were invoiced for an amount greater than 0 in the previous month and were not invoiced in the current month

Question

I need to find the number of users that were invoiced for an amount greater than 0 in the previous month and were not invoiced in the current month. This calcualtion is to be done for 12 months in a single query. Output should be as below.

Month Count

01/07/2019 50

01/08/2019 34

01/09/2019 23

01/10/2019 98

01/11/2019 10

01/12/2019 5

01/01/2020 32

01/02/2020 65

01/03/2020 23

01/04/2020 12

01/05/2020 64

01/06/2020 54

01/07/2020 78

I am able to get the value only for one month. I want to get it for all months in a single query.

This is my current query:

SELECT COUNT(DISTINCT TWO_MONTHS_AGO.USER_ID), TWO_MONTHS_AGO.MONTH AS INVOICE_MONTH
FROM (
    SELECT USER_ID, LAST_DAY(invoice_ct_dt)) AS MONTH
    FROM table a AS ID
        WHERE invoice_amt > 0
        AND LAST_DAY(invoice_ct_dt)) = ADD_MONTHS(LAST_DAY(CURRENT_DATE - 1), - 2)
    GROUP BY user_id
    ) AS TWO_MONTHS_AGO
LEFT JOIN (
    SELECT user_id,LAST_DAY(invoice_ct_dt)) AS MONTH
    FROM table a AS ID
    AND LAST_DAY(invoice_ct_dt)) = ADD_MONTHS(LAST_DAY(CURRENT_DATE - 1), - 1)
    GROUP BY USER_ID
    ) AS ONE_MONTH_AGO ON TWO_MONTHS_AGO.USER_ID = ONE_MONTH_AGO.USER_ID
WHERE ONE_MONTH_AGO.USER_ID IS NULL
GROUP BY INVOICE_MONTH;

Thank you in advance. Lona

score 0 · Answer 1 · answered Aug 30 '20 at 12:40

Probably lots of different approaches but the way I would do it is as follows:

Summarise data by user and month for the last 13 months (you need 12 months plus the previous month to that first month
Compare "this" month (that has data) to "next" month and select records where there is no "next" month data
Summarise this dataset by month and distinct userid

For example, assuming a table created as follows:

create table INVOICE_DATA (
USERID varchar(4),
INVOICE_DT date,
INVOICE_AMT NUMBER(10,2)
);

the following query should give you what you want - you may need to adjust it depending on whether you are including this month, or only up to the end of last month, in your calculation, etc.:

--Summarise data by user and month
WITH MONTH_SUMMARY AS 
(
  SELECT USERID
  ,TO_CHAR(INVOICE_DT,'YYYY-MM') "INVOICE_MONTH"
  ,TO_CHAR(ADD_MONTHS(INVOICE_DT,1),'YYYY-MM') "NEXT_MONTH"
  ,SUM(INVOICE_AMT) "MONTHLY_TOTAL"
  FROM INVOICE_DATA
  WHERE INVOICE_DT >= TRUNC(ADD_MONTHS(current_date(),-13),'MONTH') -- Last 13 months of data
  GROUP BY 1,2,3
),
--Get data for users with invoices in this month but not the next month
USER_DATA AS 
  (
  SELECT USERID, INVOICE_MONTH, MONTHLY_TOTAL
  FROM MONTH_SUMMARY MS_THIS
  WHERE NOT EXISTS 
  (
    SELECT USERID
    FROM MONTH_SUMMARY MS_NEXT
    WHERE 
      MS_THIS.USERID = MS_NEXT.USERID AND
      MS_THIS.NEXT_MONTH = MS_NEXT.INVOICE_MONTH
   )
   AND MS_THIS.INVOICE_MONTH < TO_CHAR(current_date(),'YYYY-MM') -- Don't include this month as obviously no next month to compare to 
)
SELECT INVOICE_MONTH, COUNT(DISTINCT USERID) "USER_COUNT"
FROM USER_DATA
GROUP BY INVOICE_MONTH
ORDER BY INVOICE_MONTH
 ;

I need to find the number of users that were invoiced for an amount greater than 0 in the previous month and were not invoiced in the current month

1 Answers1