How to determine date paid given billing and payment data in BigQuery

TMo 0
Viewed 76

I need to know when a bill was paid to determine how early or late it was paid. Unfortunately, I just have billing creation data and payment records.

Example 1

--raw data
WITH bill AS (
  SELECT 'b1' AS id, DATE('2022-01-01') AS created, DATE('2022-01-15') AS due, 50 AS amount UNION ALL
  SELECT 'b2', DATE('2022-01-01'), DATE('2022-01-30'), 50 UNION ALL
  SELECT 'b3', DATE('2022-01-03'), DATE('2022-01-17'), 50 UNION ALL
  SELECT 'b4', DATE('2022-01-03'), DATE('2022-02-01'), 50 UNION ALL
  SELECT 'b5', DATE('2022-01-05'), DATE('2022-01-19'), 50 UNION ALL
  SELECT 'b6', DATE('2022-01-05'), DATE('2022-02-04'), 50
),
payment AS (
  SELECT 'p1' AS id, DATE('2022-01-10') AS made, 50 AS amount UNION ALL
  SELECT 'p2', DATE('2022-01-11'), 50
)
-- setup
SELECT * FROM bill

I want a query that returns all of the data from the bill table plus the date that the bill was paid, which is theoretically derived from the payment table.

In the example above, the solution could be to sort the bill rows by the due date and apply the payments accordingly:

--raw data
WITH bill AS (
  SELECT 'b1' AS id, DATE('2022-01-01') AS created, DATE('2022-01-15') AS due, 50 AS amount UNION ALL
  SELECT 'b2', DATE('2022-01-01'), DATE('2022-01-30'), 50 UNION ALL
  SELECT 'b3', DATE('2022-01-03'), DATE('2022-01-17'), 50 UNION ALL
  SELECT 'b4', DATE('2022-01-03'), DATE('2022-02-01'), 50 UNION ALL
  SELECT 'b5', DATE('2022-01-05'), DATE('2022-01-19'), 50 UNION ALL
  SELECT 'b6', DATE('2022-01-05'), DATE('2022-02-04'), 50
),
payment AS (
  SELECT 'p1' AS id, DATE('2022-01-10') AS made, 50 AS amount UNION ALL
  SELECT 'p2', DATE('2022-01-11'), 50
),
--start solution
p AS (
  SELECT
    payment.id,
    payment.made,
    payment.amount,
    SUM( payment.amount ) OVER (
      ORDER BY payment.made
    ) AS amount_cumulative
  FROM payment
),
b AS (
  SELECT
    bill.id,
    bill.created,
    bill.due,
    bill.amount,
    SUM( bill.amount ) OVER (
      ORDER BY bill.due
    ) AS amount_cumulative
  FROM bill
),
repayments AS (
  SELECT
    b.*,
    p.made,
    ROW_NUMBER() OVER (
      PARTITION BY b.id
      ORDER BY p.made ASC
    ) AS seq
  FROM b
  LEFT JOIN p
    ON b.amount_cumulative <= p.amount_cumulative
  WHERE p.made IS NOT NULL
)
SELECT
  b.*,
  repayments.made AS payment_date
FROM b
LEFT JOIN repayments
  ON b.id = repayments.id
WHERE (repayments.seq = 1 OR repayments.seq IS NULL)
ORDER BY b.id

Correct answer, but wrong solution

Example 2

However, that solution breaks down if we change some of the bill and payment dates (because payments must be applied to the bill with an oustanding balance and the earliest due date at the time of the payment). For example:

--raw data
WITH bill AS (
  SELECT 'b1' AS id, DATE('2022-01-01') AS created, DATE('2022-01-15') AS due, 50 AS amount UNION ALL
  SELECT 'b2', DATE('2022-01-01'), DATE('2022-01-30'), 50 UNION ALL
  SELECT 'b3', DATE('2022-01-03'), DATE('2022-01-17'), 50 UNION ALL
  SELECT 'b4', DATE('2022-01-03'), DATE('2022-02-01'), 50 UNION ALL
  SELECT 'b5', DATE('2022-01-05'), DATE('2022-01-19'), 50 UNION ALL
  SELECT 'b6', DATE('2022-01-05'), DATE('2022-02-04'), 50
),
payment AS (
  SELECT 'p1' AS id, DATE('2022-01-02') AS made, 100 AS amount UNION ALL
  SELECT 'p2', DATE('2022-01-11'), 50
),
--start solution
p AS (
  SELECT
    payment.id,
    payment.made,
    payment.amount,
    SUM( payment.amount ) OVER (
      ORDER BY payment.made
    ) AS amount_cumulative
  FROM payment
),
b AS (
  SELECT
    bill.id,
    bill.created,
    bill.due,
    bill.amount,
    SUM( bill.amount ) OVER (
      ORDER BY bill.due
    ) AS amount_cumulative
  FROM bill
),
repayments AS (
  SELECT
    b.*,
    p.made,
    ROW_NUMBER() OVER (
      PARTITION BY b.id
      ORDER BY p.made ASC
    ) AS seq
  FROM b
  LEFT JOIN p
    ON b.amount_cumulative <= p.amount_cumulative
  WHERE p.made IS NOT NULL
)
SELECT
  b.*,
  repayments.made AS payment_date
