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
Go to Top of Page

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!

Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -