0

I want to create an Effectivity Satellite off the link table below ( you can open and create in http://sqlfiddle.com/ via the code below).

CREATE TABLE l_opportunity_account (
  opportunity_account_hash_id VARCHAR(100),
  load_timestamp TIMESTAMP,
  record_source VARCHAR(100),
  opportunity_hash_id VARCHAR(100),
  account_hash_id VARCHAR(100),
  insert_dt TIMESTAMP,
  opportunity_account_hashdiff VARCHAR(100)
);

INSERT INTO l_opportunity_account VALUES
('a1', '2023-03-18 02:28:34', 'account', 'a', '1', '2023-03-18', '06D6B4A1E291EFF41128B1871C8749002109354E4A8895FDD647572B631B22E1'),
('b2', '2023-03-18 00:00:00', 'account', 'b', '2', '2023-03-18', 'B4F87912A5D1DB9BC0AAD06428F66F5B92BC3CFC59A36D886CD262D899E5F8D7'),
('c3', '2023-03-18 02:28:34.000'    ,'account', 'c','3',    '2023-03-18','3DC600B94F4FEC867FB6BF7715B84CA08E46293AFECAB336ED186B6A4351136F'),
('c5', '2023-03-18 02:28:34.000'    ,'account', 'c','5',    '2023-03-19','3cC600B94F4FEC867FB6BF7715B84CA08E46293AFECAB336ED186B6A4351136F'),
('c3', '2023-03-18 02:28:34.000'    ,'account', 'c','3',    '2023-03-20','37C600B94F4FEC867FB6BF7715B84CA08E46293AFECAB336ED186B6A4351136F'),
('b1', '2023-03-18 02:28:34.000'    ,'account', 'c','1',    '2023-03-24','37C600B94F4FEC867FB6BF7715B84CA08E46293AFECAB336ED186B6A4351136F')

The effectivity Satellite has to meet the criteria below:

  1. Relationships can be 1-many, many-many, 1-1 (so child account can have one or many parent orgs and so on)
  2. Captures relationships that may have changed (child account went to one parent account after another and then back)
  3. Contain effective_from & effective_to date for each record

I tried to query a query as below, and the window function does create effective_from & effective_to ---- but does not handle many-many or if a relationship comes back. Do I have columns with the right detail to even do this-- not not what do I need, or can I create an effectivity_sat off this link table above?

WITH es_opportunity_account as (
    SELECT
        t1.*,
        t1.load_timestamp AS effective_from,
        LEAD(t1.load_timestamp) OVER (
            PARTITION BY t1.opportunity_hash_id
            ORDER BY
                t1.load_timestamp
        ) AS effective_to
    FROM
        l_opportunity_account t1
)
SELECT

*
    
FROM
    es_opportunity_account;
0004
  • 1,156
  • 1
  • 14
  • 49

1 Answers1

0

If I understand correctly, l_opportunity_account is your link table. If so then the link table contains the unique relationship only. So you will not have the new record if the old relationship appears again, be it one to one or one to many. to overcome this scenario you need to define a Driving key which is in your case it seems Account_Hash_ID can act as Driving key and then you generate the satellite query from source data instead of link table. I have found a very good example posted by Patrick Cuba. you may go and check this blog. I can help you with the query if you are planning to create it based on Driving key. https://www.linkedin.com/pulse/data-lt-mysteries-effectivity-satellite-driving-key-patrick-cuba/

In my opinion below query should solve your problem.

SELECT  *,
CASE WHEN LAG(LOAD_TIMESTAMP) OVER(PARTITION BY ACCOUNT_HASH_ID 
order by LOAD_TIMESTAMP DESC) IS NULL THEN  '1800-01-01' ELSE 
LOAD_TIMESTAMP END  AS effective_from,
IFNULL(DATEADD(S,-1, LEAD(LOAD_TIMESTAMP) OVER(PARTITION BY 
ACCOUNT_HASH_ID order by LOAD_TIMESTAMP DESC )), '9999-12-31') AS 
effective_to 
FROM  
    l_opportunity_account SRC
QUALIFY row_number() OVER (PARTITION BY OPPORTUNITY_ACCOUNT_HASH_ID 
ORDER BY LOAD_TIMESTAMP ) = 1
Arbind Chandra
  • 99
  • 5