google big query

Question

Suppose there is a database with two tables namely Loans and Billpayment.

For the Loans table I have the following columns:

loanId,
clientId(primary key),
DisbursementDate,
applicationDate,
approvalDate,
loanNumber,
loanAmount,
interestRate

Billpayment table columns include:

billId(primary),
customerId(foreign),
billAmount,
payment_status[success/fail],
billDate

How can I get the clients that had a bill payment in March 2018, and show the number of those clients that made at least one bill payment in the remaining months of 2018, and On top of this, show whether or not the bill payment clients have a loan within 2018 or not?

Answer 1

OK, let's work this through. I haven't tested any of this, nor is it properly optimised, but hopefully it will help.

To list the payments in March 2018, assuming we don't care whether successful or not, do a where:

select * from billpayment
where month(billDate) = 3 and year(billDate) = 2018

To include the relevant customer information in the above, do a join:

select * from billpayment
join loans on customerId = clientId
where month(billDate) = 3 and year(billDate) = 2018

To list only customers who also made a payment in another month of 2018, do another join to a derived table:

select * from billpayment b
join loans l on b.customerId = l.clientId
join (select distinct b1.clientId as clientid1 from billpayment b1
      where year(b1.billdate) = 2018 and month(b1.billdate) <> 3) c
        on b.customerId = c.clientId1 
where month(b.billDate) = 3 and year(b.billDate) = 2018

To add a check whether they started their loan in 2018 (assuming by application date), add an and to your where clause:

select * from billpayment b
join loans l on b.customerId = l.clientId
join (select distinct b1.clientId as clientid1 from billpayment b1
      where year(b1.billdate) = 2018 and month(b1.billdate) <> 3) c
        on b.customerId = c.clientId1 
where month(b.billDate) = 3 and year(b.billDate) = 2018
and year(l.applicationDate) = 2018