SQL – Join with window

I have this dataset of product sales:

PRODUCT SALES   
YearMonth   Client_ID   
202103             1    
202008             1    
201807             1    
202101             2    
202011             2    
201802             2    
201801             2    

And I have this dataset of Financial Sales

FINANCIAL SALES 
YearMonth   Client_ID
202104      1
202009      1
201607      1
202104      2
202012      2
201512      2

I want to create a column in the dataset PRODUCT SALES named “If_financial_sales” where it takes the value 1 when the client in product sales bougth a financial product in the last 24 months, and 0 otherwise. Those 24 months are counting from the YearMonth of PRODUCT SALES (not from today). For example, If the client 2 on 201802 from product sales date bought a financial sale in the last 24 months (from 201801 to 201601 = 24 months) then If_financial_sales = 1 for this client in that month.

Output expected:

PRODUCT SALES   
YearMonth   Client_ID   If_Financial_Sales
202103             1    1
202008             1    0
201807             1    1
202101             2    1
202011             2    0
201802             2    0
201801             2    1

I have been trying this with ROW_NUMBER(), but it seems to work better with some joins I have seen that uses equality operatos on the keys when joining (>, <, =) but I don’t know what their name is neither how to use them.

Answer

Try this:

-- Auxiliary data for testing purposes
with product_sales (YearMonth,   Client_ID) as (   
  select  202103,     1 from dual union all
  select  202008,     1 from dual union all    
  select  201807,     1 from dual union all    
  select  202101,     2 from dual union all    
  select  202011,     2 from dual union all    
  select  201802,     2 from dual union all    
  select  201801,     2 from dual),
financial_sales (YearMonth,   Client_ID) as (
  select  202104,     1 from dual union all
  select  202009,     1 from dual union all
  select  201607,     1 from dual union all
  select  202104,     2 from dual union all
  select  202012,     2 from dual union all
  select  201512,     2 from dual)

-- Actual query
SELECT
    ps.*,
    CASE
        WHEN EXISTS (
            SELECT
                1
            FROM
                financial_sales fs
            WHERE
                to_date(fs.yearmonth, 'YYYYMM') < to_date(ps.yearmonth, 'YYYYMM')
                AND to_date(fs.yearmonth, 'YYYYMM') >= to_date(ps.yearmonth, 'YYYYMM') - INTERVAL '25' MONTH
                AND fs.client_id = ps.client_id
        ) THEN
            1
        ELSE
            0
    END if_financial_sales
FROM
    product_sales ps;

Output:

YearMonth Client_ID If_financial_sales
----------------------
202103  1   1
202008  1   0
201807  1   1
202101  2   1
202011  2   0
201802  2   0
201801  2   1