Financial analyses using Orb data

WITH unpaid_invoices AS (
  SELECT id
       , due_date
       , customer_id
       , total
       , CAST(amount_due AS DECIMAL) AS amount_due

  FROM invoice_metadata

  -- Include issued and synced invoices (unpaid)
  WHERE status IN ('issued', 'synced')
    AND deleted_at IS NULL
    AND voided_at IS NULL
)

, invoices_with_buckets AS (
  SELECT id
       , customer_id
       , total
       , amount_due
       , CASE
           WHEN due_date >= NOW() THEN 'Current'
           WHEN due_date >= NOW() - INTERVAL '30 days' THEN '1-30 days past due'
           WHEN due_date >= NOW() - INTERVAL '60 days' THEN '31-60 days past due'
           WHEN due_date >= NOW() - INTERVAL '90 days' THEN '61-90 days past due'
           ELSE '91+ days past due'
         END AS aging_bucket

  FROM unpaid_invoices
)

SELECT aging_bucket
     , customer.name
     , COUNT(*) AS invoice_count
     , SUM(total) AS total
     , SUM(amount_due) AS amount_due

FROM invoices_with_buckets
LEFT JOIN customer ON customer.id = invoices_with_buckets.customer_id

GROUP BY 1, 2
ORDER BY
  CASE aging_bucket
    WHEN 'Current' THEN 1
    WHEN '1-30 days past due' THEN 2
    WHEN '31-60 days past due' THEN 3
    WHEN '61-90 days past due' THEN 4
    WHEN '91+ days past due' THEN 5
  END
, 2 ASC

SELECT id
     , invoice_number
     , customer_id
     , issued_at
     , due_date
     , paid_at

FROM invoice_metadata

WHERE status = 'paid'
  AND deleted_at IS NULL
  AND voided_at IS NULL

SELECT invoice.id
     , invoice.invoice_number
     , credit_note.credit_note_number
     , cnli.id
     , SUM(CAST(cnli.amount AS DECIMAL)) AS amount
     , STRING_AGG(DISTINCT(credit_note.reason), ', ') AS reasons

FROM credit_note
JOIN credit_note_line_item AS cnli ON cnli.credit_note_id = credit_note.id
JOIN invoice_metadata AS invoice ON invoice.id = credit_note.invoice_id

WHERE credit_note.voided_at IS NULL
  AND credit_note.deleted_at IS NULL
  AND cnli.voided_at IS NULL
  AND cnli.deleted_at IS NULL
  AND invoice.voided_at IS NULL
  AND invoice.deleted_at IS NULL

GROUP BY 1, 2, 3, 4

SELECT invoice.id
     , invoice.invoice_number
     , cbt.created_at
     , cbt.type
     , cbt.amount
     -- Positive = credit applied (reduced invoice), Negative = negative balance applied (increased invoice)
     , CASE
         WHEN cbt.type = 'decrement' THEN cbt.amount
         WHEN cbt.type = 'increment' THEN -cbt.amount
       END AS effect_on_invoice

FROM customer_balance_transaction AS cbt
JOIN invoice_metadata AS invoice ON invoice.id = cbt.invoice_id

WHERE cbt.action = 'applied_to_invoice'
  AND cbt.deleted_at IS NULL

WITH subscription_timeline AS (
  SELECT customer_id
       , subscription_id
       , JSON_VALUE(plan, '$.id') AS plan_id

       -- Keep track of when the given plan was active on the subscription.
       -- N.b. start time is _inclusive_ whereas end time is _exclusive_
       , created_at AS start_time_inclusive
       , LEAD(created_at) OVER (PARTITION BY customer_id, subscription_id ORDER BY created_at ASC) AS end_time_exclusive

       , ROW_NUMBER() OVER (PARTITION BY customer_id, subscription_id ORDER BY created_at ASC) AS sequence_number
  FROM subscription_version
)

SELECT *
FROM subscription_timeline
ORDER BY customer_id ASC, subscription_id ASC, start_time_inclusive ASC