FROM b
LEFT JOIN repayments
  ON b.id = repayments.id
WHERE (repayments.seq = 1 OR repayments.seq IS NULL)
ORDER BY b.id

Problem with the solution

This example is even trickier if p1 is for an amount = 75 (in which case b2 should remain not completely paid, but b3 should be paid in full on 2022-01-11).

Trickiest Example

This is the most contrived, but it best illustrates the problem. Payment 1 goes toward Bill 1 because that is the only available bill at the time of the payment (the payment almost pays the first bill in full). At the time of Payment 2, the bill with the earliest unpaid balance is now Bill 2, so all of the payment goes toward Bill 2 (and again, the payment almost pays the second bill in full). However, at the end of this sequence, 99/100 is paid toward Bill 1 and 49/50 is paid toward Bill 2, but neither Bill 1 nor Bill 2 are paid in full, so the paid in full date should be NULL for both.

--raw data
WITH bill AS (
  SELECT 'b1' AS id, DATE('2022-01-01') AS created, DATE('2022-01-15') AS due, 100 AS amount UNION ALL
  SELECT 'b2', DATE('2022-01-02'), DATE('2022-01-14'), 50
),
payment AS (
  SELECT 'p1' AS id, DATE('2022-01-10') AS made, 99 AS amount UNION ALL
  SELECT 'p2', DATE('2022-01-11'), 49
),
-- setup
SELECT * FROM bill

Question

Can I get the payment_date with a query? If so, what does that look like?

google-bigquery sql
1 Answers

Please refer query below. Idea is to distribute payments over range.

--raw data
WITH bill AS (
  SELECT 'b1' AS id, DATE('2022-01-01') AS created, DATE('2022-01-15') AS due, 50 AS amount UNION ALL
  SELECT 'b2', DATE('2022-01-01'), DATE('2022-01-30'), 50 UNION ALL
  SELECT 'b3', DATE('2022-01-03'), DATE('2022-01-17'), 50 UNION ALL
  SELECT 'b4', DATE('2022-01-03'), DATE('2022-02-01'), 50 UNION ALL
  SELECT 'b5', DATE('2022-01-05'), DATE('2022-01-19'), 50 UNION ALL
  SELECT 'b6', DATE('2022-01-05'), DATE('2022-02-04'), 50
), payment AS (
  SELECT 'p1' AS id, DATE('2022-01-10') AS made, 50 AS amount UNION ALL
  SELECT 'p2', DATE('2022-01-11'), 50
), b as (
-- setup
  SELECT
    bill.id,
    bill.created,
    bill.due,
    bill.amount,
    SUM( bill.amount ) OVER (
      ORDER BY bill.created, bill.due
    ) AS amount_cumulative_created,
    SUM( bill.amount ) OVER (
      ORDER BY bill.due
    ) as amount_cumulative_due,
    row_number() over (order by bill.due) crn
  FROM bill
  order by created
), p as (
   SELECT
    p.id,
    p.made,
    p.amount,
    case when 
      row_number() over (order by 0) = 
      max(un) over (partition by p.id) then
        floor(p.amount / max(un) over (partition by p.id)) + 
        mod(p.amount, max(un) over (partition by p.id))
      else
        floor(p.amount / max(un) over (partition by p.id)) 
    end new_amount,
    row_number() over (order by 0) prn
  FROM 
    (select *,
    row_number() over (order by payment.made) prn 
    from payment) p join b
    on p.prn = b.crn
,unnest(generate_array(1,
(select coalesce(max(b.crn),0)+1 from b
where p.amount > b.amount_cumulative_due
and p.made between b.created and b.due),
1)) un
), p_distrib as (
  select *,
    SUM( p.new_amount ) OVER (
      ORDER BY p.prn
    ) AS payment_cumulative, from p
)
select b.*, 
(select min(pd.made) from p_distrib pd
 where
--- Adjust here for what date takes preference - created vs. due.
--- Replace amount_cumulative_created  with amount_cumulative_due as needed
 b.amount_cumulative_created <= pd.payment_cumulative
 and pd.made between b.created and b.due)
from b;

enter image description here

user92oW0
Related