-
Notifications
You must be signed in to change notification settings - Fork 0
/
Monthly Merchant Balance [Visa SQL Interview Question]
45 lines (39 loc) · 1.71 KB
/
Monthly Merchant Balance [Visa SQL Interview Question]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
with cte as (select transaction_date,sum(case when type='deposit' then amount else -amount end) as t
from transactions
group by transaction_date
order by transaction_date
)
,
cte2 as(select date_trunc('day',transaction_date) as transaction_date,sum(t) as p
from cte
group by date_trunc('day',transaction_date)
order by transaction_date)
,
cte3 as (select transaction_date,p ,extract(day from transaction_date) as dt ,extract(month from transaction_date) as mt
from cte2)
,
cte4 as (select transaction_date,p,row_number() over(PARTITION BY mt) as dt ,dense_rank() over (order by mt ) as mt
from cte3)
select transaction_date,sum(p) over(PARTITION BY mt order by dt) as balance
from cte4
---------
Say you have access to all the transactions for a given merchant account. Write a query to print the cumulative balance of the merchant account at the end of each day, with the total balance reset back to zero at the end of the month. Output the transaction date and cumulative balance.
transactions Table:
Column Name Type
transaction_id integer
type string ('deposit', 'withdrawal')
amount decimal
transaction_date timestamp
transactions Example Input:
transaction_id type amount transaction_date
19153 deposit 65.90 07/10/2022 10:00:00
53151 deposit 178.55 07/08/2022 10:00:00
29776 withdrawal 25.90 07/08/2022 10:00:00
16461 withdrawal 45.99 07/08/2022 10:00:00
77134 deposit 32.60 07/10/2022 10:00:00
Example Output:
transaction_date balance
07/08/2022 12:00:00 106.66
07/10/2022 12:00:00 205.16
To get cumulative balance of 106.66 on 07/08/2022 12:00:00, we take the deposit of 178.55 and minus against two withdrawals 25.90 and 45.99.
The dataset you are querying against may have different input & output - this is just an example!