Outstanding invoices 💱

Tip

Solution to the following problem:

• outstanding-invoices.md

Result Set

Regardless of the database, the result set should look like:

invoice_currency	amount_outstanding
GBP	59184.66
INR	852086.90
USD	11219.50

Expand for the DDL

solution(invoice_currency, amount_outstanding) as (
    values
        ('GBP',  59184.66),
        ('INR', 852086.90),
        ('USD',  11219.50)
)

Solution

Some SQL solutions per database are provided below.

DuckDB

select
    invoices.invoice_currency,
    ceil(100 * sum(invoices.invoice_amount_usd * coalesce(exchange_rates.rate, 1))) / 100 as amount_outstanding
from invoices
    asof left join exchange_rates
        on  invoices.invoice_datetime >= exchange_rates.from_datetime
        and invoices.invoice_currency = exchange_rates.to_currency
        and exchange_rates.from_currency = 'USD'
where not invoices.is_paid
group by invoices.invoice_currency
order by invoices.invoice_currency

PostgreSQL

select
    invoices.invoice_currency,
    ceil(100.0 * sum(invoices.invoice_amount_usd * coalesce(rates.rate, 1.0))) / 100.0 as amount_outstanding
from invoices
    left join lateral (
        select *
        from exchange_rates
        where 1=1
            and invoices.invoice_datetime >= exchange_rates.from_datetime
            and invoices.invoice_currency = exchange_rates.to_currency
            and exchange_rates.from_currency = 'USD'
        order by exchange_rates.from_datetime desc
        limit 1
    ) as rates on true
where not invoices.is_paid
group by invoices.invoice_currency
order by invoices.invoice_currency

SQL Server

select
    invoices.invoice_currency,
    ceiling(100.0 * sum(invoices.invoice_amount_usd * coalesce(rates.rate, 1.0))) / 100.0 as amount_outstanding
from invoices
    outer apply (
        select top 1 *
        from exchange_rates
        where 1=1
            and invoices.invoice_datetime >= exchange_rates.from_datetime
            and invoices.invoice_currency = exchange_rates.to_currency
            and exchange_rates.from_currency = 'USD'
        order by exchange_rates.from_datetime desc
    ) as rates
where invoices.is_paid = 0
group by invoices.invoice_currency
order by invoices.invoice_currency