Categorising transactions in past 90 days-CodePudding

I have a table where the columns are:

Transaction_id(T_id): Distinct id generated for each transactions
Date(Dt): Date of Transaction
account-id(Ac_id): The id from which the transaction is done
Org_id(O_id): It is the id given to the organizations. One organization can have multiple accounts thereby different account id can have the same org_id

Sample table:

T_id	Dt	Ac_id	O_id
101	23/4/22	1	A
102	06/7/22	3	C
103	01/8/22	2	A
104	13/3/22	6	B

*The question is to mark the o_id where transactions are done in the past 90 days as 1 and others as 0

Output

T_id	Dt.	Ac_id.	O_id	Mark
101	23/4/22	1	A	0
102	06/7/22	3	C	1
103	01/8/22	2	A	1
104	13/3/22	6	B	0

The query I am using is:

Select *,
Case when datediff('day', Dt, current_date()) between 0 and 90 then '1'
Else '0'
End as Mark
From Table1

Desired Output:

T_id	Dt.	Ac_id.	O_id	Mark
101	23/4/22	1	A	1
102	06/7/22	3	C	1
103	01/8/22	2	A	1
104	13/3/22	6	B	0

for o_id 'A' from the output the mark I want is 1 in all cases as one transaction is done past 90 days, irrespective of other transactions done prior to 90days.
I have to join this out to another table so need all o_id where ever any one transaction is done in the past 90 days as '1'.

Please help me with it quickly.

CodePudding user response：

The easisest approach is to compare date difference of current date against windowed MAX partitioned by o_id:

SELECT *,
  CASE 
     WHEN DATEDIFF('day', (MAX(Dt) OVER(PARTITION BY o_id)), CURRENT_DATE()) <= 90 
     THEN 1
     ELSE 0
  END AS Mark
FROM Tab;

Sample data:

ALTER SESSION SET DATE_INPUT_FORMAT = 'DD/MM/YYYY';
CREATE OR REPLACE TABLE tab(t_id INT,
                            Dt Date,
                            Ac_id INT,
                            O_id TEXT)
AS
SELECT 101,     '23/04/2022'    ,1  ,'A' UNION
SELECT 102,     '06/07/2022'    ,3  ,'C' UNION
SELECT 103,     '01/08/2022'    ,2  ,'A' UNION
SELECT 104,     '13/03/2022'    ,6  ,'B';

Output:

Snowflake supports natively BOOLEAN data types so entire query could be just:

SELECT *,
 DATEDIFF('day', (MAX(Dt) OVER(PARTITION BY o_id)), CURRENT_DATE()) <= 90 AS Mark
FROM tab

CodePudding user response：

Create a subquery where you identify all the distinct o_id where there is a recent transaction, and use that to update the main result.

The subquery would be:

select o_id, dt from table1 
group by o_id
having datediff('day', max(Dt), current_date()) between 0 and 90;

Then your main query becomes:

 Select *,'1' as Mark
    From Tab
    where o_id in 
       (select x.o_id from (select o_id, max(Dt) 
        from tab 
        group by o_id
        having datediff('day', max(Dt), current_date()) between 0 and 90) x)
    union all
    select *,'0' as Mark
    from Tab
    where o_id not in 
        (select x.o_id from (select o_id, max(Dt) 
        from tab 
        group by o_id
        having datediff('day', max(Dt), current_date()) between 0 and 90) x);