Query to Get the Customers Who Haven't Transacted in the Past Two Months

Query to get the customers who haven't transacted in the past two months

Here is the first solution, which could be used as a working base.

CREATE TABLE #orders(OrderId int identity(1,1), CustNum int, Orderdate date)

-- using system columns to populate demo data (I'm lazy)
INSERT INTO #orders(CustNum,Orderdate)
    SELECT system_type_id, DATEADD(month,column_id*-1,GETDATE())
    FROM sys.all_columns

-- Possible Solution 1:
-- Getting all your customers who haven't placed an order in the last 2 months
SELECT *
FROM (
        -- All your customers
        SELECT DISTINCT CustNum
        FROM #orders
        EXCEPT
        -- All customers who have a transaction in the last 2 months
        SELECT DISTINCT CustNum
        FROM #orders
        WHERE Orderdate >= DATEADD(month,-2,GETDATE())
    ) dat

DROP TABLE #orders

Based on the fact that a customer table is available, this can also be a solution:

CREATE TABLE #orders(OrderId int identity(1,1), CustNum int, Orderdate date)

-- using system columns to populate demo data (I'm lazy)
INSERT INTO #orders(CustNum,Orderdate)
    SELECT system_type_id, DATEADD(month,column_id*-1,GETDATE())
    FROM sys.all_columns

CREATE TABLE #customers(CustNum int)

-- Populate customer table with demo data
INSERT INTO #customers(CustNum)
    SELECT DISTINCT custNum 
    FROM #orders

-- Possible Solution 2:
SELECT 
    COUNT(*) as noTransaction
FROM #customers as c
LEFT JOIN(
        -- All customers who have a transaction in the last 2 months
        SELECT DISTINCT CustNum
        FROM #orders
        WHERE Orderdate >= DATEADD(month,-2,GETDATE())
    ) t
    ON c.CustNum = t.CustNum
WHERE t.CustNum IS NULL

DROP TABLE #orders
DROP TABLE #customers

You'll receive a counted value of each customer which hasn't bought anything in the last 2 months. As I've read it, you try to run this query regularly (maybe for a special newsletter or something like that). If you won't count, you'll getting the customer numbers which can be used for further processes.

Solution with rolling months
After clearing the question, this should make the thing you're looking for. It generates an output based on rolling months.

CREATE TABLE #orders(OrderId int identity(1,1), CustNum int, Orderdate date)

-- using system columns to populate demo data (I'm lazy)
INSERT INTO #orders(CustNum,Orderdate)
    SELECT system_type_id, DATEADD(month,column_id*-1,GETDATE())
    FROM sys.all_columns

CREATE TABLE #customers(CustNum int)

-- Populate customer table with demo data
INSERT INTO #customers(CustNum)
    SELECT DISTINCT custNum 
    FROM #orders

-- Possible Solution with rolling months:
-- first of all, get all available months
-- this can be also achieved with an temporary table (which may be better)
-- but in case, that you can't use an procedure, I'm using the CTE this way.
;WITH months AS(
    SELECT DISTINCT DATEPART(month,orderdate) as allMonths, 
                    DATEPART(year,orderdate) as allYears
    FROM #orders
)
SELECT m.allMonths,m.allYears, monthyCustomers.noBuyer
FROM months m
OUTER APPLY(
        SELECT N'01/'+m.allMonths+N'/'+m.allYears as monthString, COUNT(c.CustNum) as noBuyer
        FROM #customers as c
        LEFT JOIN(
                -- All customers who have a transaction in the last 2 months
                SELECT DISTINCT CustNum
                FROM #orders
                -- to get the 01/01/2015 out of 03/2015
                WHERE Orderdate BETWEEN DATEADD(month,-2,
                                            CONVERT(date,N'01/'+CONVERT(nvarchar(max),m.allMonths)
                                            +N'/'+CONVERT(nvarchar(max),m.allYears)))
                                -- to get the 31/03/2015 out of the 03/2015                     
                                AND DATEADD(day,-1,
                                        DATEADD(month,+1,CONVERT(date,N'01/'+
                                            CONVERT(nvarchar(max),m.allMonths)+N'/'+
                                            CONVERT(nvarchar(max),m.allYears))))
                                -- NOTICE: the conversion to nvarchar is needed
                                -- After extracting the dateparts in the CTE, they are INT not DATE
                                -- A explicit conversion from INT to DATE isn't allowed
                                -- This way we cast it to NVARCHAR and convert it afterwards to DATE

            ) t
            ON c.CustNum = t.CustNum
        WHERE t.CustNum IS NULL
            -- optional: Count only users which were present in the counting month.
            AND t.CustRegdate >= CONVERT(date,N'01/'+CONVERT(nvarchar(max),m.allMonths)+N'/'+CONVERT(nvarchar(max),m.allYears))
    ) as monthyCustomers
ORDER BY m.allYears, m.allMonths

DROP TABLE #orders
DROP TABLE #customers

How can I extract customers who have transacted since a specific date? (active customers definition)

First, you need aggregation in your first query. Second, you can use a HAVING clause to identify the active customers:

select customerid, 
       sum(count) as transaction_count,
       sum(value) as dollar_amount
from cust_orders 
group by customerid
having max(yearmonth) >= 201911;

Note: I interpret "since Nov 2011" as including that month, so I changed the comparison to >=.

MySQL Select all users with no transactions within a month

If you want members who had no transactions in a given month, I would expect something like this:

select m.*
from members m
where not exists (select 1
                  from mybuys mb
                  where mb.member_id = m.id and
                        mb.timed >= $month_start and
                        mb.timed < $month_start + interval 1 month
                 );

For performance, you want an index on mybuys(member_id, timed).

I'm not sure what amount has to do with this. The question is about whether or not any rows exist, not about whether any rows exist with a non-zero amount.

SQL Statement Help - Select Only Customers with no order for past 3 months

;WITH CTE_LastOrder (CustomerId, LastOrderDate) As
(
SELECT CustomerId, MAX(OrderDate) LastOrderDate
FROM Orders 
GROUP By CustomerId
)
SELECT * from Customers C
JOIN CTE_LastOrder LO ON C.CustomerId = LO.CustomerId
WHERE LO.LastOrderDate > Cast(Floor(Cast(dateAdd(Month,-3, GetDate()) as Float))as DateTime) 

Above is the basic sql for SQL Server. There might be slight difference in the syntax.

SQL Query to find rows that didn't occur this month

Use conditional aggregation:

SELECT count(*) as users
FROM
 (
    SELECT user_id
    FROM transactions
                       -- 1st of previous month
    WHERE date BETWEEN SUBDATE(SUBDATE(CURRENT_DATE, DAYOFMONTH(CURRENT_DATE)-1), interval 1 month) 
                       -- end of current month
                   AND LAST_DAY(CURRENT_DATE)
    AND transactions.status = 'COMPLETED'
    AND transactions.amount > 0
    GROUP BY user_id
           -- any row from previous month
    HAVING MAX(CASE WHEN date < SUBDATE(CURRENT_DATE, DAYOFMONTH(CURRENT_DATE)-1)
                    THEN date 
               END) IS NOT NULL
           -- no row in current month
       AND MAX(CASE WHEN date >= SUBDATE(CURRENT_DATE, DAYOFMONTH(CURRENT_DATE)-1)
                    THEN date 
               END) IS NULL           
 ) AS dt

SUBDATE(CURRENT_DATE, DAYOFMONTH(CURRENT_DATE)-1) = first day of current month

SUBDATE(first day of current month, interval 1 month) = first day of previous month

LAST_DAY(CURRENT_DATE) = end of current month

Iterating through users and check if they exist in the past 12 months

Use LAG() to check if there is an order in the previous 12 months:

SELECT c.order_date, c.customer
FROM (SELECT c.*,
             LAG(order_date) OVER (PARTITION BY customer) as prev_order_date
      FROM customers c
     ) c
WHERE c.order_date >= '2021-05-01' AND c.order_date < '2021-06-30' AND
      c.prev_order_date >= c.order_date - INTERVAL '12 month';

Here is a db<>fiddle.

Note that I fixed the date comparisons so you are not fiddling with seconds when defining a month timeframe.

MySQL - SQL query to get the customer ids and date of 20th transaction

You need to select the rows of transactions belonging to the customer_id and filter the result by the 20th row

SELECT * FROM (
    SELECT customer_id, purchase_date, ROW_NUMBER() OVER(
        PARTITION BY customer_id
        ORDER BY purchase_date DESC
    ) AS nth
    FROM transactions
) as t WHERE nth = 20

---

Related Topics