So I have created a table that has the following columns from a transaction table with all customer purchase records:
1. Month-Year, 2.Customer ID, 3. Number of Transactions in that month.
I'm trying to create a table that has the output of
1. Month-Year, 2. Number of active customers defined by having at least 1 purchase in the previous year.
The code that I have currently is this but the case when obviously only capturing one date and the where clause isn't dynamic. Would really appreciate your help.
select month_start_date, cust_ID,
(case when month_start_Date between date and add_months(date, -12) then count(cust_ID) else 0 end) as active
group by 1,2
You might use Teradata's proprietary
EXPAND ON synax for creating time series:
SELECT month_start_date, COUNT(*) FROM ( -- create one row for every month within the next year -- after a customer's transaction SELECT DISTINCT BEGIN(pd) AS month_start_date, cust_ID FROM myserver.mytable EXPAND ON PERIOD(month_start_date, ADD_MONTHS(month_start_date,12)) AS pd BY ANCHOR MONTH_BEGIN -- every 1st of month FOR PERIOD (DATE - 500, DATE) -- use this to restrict to a specific date range ) AS dt GROUP BY month_start_date ORDER BY month_start_date