Sometimes, payment transactions are repeated by
accident; it could be due to user error, API failure or a retry error that
causes a credit card to be charged twice.
Using the transactions table, identify any
payments made at the same merchant with the same credit card for the same
amount within 10 minutes of each other. Count such repeated payments.
Assumptions:
· The first transaction of
such payments should not be counted as a repeated payment. This means, if there
are two transactions performed by a merchant with the same credit card and for
the same amount within 10 minutes, there will only be 1 repeated payment.
table: transactions
Solution:
with cte as
(
select
merchant_id,
datediff(minute,((lag(transaction_timestamp,1)
over (partition by merchant_id,credit_card_id,amount
order by transaction_timestamp) )),transaction_timestamp) as diff_min
from transactions
)
select count(merchant_id) as payment_count
from cte where diff_min<=10
Output:
SQL Script:
CREATE TABLE [dbo].[transactions](
[transaction_id]
[int] NULL,
[merchant_id]
[int] NULL,
[credit_card_id]
[int] NULL,
[amount]
[int] NULL,
[transaction_timestamp]
[datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[transactions] ([transaction_id], [merchant_id],
[credit_card_id],
[amount],
[transaction_timestamp]) VALUES (1, 101, 1, 100, CAST(N'2022-09-25T12:00:00.000' AS DateTime))
GO
INSERT [dbo].[transactions] ([transaction_id], [merchant_id],
[credit_card_id],
[amount],
[transaction_timestamp]) VALUES (2, 101, 1, 100, CAST(N'2022-09-25T12:08:00.000' AS DateTime))
GO
INSERT [dbo].[transactions] ([transaction_id], [merchant_id],
[credit_card_id],
[amount],
[transaction_timestamp]) VALUES (3, 101, 1, 100, CAST(N'2022-09-25T12:28:00.000' AS DateTime))
GO
INSERT [dbo].[transactions] ([transaction_id], [merchant_id],
[credit_card_id],
[amount],
[transaction_timestamp]) VALUES (4, 102, 2, 300, CAST(N'2022-09-25T12:00:00.000' AS DateTime))
GO
INSERT [dbo].[transactions] ([transaction_id], [merchant_id],
[credit_card_id],
[amount],
[transaction_timestamp]) VALUES (6, 102, 2, 400, CAST(N'2022-09-25T14:00:00.000' AS DateTime))
GO