ibk_sql

/*
1. Count of unique accounts that make at least one payment on any given day
*/
select payment_date, count(distinct account_id) as account_count
from payment
group by payment_date
order by payment_date desc



/*
2: Count of unique accounts that make at least one payment in a month
*/

select concat(a.mnth,'/' ,a.yr) as payment_month, count(distinct a.account_id) as account_count
from (
  select account_id, MONTH(payment_date) as mnth, YEAR(payment_date) as yr
  from payment
) a
group by a.yr, a.mnth
order by a.yr desc, a.mnth DESC


/*
3: How many new accounts made a payment in the last month?
*/

with last_month_payments(acct_id, rnk) as (
  select account_id,
  row_number() over (partition by account_id order by payment_date DESC) as rnk -- row_number(): to know the first time a payment was made
  from payment
  where payment_date BETWEEN DATEADD(DAY, 1, EOMONTH(GETDATE(), -2)) AND EOMONTH(GETDATE(), -1)
),

number_of_payments(acct_id, max_rnk) as (
  select a.acct_id, max(a.rnk) as max_rnk
  from last_month_payments a
  group by a.acct_id
)
select count(*) as new_accounts
from number_of_payments n
where n.max_rnk = 1



/*
4: What is the average count of payments per account?
*/
select a.account_id, avg(a.cnt) as avg_count
from (
  select payment_date, account_id, count(*) as cnt
  from payment
  group by payment_date, account_id
) a
group by a.account_id



/*
5: What is the failed payment rate this month?
calculated as follows:
failed rate = (count of failed payments this month)/(count of all payments this month)
*/

WITH all_payments_this_month(status, status_count) as (
  select Status, count(*) as cnt
  from payment
  where payment_date >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))
  group by status
)
select round(cast((select status_count from all_payments_this_month where status = 'failed') as float)/cast(sum(status_count) as float), 3) as failed_payment_rate
from all_payments_this_month



/*
6: What is the number of GBP payments that have no decimal place?
Assumption: amount is float.
*/

select count(*) as cnt
from payment
where currency = 'GBP'
AND cast(amount as decimal(20, 2)) % 1 = 0

/*
If amount is varchar
*/
select count(*) as cnt
from payment
where currency = 'GBP'
AND amount NOT LIKE '%.%'



/*
7. How will you calculate the daily amount GBP Equiv. per day for the last three months?
*/
select
  p.payment_date,
  p.account_id,
  p.currency as original_currency,
  p.amount as original_amount,
  p.amount * h.rate as amount_in_gbp
from payment p JOIN historic_rates h on p.payment_date = h.date AND p.currency = h.from_ccy
where p.currency <> 'GBP'AND payment_date >= DATEADD(month, -3, GETDATE())
ORDER BY p.payment_date



/* 8: What is the average payment amount GBP Equiv. per account year to date? */
select
    p.payment_date,
    p.account_id,
    AVG(p.amount * h.rate) OVER (partition by YEAR(p.payment_date), p.account_id order by p.payment_date) as amount_ytd

from payment p join historic_rates h on p.payment_date = h.date and p.currency = h.from_ccy
where p.currency <> 'GBP'
ORDER BY p.payment_date, p.account_id