Compare dates in multiple rows within the same table and account

user_17688034 0
Viewed 50

I'm trying to compare two dates within one table to try and find duplicate accounts. I would like it to bring me back only accountid-s that have transacted twice or more times within a 7-day window from either date authorized.

My code so far:

    select 
      tr.dateauth,
      a.email,
      a.accountid,
      tr.transactionid
    
    
    from
      userdata as ud
      join account as a on a.accountid = ud.accountid
      join crcc as cc on a.accountid = cc.accountid
      join trid as tr on tr.cardid = cc.cardid
    
    where 
      tr.dateauth >= CURRENT_DATE - INTERVAL '7 day' 
      and ud.created >= CURRENT_DATE - INTERVAL '6 months' 
    
      
    group by
      a.accountid,
      tr.dateauth,
      a.email,
      tr.transactionid
    
    order by
      tr.date desc

Example Result, with the correct code I should only get back the rows with accountid 1256

|     dateauthorized     |    email         |  accountid | transactionid |
|------------------------|------------------|------------|---------------|
|2022-07-21T13:52:03.000Z| first@aol.com    |   1256     |    1568499    |
|2022-07-21T04:58:10.000Z| second@gmail.com |    34      |    3768789    |
|2022-07-20T17:07:49.000Z| first@aol.com    |   1256     |    2687941    |
|2022-07-18T23:37:10.000Z| third@aol.com    |    78      |    4198796    |

I have attempted this next to my where clause but found no luck

where EXISTS(
  SELECT 1
    FROM accounts as t2
    join crcc as t3 on t2.accountid = t3.accountid
    join trid as t4 on t4.cardid = t3.cardid
      and a.accountid = t2.accountid
      AND tr.dateauthorized <> t4.dateauthorized
      AND tr.dateauthorized BETWEEN
               t4.dateauthorized - interval '7 day'
            AND
               t4.dateauthorized + interval '7 day'
      HAVING count(t2.accountid)>1
)
compare report postgresql sql
1 Answers

I see what you are trying to do, but there is a better way to do this than joining on a grouped query from the same tables. The better way is with analytic functions or window functions. Rather than grouping, you can partition and do groups on slices of the dataset.

Here is an example I believe will do what you seek:

with cte as (
  select 
    tr.dateauth,
    a.email,
    a.accountid,
    tr.transactionid,
    tr.date,
    count (*) over (partition by a.account_id) as trans_count
  from
    userdata as ud
    join account as a on a.accountid = ud.accountid
    join crcc as cc on a.accountid = cc.accountid
    join trid as tr on tr.cardid = cc.cardid
  where 
    tr.dateauth >= CURRENT_DATE - INTERVAL '7 day' 
    and ud.created >= CURRENT_DATE - INTERVAL '6 months' 
)
select
  dateauth, email, accountid, transactionid
from cte
where trans_count > 1
order by
  date desc

The code is not only easier to follow (IMO), but it will be more efficient because the grouping occurs on smaller chunks of data -- O(n log n) (love any chance to talk about Big O notation...).

user_10018840
Related