SELECT Data with logic created date not greater than

I have three tables with detail structure and data like this :

1. Table TBL_REFERRAL

   ID     CREATED_DATE           USER_NAME    REFERRAL_CODE
   ---------------------------------------------------------
   1      28-08-2020 12:15:40    DINA         EM0001
   2      28-08-2020 12:19:42    DINA         EM0002
   3      28-08-2020 12:19:22    LISA         EM0003
   4      28-08-2020 20:00:09    LISA         EM0004
   5      29-08-2020 12:00:05    RYAN         EM0004
   

2. Table TBL_USER

   ID     USER_NAME       UNIQUE_CODE
   ----------------------------------
   1      DINA            UNI0001
   3      LISA            UNI0002
   5      RYAN            UNI0003
   

3. Table TBL_CHECKOUT

   TRANS_ID      UNIQUE_CODE     CREATED_DATE           ITEM         TOTAL
   ------------------------------------------------------------------------
   1112          UNI0001         28-08-2020 12:20:40    Milk          5000
   1113          UNI0002         28-08-2020 12:22:22    Shampoo      12000
   1114          UNI0002         28-08-2020 20:11:09    Biscuit       5000
   1115          UNI0003         29-08-2020 12:02:05    Soap          2000
   

Because of some conditions, in the process I can’t connect between checkout table and referral. But I want to have a view table that connecting the checkout with referral but I am map that with logic based on created date time between referral and check out. Is it possible to me have the view table like this ? :

  REFERRAL_CODE  TRANS_ID  USER_NAME  UNIQUE_CODE  REFERRAL_DATE        CHECKOUT_DATE
  ------------------------------------------------------------------------------------------
  EM0002         1112      DINA       UNI0001      28-08-2020 12:19:42  28-08-2020 12:20:40
  EM0003         1113      LISA       UNI0002      28-08-2020 12:19:22  28-08-2020 12:22:22
  EM0004         1114      LISA       UNI0002      28-08-2020 20:00:09  28-08-2020 20:11:09 
  EM0004         1115      RYAN       UNI0003      29-08-2020 12:00:05  29-08-2020 12:02:05

Updated -> I have been try the query like this :

SELECT 
    a.REFERRAL_CODE, a.USER_NAME, b.UNIQUE_CODE, c.TRANS_ID, 
    a.CREATED_DATE AS "REFERRAL_DATE", c.CREATED_DATE AS "CHECKOUT_DATE"
FROM
    TBL_CHECKOUT c
LEFT JOIN TBL_USER b
 ON c.UNIQUE_CODE = b.UNIQUE_CODE
LEFT JOIN TBL_REFERRAL a
 ON a.USER_NAME = b.USER_NAME
WHERE 
    c.CREATED_DATE > a.CREATED_DATE;

The result come is :

REFERRAL_CODE  TRANS_ID  USER_NAME  UNIQUE_CODE  REFERRAL_DATE        CHECKOUT_DATE
------------------------------------------------------------------------------------------
  EM0001         1112      DINA       UNI0001      28-08-2020 12:15:40  28-08-2020 12:20:40
  EM0002         1112      DINA       UNI0001      28-08-2020 12:19:42  28-08-2020 12:20:40
  EM0003         1113      LISA       UNI0002      28-08-2020 12:19:22  28-08-2020 12:22:22
  EM0004         1114      LISA       UNI0002      28-08-2020 20:00:09  28-08-2020 20:11:09 
  EM0004         1115      RYAN       UNI0003      29-08-2020 12:00:05  29-08-2020 12:02:05

So, based on my result and expectation there is still missing 1 filtering. In this condition c.CREATED_DATE > a.CREATED_DATE, if a.CREATED_DATE that smaller then c.CREATED_DATE is more than 1. the data show 2 row, it should be just one, get latest a.CREATED_DATE. If i using ROW_NUMBER in logic it will be disturb row EM0003 & 1113 and EM0004 & 1114.

based on my current result, i want to grouping the transaction_id get based on latest REFERRAL_DATE, is it possible ?

Thank you so much for help I really appreciate it

Answer

You can use LEFT JOIN among those three tables, and ROW_NUMBER() analytic function is need to filter out the records among latest CHECKOUT_DATE values :

WITH t AS
(
SELECT r.REFERRAL_CODE, r.USER_NAME, u.UNIQUE_CODE, c.TRANS_ID, 
       r.CREATED_DATE AS "REFERRAL_DATE", c.CREATED_DATE AS "CHECKOUT_DATE",
       ROW_NUMBER() OVER (PARTITION BY r.USER_NAME ORDER BY c.CREATED_DATE DESC) AS rn
  FROM TBL_CHECKOUT c
  LEFT JOIN TBL_USER u
    ON u.UNIQUE_CODE = c.UNIQUE_CODE 
  LEFT JOIN TBL_REFERRAL r
    ON r.USER_NAME = u.USER_NAME
 WHERE r.CREATED_DATE < c.CREATED_DATE
 )
 SELECT REFERRAL_CODE, USER_NAME, UNIQUE_CODE,TRANS_ID,REFERRAL_DATE,CHECKOUT_DATE
   FROM t
  WHERE rn = 1