Need help with a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with a query

Author

Topic

bluenotebooks
Starting Member

3 Posts

Posted - 2015-03-21 : 21:49:51

I have a large table of credit card transaction information which includes the card number, merchant name, transaction amount, date and time, along with lots of other info about each transaction. There is also a field that is Y or N to indicate whether or not the transaction is fraudulent. What I'd like to do is look at the percentage of cards used at each merchant that then have fraudulent transactions. I'm doing this to look for compromised merchants.

It's easy to look at the number of cards that have had fraud after being used at a particular merchant using:

Select
Merchantname,
Count(accountnum)
From database
Where
Accountnum in
(Select
Distinct Accountnum
From database
Where fraud = 'y'
)
Group by merchantname
Order by count(accountnum) desc

While this is helpful the count doesn't necessarily tell me much. What I'd like to do is have each of those counts of cards with fraud divided by the total number of cards that were used at each merchant to give me the rate of fraud for cards used at each merchant. Hopefully that makes sense. Is this something I can do using sql? If that isn't clear I'll be happy to try and explain better.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-22 : 08:33:42

Try this (remove top orange section, as this is just sample data I provided for test, and also replace buttom orange section with the correct tablename):

with yourtable(id,merchantname,accountnum,fraud)
  as (          select  1,'A',1,NULL
      union all select  2,'A',2,NULL
      union all select  3,'A',3,'y'
      union all select  4,'A',4,NULL
      union all select  5,'A',5,'y'
      union all select  6,'A',3,'y'
      union all select  7,'A',6,NULL
      union all select  8,'B',1,NULL
      union all select  9,'B',2,NULL
      union all select 10,'B',3,'y'
      union all select 11,'C',2,NULL
      union all select 12,'C',4,NULL
      union all select 13,'C',5,'y'
     )
select merchantname
      ,sum(accounts) as accounts
      ,sum(account_frauds) as account_frauds
      ,sum(transactions) as transactions
      ,sum(transaction_frauds) as transaction_frauds
  from (select merchantname
              ,1 as accounts
              ,sign(sum(case when fraud in ('y','Y') then 1 else 0 end)) as account_frauds
              ,count(*) as transactions
              ,sum(case when fraud in ('y','Y') then 1 else 0 end) as transaction_frauds
          from yourtable
         group by merchantname
                 ,accountnum
       ) as a
 group by merchantname
 order by merchantname

bluenotebooks
Starting Member

3 Posts

Posted - 2015-03-22 : 18:12:48

Thank you very much for the help. There is one part I don't understand though.

,1 as accounts

Could you explain this part? Thanks!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-22 : 19:10:43

As we group by merchantname and accountnum, we can get the count of unique accounts, by just specifying "1 as accounts".
In other words - if an account has more than one transaction with one merchant (as my sample data reflects in id 3 and 6), we only count it once.
At the time I wrote the query, I didn't know (and I still don't), if you wanted to calculate the procentage by the number of transactions or accounts. Now you have both options.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources