SQL Code How to do iterations in historical table

Question

I need help on SQL

I have a historical table named A. It has month ID, srvc key, etc.

I need to check if a custkey is a new customer in that table A. The logic is - to see if that cust key is new for the current month ID and does not exist prior months (less than the current month ID).

To illustrate,

My current month ID = Feb2022 The cust key MUST exist in Feb2022 BUT not in Jan 2022, Dec2021,.., and so on..

Also, is it possible to tag if a cust key exist in Feb 2022 and Jan 2022 BUT not in Dec 2021, and so on..

select A.\*,B.level_1, B.level_2, B.level_3, B.LE,
case when cust_key in ('2100707688',
'1xxx4',
'1xxxx',
'28xxxx1',
'2xxxxxx',
) then 'New' else 'Old' end as Tag,
A.NET_AMT/(nullif(A.prod_cnt,0)\*B.LE) as ARPU

Hi @NickW,

thanks for responding, what I need is it from sample historical table below, I need to tag CNumber that are new for the current month (202202). They are new because CNumber2 didnt appear for 202201,202112,20211. I dont care if it appeared 202110 and less. I care only about CNumber which didnt appear last 3 months.

Cnumber MonthID 1 202202 1 202201 1 202112 1 202111 2 202202 2 202105 2 202104 2 202103 2 202102 2 202101 3 202202 3 202201 3 202112 3 202111 3 202110 3 202109

Based on this sample, Only CNumber 2 satisfies this rule since it appeared on 202202 but not in 202201 202112 202111.

Next, I would want to tag also CNumber who is new for Jan2022.

In this case, current monthID = 202201. Now, that CNumber should not appear in 202112,20211,202110 to be able to say it is New.

Next, want to tag also CNumber who is new for Dec 2022. Now, that CNumber should not appear in 20211,202110,202109 to be able to tell that they are new.

And so on..

My goal is to tag customers on when did they first appear in the historical table via Month ID. I am assuming that that is their booking date. So in a table, my goal is to see a column that is named as booking date.

Answer 1

We can use a cte to get the month of the first entry for the account. With that we can compare and calculate as needed.

create table sales(
cnumber int,
salesDate date);

insert into sales values
(1,'2021-11-15'),
(1,'2021-12-15'),
(1,'2022-01-15'),
(1,'2022-02-15'),
(2,'2022-02-15');

with cre as (
  select
    cnumber cnum,
    DATE_FORMAT(min(salesDate),
       '%Y-%m-01') monCre
  from sales
  group by 
    cnumber),
salesMonth as(
  select
    DATE_FORMAT(salesDate, 
      '%Y-%m-01') as mon,
    cnumber cust
  from sales
  group by 
    cnumber,
    mon)
select
  cust customer,
  mon "month",
  case when mon = monCre
    then 'new' else 'existing' end
    as "status",
  TIMESTAMPDIFF(MONTH,monCre ,mon)
    as "account Age"
from salesMonth
join cre on cust = cnum
order by cust, mon;

customer | month      | status   | account Age
-------: | :--------- | :------- | ----------:
       1 | 2021-11-01 | new      |           0
       1 | 2021-12-01 | existing |           1
       1 | 2022-01-01 | existing |           2
       1 | 2022-02-01 | existing |           3
       2 | 2022-02-01 | new      |           0

db<>fiddle here